How to convert JSON to CSV

JSON and CSV are the two formats data bounces between most often. JSON is nested and hierarchical; CSV is flat and tabular. Converting between them sounds trivial and usually is, until you hit the edges: nested objects, arrays as values, mixed schemas, special characters, tens of millions of rows, or Excel’s opinions about what a CSV looks like. This guide covers the conversion rules that actually matter, how to handle nested data (flatten vs spread), encoding and delimiter pitfalls, streaming for large files, the Excel quirks that break normal CSVs, and when to reach for a proper ETL pipeline instead.

The happy-path conversion

JSON that’s already tabular (array of flat objects) maps 1:1 to CSV:

[
  { "name": "Alice", "age": 30, "city": "NYC" },
  { "name": "Bob", "age": 25, "city": "LA" }
]

Becomes:

name,age,city
Alice,30,NYC
Bob,25,LA

Header row uses object keys. Column order is either insertion order or alphabetical (choose explicitly — don’t let your tool decide).

Nested data — flatten or spread

Nested JSON doesn’t fit in flat CSV. Three common strategies:

Dot-path flatten: user.address.city becomes a column named user.address.city. Clean for single-level nesting; awkward for deep or variable structures.

JSON-in-cell: put nested objects or arrays as escaped JSON strings in a single column: "[{\"id\":1}]". Preserves everything, but you can’t analyze it without re-parsing.

Explode: turn an array into multiple rows, duplicating parent fields. One row per array element. Lossy in the reverse direction (can’t reconstruct the original structure).

Choose based on consumer: analysts in Excel want exploded rows; data warehouses want dot-paths; export pipelines want JSON-in-cell.

Schema variance — when rows have different keys

If JSON objects don’t all share the same keys, CSV forces a choice:

Union headers: include every key that appears anywhere. Rows leave unused cells empty. Default for most converters.

Intersection headers: include only keys present in every row. Drops data but produces consistent rows.

First-object schema: use only keys from the first object. Silently drops data from later rows that have extra keys. Avoid.

Union is usually what you want. Warn the user (or log) when rows have missing keys so they know data gaps exist.

Quoting and escaping

CSV rules (per RFC 4180):

Fields containing commas, newlines, or double quotes must be wrapped in double quotes.

Double quotes inside a quoted field are escaped by doubling: "She said \"hi\"".

Newlines inside quoted fields are literal — parsers must handle multi-line rows.

Gotcha: many tools (and people) fail on newlines in fields. Some normalize to spaces; some corrupt the CSV; some produce broken output that opens in Excel anyway.

Delimiter wars

CSV is “comma-separated values” — except when it’s not.

Comma (,): US default. Breaks on international data with European number formats.

Semicolon (;): European default. Excel uses it for locales where comma is the decimal separator (France, Germany).

Tab (\t): “TSV.” Safer than comma because tab rarely appears in real text.

Pipe (|): sometimes used for data exchange. Even rarer in natural text.

Rule: TSV is easier than CSV to produce correctly but harder for humans to eyeball. For shipping data between systems, prefer TSV. For end users in Excel, use locale-aware CSV.

Encoding — UTF-8 with (or without) BOM

CSV files have no header declaring their encoding. You find out by trying.

UTF-8 without BOM: universal default.

UTF-8 with BOM: Excel on Windows needs the BOM to auto-detect UTF-8. Without it, non-ASCII characters (é, ñ, 日) display as garbage on Excel Windows.

CP1252 / Windows-1252: legacy encoding some Windows tools export by default. Breaks on any character outside the 255 it supports.

UTF-16: some Excel versions save as UTF-16 if you “Save as Unicode Text.” Uncommon now but stare at failing imports and you’ll see it eventually.

Serving CSV for Excel? UTF-8 with BOM. Otherwise UTF-8 without BOM.

Large files — stream, don’t load

Loading a 500MB JSON file into memory to write CSV → OOM. Stream:

Streaming JSON parsers: JavaScript stream-json, Python ijson, Go jsonitr. Read objects one at a time as the file is read.

Streaming CSV writers: write row-by-row as you read JSON. Flush often. Back-pressure if writing to network.

Line-delimited JSON (NDJSON): if your source is already one JSON object per line, converting is trivial — read line, parse, write CSV row, repeat. Often the best choice for data warehousing.

Excel’s opinions

Excel thinks it knows what your CSV should look like. It doesn’t.

Leading zeros stripped: 01234 becomes 1234. Force text by prefixing with ="01234" or formatting the column as text after import.

Scientific notation for long numbers: 1234567890123456 becomes 1.23457E+15. Long IDs, phone numbers, credit card numbers all corrupt.

Dates reformatted: 2026-04-22 might become 22/04/2026 or 4/22/2026 depending on locale. Pre-format as text or use ISO 8601 strings the user won’t autoformat.

Gene names turned into dates: famous gotcha — SEPT1 becomes 1-Sep. Affects bioinformatics so much that some genes were renamed to avoid Excel.

Save-as-CSV round-trip corrupts: Excel saves CSVs in locale-specific format. Open, save, and CSVs can change delimiter, encoding, and date format silently.

CSV to JSON — the reverse

Similar rules, reversed:

Header row → JSON keys.

Each data row → one JSON object.

Type inference: try to parse as number, fallback to string. Some converters infer booleans and nulls; some don’t. Be explicit about your type expectations.

Missing values: empty string, null, or missing key? Pick one and document it.

When CSV is the wrong format

Deeply nested data: CSV loses structure. Use JSONL or Parquet.

Files > 10GB: Parquet or Arrow for columnar analytics; CSV is slow.

Typed data that matters: JSON or Parquet preserve types; CSV loses them.

Binary content: never CSV. Base64 encode into JSON, or use a binary-native format.

Common mistakes

Assuming comma is safe. Any text with a comma — addresses, descriptions, quoted text — must be properly quoted. Use the library, don’t concatenate.

Forgetting the header row. CSV without headers is ambiguous. Always include headers unless the format explicitly forbids them.

Mixing quoted and unquoted fields inconsistently.RFC 4180 allows it; parsers often don’t. Quote everything or nothing.

Using Excel as the converter. Excel corrupts numbers, dates, and encoding. Script the conversion; use Excel to view.

Ignoring line endings. CRLF (Windows) vs LF (Unix) can break naive parsers. RFC 4180 says CRLF; reality is messier.

Run the numbers

Convert JSON to CSV instantly with the JSON to CSV converter. Pair with the JSON formatter to clean up your JSON first, and the CSV to JSON converter for the reverse direction.