JSON to CSV for Excel — Nested Objects Hate You

webdev json-csv tools

CSV is a rectangle. JSON is a tree. Every “just convert JSON to CSV” request is really a schema design problem: which nested fields become columns, what happens to arrays, and how Excel will misread UTF-8 if you are not careful.

Teams ship a “flat” export that drops nested keys without warning. Finance opens it in Excel, Japanese customer names become mojibake, and nobody notices until a quarterly report.

Decide the flatten rules before you write a line

Given:

[
  {
    "id": 1,
    "name": "Ada",
    "address": { "city": "London", "zip": "E1" },
    "tags": ["admin", "beta"]
  }
]

Reasonable CSV strategies:

  • Dot columns: address.city, address.zip
  • JSON-in-cell: keep address as a stringified object (ugly but lossless)
  • Arrays: join with |, duplicate rows per tag, or drop

There is no universal right answer. There is only an answer you document and apply consistently.

id,name,address.city,address.zip,tags
1,Ada,London,E1,admin|beta

A browser JSON ↔ CSV converter is useful for one-off analyst exports when the shape is already almost tabular. For recurring pipelines, encode the flatten map in code so Monday’s export matches Friday’s.

Excel-specific landmines

UTF-8 without BOM on Windows Excel. Many Excel versions assume ANSI/legacy encodings when opening CSV via double-click. Prefacing the file with UTF-8 BOM (EF BB BF) fixes a large class of mojibake for names and currency symbols. LibreOffice and Google Sheets are more forgiving; your CFO’s desktop Excel is not.

Leading zeros. Zip codes and account IDs become numbers. Force text with a leading ' in Excel terms, or wrap fields, or export as .xlsx when identity must survive.

Separators. Locales that use ; as the list separator will split your commas wrong. Either emit ; consistently for that audience or instruct users to use Data → From Text/CSV with explicit UTF-8.

Formulas. Cells starting with =, +, -, or @ can be interpreted as formulas (CSV injection). Prefix risky user content with a single quote or sanitize.

ProblemMitigation
Nested objectsExplicit flatten map
Arrays of objectsSeparate table / multiple rows
UTF-8 in ExcelBOM + import wizard
Big integersQuote as strings
Inconsistent keys per rowUnion of keys, empty cells

Do not “JSON.parse then join commas”

Naive:

rows.map((r) => Object.values(r).join(","));

Breaks on commas inside fields, newlines in comments, nested objects becoming [object Object], and unstable column order when keys differ between rows. Use a proper CSV encoder that quotes fields, or a library. Escape rules matter.

Round-trip expectations

CSV → JSON will not restore nested structure unless you encoded it on the way out. Treat CSV as an analytical projection, not as your system of record. Keep the JSON (or database) canonical.

FAQ

Should every API offer CSV?
Offer it when stakeholders live in spreadsheets. Keep pagination and column selection explicit so you do not dump million-row trees by accident.

Is TSV better?
Sometimes — fewer comma collisions. Excel still wants careful UTF-8 handling.

Why did my nested field disappear?
Your flattener ignored unknown depths. Fail loudly on unexpected objects in strict mode for pipelines.

Flatten with a known schema, encode CSV correctly, and add a UTF-8 BOM when Windows Excel is part of the audience. Nested JSON is not being difficult — CSV is just flat.