JSON to CSV Without Losing Data: Flattening, Encoding, and Excel Traps
By the Super Simple Digital Tools Team · Updated June 2026
Most JSON to CSV problems are not really about JSON or CSV; they are about the gap between a flexible tree and a rigid grid. JSON can nest objects inside objects and arrays inside arrays to any depth, while CSV is a flat table of rows and columns. The conversion succeeds when your data is already list-shaped, an array of records that all describe the same kind of thing, and gets awkward only when a single record fans out into sub-lists. Knowing that distinction up front tells you whether you should convert at all or keep the data as JSON.
The first real decision is how to flatten. A nested object usually maps cleanly to dotted columns: {"user":{"id":1,"city":"Oslo"}} becomes user.id and user.city, one value each, no information lost. Arrays need more thought. An array of primitives like ["red","blue"] reads best joined into one cell, because the items belong together as a set. An array of objects, such as invoice line items, carries independent meaning per item, so you either expand them into numbered columns or split the record into multiple rows. Picking the wrong strategy is the most common cause of a CSV that technically converts but is unusable.
Headers come from the union of keys, not just the first object. If one record has an email field and the next does not, the converter still creates an email column and leaves the missing cell empty so every row has the same width. This matters with API data, where optional fields appear only on some records. Relying on the first object alone would silently drop columns. Empty cells are correct here; they preserve the shape of the table and let spreadsheets and importers line up values under the right headers.
Escaping is where naive conversions fall apart. The CSV format reserves the comma as a separator, the double quote as a wrapper, and the line break as a row terminator, so any value containing one of those must be quoted, and quotes inside the value are doubled rather than backslash-escaped. A product description with a comma, an address with a newline, or a note with a quotation mark all need this treatment, or the columns shift and the file becomes garbage two rows down. A correct converter applies RFC 4180 quoting automatically so you never have to think about it.
Finally, encoding and the spreadsheet itself. Save and treat CSV as UTF-8 so accented names and non-Latin scripts survive; Excel on Windows may need a byte-order mark or an explicit import step to read UTF-8 correctly. The bigger trap is type auto-detection: opening a CSV by double-click lets Excel reinterpret your text, dropping leading zeros and rewriting long IDs as 1.23457E+15. The file is fine; the viewer is guessing. Import through the data wizard, set identifier columns to Text, and your CSV will round-trip exactly as written.
- Wrap your input in square brackets if it is a bare list of objects; the converter needs a top-level array to produce one row per record.
- For ZIP codes, phone numbers, and long IDs, open the CSV via Excel's Data > From Text/CSV and set those columns to Text to stop leading zeros and scientific-notation corruption.
- Decide per array: join primitive arrays like tags into one cell, but expand arrays of objects (line items) into separate columns or rows so nothing collapses.
- If accented or non-Latin characters look wrong after opening, the file is UTF-8 but the spreadsheet guessed the encoding; reimport and choose UTF-8 explicitly.