How to Convert JSON to CSV (Including Nested Data)
Conversion 9 min readUpdated October 12, 2026
Spreadsheets, reporting tools and many databases want rows and columns, while APIs hand you nested JSON. Converting between them is easy when every record is flat and tricky as soon as objects and arrays are nested. This guide walks through the decisions you have to make, with code you can adapt.
In this guide
- The simple case: an array of flat objects
- Nested objects: flatten with dotted names
- Arrays inside records: pick a strategy
- Records with different keys
- Quoting and escaping (RFC 4180)
- Working code: Python
- Working code: jq
- Problems that appear when you open the file in Excel
- Check the result
- Frequently asked questions
The simple case: an array of flat objects
When the JSON is an array of objects that all have the same simple keys, each object becomes a row and each key becomes a column.
[
{ "id": 1, "name": "Asha", "city": "Pune" },
{ "id": 2, "name": "Rohan", "city": "Delhi" }
]id,name,city
1,Asha,Pune
2,Rohan,DelhiThat is all that most online converters do. Everything below is about what to do when the data is not that tidy.
Nested objects: flatten with dotted names
CSV has no nesting, so a nested object has to be spread across several columns. The usual convention is to join the path with dots.
{ "id": 1, "name": "Asha", "address": { "city": "Pune", "zip": "411001" } }id,name,address.city,address.zip
1,Asha,Pune,411001Arrays inside records: pick a strategy
Arrays are the hard part, because one record can contain many values. There are three common choices, and the right one depends on how the CSV will be used.
| Strategy | Result | Use when |
|---|---|---|
| Join into one cell | tags = admin;beta | The array holds a few simple values and you only need to read them. |
| One column per position | tags.0 = admin, tags.1 = beta | Arrays are short and have a similar length in every record. |
| One row per array item | Two rows for the same user, each with one tag | You want to filter or pivot on the items. The parent fields are repeated on each row. |
Arrays of objects, such as order lines, usually deserve the third option or a separate CSV file for the child records with the parent's id in each row, just like a database table.
Records with different keys
JSON does not guarantee that every object has the same keys. If you build the header from only the first record, later fields are lost. Collect the union of all keys across the whole array, in the order they first appear, and leave a cell empty when a record lacks that key.
Quoting and escaping (RFC 4180)
CSV needs quotes whenever a value contains the delimiter, a double quote or a line break. A double quote inside a quoted value is written twice.
id,note
1,"Pune, India"
2,"He said ""hello"""
3,"line one
line two"Do not build CSV by joining strings with commas yourself. Use your language's CSV library, which handles these rules for you.
Working code: Python
import csv
import json
def flatten(value, prefix=""):
"""{"a": {"b": 1}} -> {"a.b": 1}. Lists of simple values are joined with ';'."""
row = {}
for key, item in value.items():
name = f"{prefix}{key}"
if isinstance(item, dict):
row.update(flatten(item, prefix=f"{name}."))
elif isinstance(item, list):
row[name] = ";".join(str(x) for x in item)
else:
row[name] = item
return row
with open("users.json", encoding="utf-8") as f:
records = [flatten(record) for record in json.load(f)]
# Union of all keys, in order of first appearance
columns = []
for record in records:
for key in record:
if key not in columns:
columns.append(key)
with open("users.csv", "w", newline="", encoding="utf-8") as f:
writer = csv.DictWriter(f, fieldnames=columns)
writer.writeheader()
writer.writerows(records) # missing keys become empty cellsWorking code: jq
# Flat array of objects -> CSV, using the union of all keys as the header
jq -r '(map(keys) | add | unique) as $cols
| $cols, (.[] | [.[$cols[]]])
| @csv' users.json > users.csvThis handles flat records. For nested data, flatten first, for example with jq expressions that build the columns you want, or use the Python function above.
Problems that appear when you open the file in Excel
- Leading zeros disappear. Excel turns
02134into2134. Import the column as text, or add a leading apostrophe in a copy intended only for viewing. - Long numbers turn into scientific notation. Identifiers with 15 or more digits lose precision when a spreadsheet treats them as numbers. Import them as text.
- Garbled non-English characters. Excel often assumes a legacy encoding unless the file starts with a UTF-8 byte order mark. Save as "UTF-8 with BOM" for Excel users, or import using the Data tab.
- Semicolons instead of commas. In locales that use a comma as the decimal separator, Excel expects semicolons as the delimiter.
- Formula injection. A cell that starts with
=,+,-or@can be run as a formula when opened in a spreadsheet. If the data comes from users, prefix such values with a single quote or a tab before exporting. OWASP documents this as CSV injection.
Check the result
After converting, count the rows and compare them with the number of records in the JSON, and spot-check a record with nested or missing values. A conversion that silently drops a field is much harder to notice later than one that fails loudly.
Frequently asked questions
Can every JSON document be converted to CSV?
Not without choices. A plain array of flat objects converts directly. Anything nested requires a rule for flattening, and a document that is not an array of records (for example one big object with unrelated sections) may need to be split into several CSV files.
How do I convert CSV back to JSON?
Read each row into an object using the header as keys. Remember that CSV stores everything as text, so you must convert numbers, booleans and empty cells yourself. Nested structures flattened with dots can be rebuilt by splitting the column names.
What delimiter should I use?
A comma is the standard. Use a semicolon if your users' spreadsheets expect it, or a tab (TSV) when values often contain commas. State the delimiter in the file name or documentation.
Is it safe to convert sensitive data with an online tool?
Only use tools that process the data in your browser and do not upload it, and avoid pasting real credentials or personal data into any website. For regulated data, convert it locally with a script.