Flattening JSON for CSV: Arrays, Nesting and Common Pitfalls
CSV is still the easiest way to get data into a spreadsheet, but JSON is nested and typed while CSV is a flat table of text. This guide explains how the conversion works, what happens to nested objects and arrays, and how to avoid the classic spreadsheet surprises.
The ideal input shape
CSV works best when the JSON is an array of objects, where each object is one row and each key is a column:
[
{"id": 1, "name": "Ada", "city": "London"},
{"id": 2, "name": "Lin", "city": "Bengaluru"}
]id,name,city
1,Ada,London
2,Lin,BengaluruIf you have a single object, the converter writes it as one row.
Nested objects become dotted columns
CSV has no nesting, so the JSON to CSV converter flattens nested objects into column names joined with dots:
[{"id": 1, "user": {"name": "Ada", "address": {"city": "London"}}}]id,user.name,user.address.city
1,Ada,LondonArrays are written as JSON text
An array inside a row cannot be spread over a fixed set of columns without guessing, so it is kept in one cell as JSON text: "[""math"",""code""]". If you need one row per array item, reshape the data before converting.
Different keys in different rows
The converter collects every key that appears in any row to build the header. A row that lacks a key gets an empty cell, so no data is dropped when objects are inconsistent.
Quoting and special characters
Following RFC 4180, a value is wrapped in double quotes when it contains a comma, a double quote or a line break, and inner double quotes are doubled. Everything else is written as-is. Choose JSON to TSV instead if your values contain many commas.
Spreadsheet surprises
- Leading zeros: spreadsheets turn
00123into123. Import the column as text. - Long numbers: IDs longer than 15 digits can be rounded or shown in scientific notation.
- Dates: strings like
2026-10-03may be converted to the local date format. - Encoding: the output is UTF-8; if accented characters look wrong, use your spreadsheet's import dialog and choose UTF-8.
Going back from CSV to JSON
CSV to JSON reads the first row as the header and returns an array of objects. Every value is a string, because CSV does not store types, and dotted headers are not turned back into nested objects.
Frequently asked questions
Why are my numbers strings after CSV to JSON?
CSV does not record types, so the converter cannot know whether 42 is a number or text. Convert types in your own code.
Can I choose a different delimiter?
Use JSON to TSV for tab-separated output, which avoids most quoting.
Why is my nested data in one cell?
Arrays are kept as JSON text in one cell. Reshape the data into one object per row if you need separate rows.