Flattening JSON for CSV: Arrays, Nesting and Common Pitfalls

Published

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,Bengaluru

If 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,London

Arrays 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 00123 into 123. 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-03 may 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.

Related guides