JSON to CSV Without Breaking Excel: 5 Rules That Matter

“Just export it as CSV” sounds like a five-minute job. Then the file opens in Excel with names split across two columns, quotes everywhere, and a column called [object Object]. Converting JSON to CSV properly comes down to a handful of rules.

1. Quote values that contain commas, quotes or line breaks

CSV has no types, only text separated by commas. A value like Sara, M. must be wrapped in double quotes, and a double quote inside a value is written twice. That's RFC 4180, and it's what Excel and Google Sheets expect.

2. Flatten nested objects

A spreadsheet is flat. Nested objects need to become columns, usually with a dot path: city.name. Arrays are trickier; the safest default is to keep them as JSON text in one cell so nothing is lost.

3. Collect every key, not just the first row's

JSON rows don't all have the same fields. If you only take the first object's keys, the note on the second row silently disappears. Gather keys from every row and leave cells empty where a row has no value.

Put together, this input:

[
  { "id": 1, "name": "Ali", "city": { "name": "Lahore" } },
  { "id": 2, "name": "Sara, M.", "note": "said \"hi\"" }
]

becomes:

id,name,city.name,note
1,Ali,Lahore,
2,"Sara, M.",,"said ""hi"""

4. Pick the delimiter for the people who'll open it

In many European locales Excel expects semicolons, because the comma is the decimal separator. If your users are there, export with ;:

id;name;city.name;note
1;Ali;Lahore;
2;Sara, M.;;"said ""hi"""

5. Watch out for Excel “helping”

1. Long numbers such as IDs or phone numbers lose their leading zeros or turn into scientific notation. Import them as text.
2. Values starting with =, +, - or @ can be run as formulas. If the CSV contains user input, prefix them with a single quote before export.
3. Save as UTF-8 with a BOM if you need accented or non-Latin characters to show correctly in Excel on Windows.