JSON to CSV: Convert Nested JSON in Python, jq, Excel
November 11, 2026 · 10 min read
JSON to CSV: Convert Nested JSON with Python, jq or Excel
Converting JSON to CSV means turning each object into a row and each key into a column. An array of flat objects converts with one line of code. Nested objects and arrays need a decision first: dotted column names, several values packed into one cell, or one row per array item. Here's each style in Python, jq, Excel and the browser.
What JSON Shape Converts Cleanly to CSV?
CSV is a grid, so the easiest input is an array of flat objects: every object becomes a row, and every key becomes a column header. Real exports rarely look that tidy. They nest a customer inside each order and keep line items in a list. This sample of two orders runs through the whole post:
[
{"id": 1001, "customer": {"name": "Ana Ruiz", "city": "Lisbon"}, "items": ["pen", "ink"], "total": 12.5},
{"id": 1002, "customer": {"name": "Luis Ortega", "city": "Madrid"}, "items": ["notebook"], "total": 4}
]
Three things decide how smoothly the conversion of JSON to CSV goes:
- Where the records live. API responses often wrap the array, as in
{"orders": [...]}. You have to point your tool at that inner array. - Whether records share keys. When one object has a key the others lack, the CSV gets the union of all keys, with blank cells where a value is missing.
- What's nested. Objects and arrays have no place in a flat cell, so something has to flatten them.
If you want a readable table for docs or a README rather than a .csv file, see our post on converting JSON to Markdown instead. To turn a CSV file back into JSON, follow our CSV to JSON format guide.
Nested JSON to CSV: Dotted Columns, Packed Cells or Extra Rows
Nested objects are the easy half. pandas, Power Query and most converters turn customer.name and customer.city into their own columns, joining the path with a dot. With jq, you name each column yourself. Arrays are the real choice, and you have three options.
Pack the array into one cell. The list becomes text such as pen, ink. Because that text contains a comma, the cell must be wrapped in double quotes. RFC 4180 says any field holding a comma, a double quote or a line break should be enclosed in double quotes. A quote inside such a field is escaped by doubling it.
id,customer.name,customer.city,items,total
1001,Ana Ruiz,Lisbon,"pen, ink",12.5
1002,Luis Ortega,Madrid,notebook,4
Explode the array into extra rows. Each item gets its own row, and the order fields repeat on every row.
id,customer.name,customer.city,item,total
1001,Ana Ruiz,Lisbon,pen,12.5
1001,Ana Ruiz,Lisbon,ink,12.5
1002,Luis Ortega,Madrid,notebook,4
Spread it across numbered columns. You get items.0 and items.1, with blanks wherever an order has fewer items. This only works when arrays are short and roughly the same length.
We prefer extra rows whenever the CSV is headed for a pivot table, because you can count and filter items directly. Just remember that order-level values such as total now repeat, so summing that column double counts. Packed cells suit archives and lookups, where one row per record matters more than analysis.
How to Convert JSON to CSV in Python
In Python, the pandas library does the flattening for you. pd.json_normalize turns nested objects into dotted columns (its sep parameter defaults to a dot). Arrays stay as Python lists, so pack or explode them before writing:
import json
import pandas as pd
with open("orders.json", encoding="utf-8") as f:
data = json.load(f)
df = pd.json_normalize(data) # customer.name, customer.city
df["items"] = df["items"].str.join(", ") # pack the list into one cell
df.to_csv("orders.csv", index=False, encoding="utf-8-sig")
That writes:
id,items,total,customer.name,customer.city
1001,"pen, ink",12.5,Ana Ruiz,Lisbon
1002,notebook,4.0,Luis Ortega,Madrid
Notice two quirks. Nested columns land after the top-level ones, and total becomes 4.0 because the column holds floats. If you skip the str.join line, the cell reads ['pen', 'ink'], which is rarely what you want. For one row per item, replace that line with df = df.explode("items"). The utf-8-sig encoding adds a byte order mark, which helps Excel on Windows read accented names correctly.
Sometimes the array sits inside a wrapper object. Then record_path points at it, and meta copies wrapper fields onto every row:
raw = {"exported_at": "2026-11-01", "orders": data}
df = pd.json_normalize(raw, record_path="orders", meta=["exported_at"])
No pandas? The standard library's csv module handles it with a short flatten function:
import csv
import json
def flatten(obj, prefix=""):
row = {}
for key, value in obj.items():
name = f"{prefix}{key}"
if isinstance(value, dict):
row.update(flatten(value, name + "."))
elif isinstance(value, list):
row[name] = ", ".join(str(v) for v in value)
else:
row[name] = value
return row
with open("orders.json", encoding="utf-8") as f:
rows = [flatten(record) for record in json.load(f)]
fieldnames = list(dict.fromkeys(key for row in rows for key in row))
with open("orders.csv", "w", newline="", encoding="utf-8-sig") as f:
writer = csv.DictWriter(f, fieldnames=fieldnames)
writer.writeheader()
writer.writerows(rows)
This keeps the original key order (id,customer.name,customer.city,items,total) and quotes "pen, ink" automatically. Building fieldnames from every row gives you the union of keys, and DictWriter fills missing ones with an empty string by default. Open the file with newline="", as the Python docs advise, or you can get blank lines between rows on Windows.
Convert from JSON to CSV with jq on the Command Line
The jq command-line tool turns JSON into CSV without a script. You list the fields you want, and its @csv filter formats each array as one CSV line:
jq -r '(["id","customer","city","items","total"]),
(.[] | [.id, .customer.name, .customer.city, (.items | join(", ")), .total])
| @csv' orders.json > orders.csv
Output:
"id","customer","city","items","total"
1001,"Ana Ruiz","Lisbon","pen, ink",12.5
1002,"Luis Ortega","Madrid","notebook",4
The first array is your header row, since jq won't invent one. @csv quotes every string, leaves numbers bare and escapes inner quotes by doubling them, so say "hi" becomes "say ""hi""". The -r flag matters: without it, jq prints each line as a JSON string with escaped quotes.
@csv refuses objects and arrays. Pass it .items unchanged and you get array (["pen","ink"]) is not valid in a csv row. Join the list first, as above, or explode it into rows:
jq -r '.[] | .id as $id | .customer.name as $name | .items[] | [$id, $name, .] | @csv' orders.json
That prints 1001,"Ana Ruiz","pen", then 1001,"Ana Ruiz","ink", then 1002,"Luis Ortega","notebook".
How Can I Convert JSON to Excel and Then to CSV?
Excel imports JSON through Power Query, and from there you can save the result as CSV. Here are the steps in Excel for Windows:
- Go to Data > Get Data > From File > From JSON and pick your file.
- The Power Query Editor opens and detects a table automatically. If you see a list of Record values instead, choose To Table.
- Nested objects show up as Record columns. Click the expand icon (two arrows) in the column header to split them into fields such as name and city.
- Arrays show up as List columns. Their expand icon offers Expand to New Rows (the extra-rows style) or Extract Values with a delimiter (the packed style).
- Choose Close & Load, then File > Save As and pick CSV UTF-8 (Comma delimited).
Save As CSV keeps only the active sheet, so save once per sheet if you loaded several queries. Microsoft's JSON connector page documents the import. Menus on Excel for Mac can differ, so check the Get Data menu in your version.
A Two-Step Browser Route with Our Table Tools
We don't have a one-click JSON to CSV converter. You can still do it in two steps with our table tools, which run in your browser:
- Paste your JSON into the JSON to Markdown tool. It accepts an array of objects, an object of objects (the outer key becomes a key column) or a single object. Nested keys become dotted paths, and arrays are joined with a comma and a space.
- Copy the Markdown table into the CSV to Markdown tool, switch it to Markdown to CSV, pick an output delimiter and download
table.csv.
Step 1 turns the sample orders into this Markdown table:
| id | customer.name | customer.city | items | total |
| ---- | ------------- | ------------- | -------- | ----- |
| 1001 | Ana Ruiz | Lisbon | pen, ink | 12.5 |
| 1002 | Luis Ortega | Madrid | notebook | 4 |
In step 2, the items cell contains the delimiter, so the CSV writer wraps it in quotes: 1001,Ana Ruiz,Lisbon,"pen, ink",12.5. Still, open the file and check it before importing it elsewhere. For the opposite trip, from a spreadsheet to a table, see our CSV to Markdown table guide. If you'd rather have a spreadsheet, paste the same Markdown table into the Excel to Markdown tool, switch it to Markdown to Excel and download an .xlsx file. Our Markdown to Excel guide covers the other routes and the cleanup each one needs.
The catch is that this route only packs arrays into one cell. It can't explode them into extra rows, and an array of objects lands in a single cell as JSON text. For those cases, use pandas or jq.
Encoding, Leading Zeros and JSON Lines: Fixing the CSV After Conversion
A CSV that looks right in a text editor can still break when someone opens it. Check for these problems before you send the file on:
- Garbled accents. Excel on Windows may misread UTF-8 without a byte order mark, so
Málagaturns into junk. Write withutf-8-sigin Python or save as CSV UTF-8 from Excel. - Lost leading zeros and long IDs. Excel reads
02134as the number 2134, and it keeps only 15 significant digits, so a 16-digit order number ends in a zero. Import through Data > From Text/CSV, choose Transform Data, and set those columns to Text. - Everything in one column. Regions that use a comma as the decimal mark often expect semicolons. Pick a semicolon delimiter when you export.
- JSON Lines files. A
.jsonlfile holds one JSON object per line rather than a single array. Add-sto jq to collect the lines into one array, and the commands above work unchanged. Without-s, jq reads each line on its own, so drop the.[]and skip the header array, which would otherwise repeat on every line. In Python, calljson.loadson each line beforejson_normalize. - Very large files. Stream them with Python or jq on your own machine rather than pasting hundreds of megabytes into a browser tab.
Every JSON to CSV job comes down to the same choice: dotted columns for objects, then packed cells or extra rows for arrays. Pick that first, and the pandas, jq, Power Query or browser steps above all produce a clean file. Want to see the flattened data as a table before you export it? Run it through the JSON to Markdown tool, then paste the table into the editor to preview it.