Converting JSON to CSV safely: flattening, quoting and other quiet traps

JSON is a tree; CSV is a grid. Squeezing one into the other forces decisions — about nested objects, missing keys, commas inside text — that naive converters make silently and wrongly. This guide names each decision so you can make it deliberately and check the result.

Every JSON-to-CSV conversion is an act of translation between two shapes that do not match. JSON nests, varies and omits; CSV demands fixed columns, identical rows, and text that must survive commas, quotes and line breaks inside its own values. A converter that does not tell you how it handled those mismatches has still handled them — by dropping your nested data, inventing column orders, or producing rows that fall apart in a spreadsheet.

Safe conversion means deciding four things on purpose: the shape (one row per what?), the flattening rule for nested values, the column set when records disagree, and the quoting standard for the output. Then verifying the result as data, not as text.

Shape first: rows, and the nesting problem

Well-formed conversion input is a JSON array of objects. Each object becomes a row; each key becomes a candidate column. Real data complicates this in two directions. Nested objects — {"address": {"city": "Lahore", "postcode": "54000"}} — flatten naturally into dotted columns address.city and address.postcode. Nested arrays do not flatten naturally at all: an orders list inside a customer record is many values competing for one cell. The honest options are joining with a separator (fine for tags, lossy for structured data), exploding to one row per array element with the parent fields repeated (the right answer for analysis), or serialising the array as JSON text in the cell (preserves everything, readable by nothing). Pick per dataset, and write the choice down for whoever consumes the file.

[
  {"id": 1, "name": "Ayesha Khan", "tags": ["vip", "newsletter"],
   "address": {"city": "Lahore", "postcode": "54000"}},
  {"id": 2, "name": "Daniel Osei", "address": {"city": "Accra"}}
]

Converted with dot-flattening and union columns, that becomes four-plus columns — id, name, tags, address.city, address.postcode — with record 2's postcode empty, its tags empty, and record 1's tags joined as vip; newsletter. Every one of those outcomes is a decision; the unsafe converter is the one that makes them invisibly.

Quoting: where CSV files actually break

CSV's grammar is three sentences long. Fields may contain anything except the delimiter, a quote, or a newline. A field containing any of those must be wrapped in double quotes, and a double quote inside a quoted field is written twice. So the name Osei, Daniel "Dan" becomes "Osei, Daniel ""Dan""". Get this wrong and columns shift silently — the most dangerous failure in data work, because the file still opens, the numbers still total, and one row's city is sitting in its postcode column.

Newlines inside values are legal when quoted — an address or a notes field can span physical lines — but they break line-count-based sanity checks and some older importers. If your consumer is unknown or elderly, replacing embedded newlines with spaces (deliberately, and logged) is often the safer trade. Delimiters deserve the same suspicion: data full of commas pushes many European pipelines to semicolon-delimited files, which Excel in those locales expects natively. The delimiter is a setting to confirm with the consumer, not a default to assume.

Types, dates and other silent edits

JSON has types; CSV has text. The conversion therefore makes promises it cannot keep: 007 the string and 7 the number both become cell text that a spreadsheet may reinterpret as it pleases — leading zeros vanish, long IDs turn into scientific notation, dates reformat themselves by locale. Defences: quote-protect identifier columns when your tooling allows, keep IDs as strings end-to-end, agree a date format (ISO 8601, 2026-09-02, travels best) before converting rather than letting each reader guess, and treat booleans/nulls explicitly — decide whether null becomes an empty cell, the text null, or an error, because your consumer will otherwise decide for you.

Verification: prove the grid matches the tree

1. Count rows: data rows in the CSV = array length in the JSON (unless you deliberately exploded arrays — then the count is the sum you expect).
2. Count columns per row with a real CSV parser, not by counting commas in a text editor; quoted commas will fool you.
3. Spot-check the ugliest records — the ones with commas, quotes, unicode and missing keys — field by field against the source.
4. Re-import somewhere else (a different spreadsheet or a script) and compare row count and a checksum of a stable column, such as the sum of IDs.
5. Open it as the consumer will. If the destination is Excel, open it in Excel. Its import behaviour is part of your specification whether you like it or not.

Mistakes and pitfalls

Converting an object of objects without noticing

Data shaped as {"user1": {...}, "user2": {...}} is a map, not a list. Converted naively, some tools produce one giant row. Promote the keys to a column (id = the former key) and the records to rows first.

Letting each record define its own columns

If the converter only reads the first record's keys, every key that first appears in record 40 is silently dropped. The column set must be the union across all records — confirm your tool does this, or pre-scan the data.

Trusting the spreadsheet's display as verification

Excel reformats as it displays: a column of IDs can be silently re-rendered in scientific notation while the file underneath is perfect — or the reverse. Verify the file with a parser; use the spreadsheet only to check the consumer's experience.

Common questions

What do I do with deeply nested JSON — five levels down?

Flattening produces a column explosion, which is a sign the data wants more than one table: split it into linked CSVs (customers.csv, orders.csv, items.csv) joined by an ID column. That is not defeat; that is the relational model doing its job.

Should the header row use the original key names?

Keep them stable and machine-friendly — original keys, dot paths for nesting — and publish a tiny data dictionary beside the file if humans need friendlier labels. Renaming columns during conversion creates a mapping nobody recorded.

Can I convert back from CSV to the same JSON?

Only if the conversion was disciplined: dot-flattened objects round-trip; joined arrays, exploded rows and type coercions do not. CSV is a projection of the JSON, not a copy of it. Keep the JSON as the source of truth.

To wrap up

A safe JSON-to-CSV conversion is a documented one: row unit chosen, nesting rule stated, columns unioned, quoting by the book, and the result verified as data. The conversion itself takes seconds — the safety is in the five minutes of decisions around it.