How to Convert JSON to CSV (Nested Data, Arrays, and Excel Gotchas)

JSON is great for nested data; spreadsheets want flat rows and columns. Converting between them is easy when your JSON is a list of similar objects, and trickier when it is deeply nested. Here is how to think about it.

The easy case

A JSON array of flat objects maps directly to a table. Each object becomes a row, and each key becomes a column header:

[
  { "name": "Ada", "age": 36 },
  { "name": "Lin", "age": 29 }
]

name,age
Ada,36
Lin,29

Nested objects and arrays

• Nested objects are usually flattened with dotted column names, so { "address": { "city": "Pune" } } becomes a column called address.city.
• Arrays are harder. Common choices are to join the values into one cell (red;green;blue), to create numbered columns (tags.0, tags.1), or to make one row per array element.
• Decide which suits your analysis; there isn't one correct answer.

Quoting and escaping

CSV has a few simple rules (standardized in RFC 4180) that trip up hand-written converters:

• Values containing a comma, a double quote, or a line break must be wrapped in double quotes.
• A double quote inside a value is escaped by doubling it: He said "hi" becomes "He said ""hi""".
• Every row should have the same number of columns.

Inconsistent keys

If objects don't all have the same keys, build the header from the union of all keys and leave missing values blank. Otherwise columns shift and data lands under the wrong heading.

Opening the CSV in Excel

• Accented and non-Latin characters may look garbled unless the file is saved as UTF-8 with a byte-order mark (BOM).
• Excel converts things that look like numbers or dates: leading zeros in IDs (00123 becomes 123) and values like 3-4 or 1E5 may be reinterpreted. Import the column as text to prevent that.
• Some regions use a semicolon as the separator, so check the delimiter if everything lands in one column.