How to convert JSON to CSV: online, Python, Excel, and jq

---

title: "How to convert JSON to CSV: online, Python, Excel, and jq" slug: "convert-json-to-csv" date: "2026-07-07" description: "Four reliable ways to turn JSON into CSV — and how to handle the nested objects, missing keys, and embedded commas that break naive converters." keywords:

• json to csv
• convert json to csv
• json to csv python
• json to csv excel
• json to spreadsheet

---

JSON and CSV describe data in fundamentally different shapes. JSON is a tree — objects inside arrays inside objects, nested as deep as you like. CSV is a flat grid: rows and columns, nothing else. Converting between them is easy exactly as long as your JSON is already grid-shaped — an array of flat objects — and gets progressively harder the further it strays from that. Here are four ways to do the conversion, and the failure modes each one has.

The shape that converts cleanly

Every JSON-to-CSV tool, library, and script is at its best with this input:

[
  {"id": 1, "name": "Ada", "role": "admin"},
  {"id": 2, "name": "Lin", "role": "editor"}
]

One array, one object per row, the same scalar keys in each object. That maps 1:1 onto:

id,name,role
1,Ada,admin
2,Lin,editor

If your JSON looks like this, use whichever method below is closest to hand. If it doesn't — nested objects, arrays inside rows, inconsistent keys — read the gotchas section first, because that's where every naive conversion silently loses data.

Option 1: online, without uploading your data

Paste the array into the JSON ↔ CSV converter and copy the result. The conversion runs entirely in your browser — nothing is uploaded — which matters more for CSV work than almost anything else, because the JSON people convert to spreadsheets is so often exports of user data, orders, or analytics that shouldn't be pasted into a random server-backed website.

The same tool converts in both directions, so you can round-trip CSV edits back to JSON.

Option 2: Python

For a flat array of objects, the standard library is enough:

import csv, json

with open("data.json", encoding="utf-8") as f:
    rows = json.load(f)

with open("data.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=rows[0].keys())
    writer.writeheader()
    writer.writerows(rows)

Note fieldnames=rows[0].keys() — the columns come from the first row. If later objects have extra keys, DictWriter raises an error rather than silently dropping them (pass extrasaction="ignore" to drop them deliberately). To collect the union of all keys instead, build fieldnames by iterating every row first.

For nested JSON, pandas does the flattening for you:

import pandas as pd

df = pd.json_normalize(rows)   # nested keys become "parent.child" columns
df.to_csv("data.csv", index=False)

json_normalize is the single most useful function in this whole problem space: it turns {"user": {"name": "Ada"}} into a user.name column instead of a useless {'name': 'Ada'} cell.

Option 3: Excel and Power Query

Excel can ingest JSON directly — no copy-paste through CSV needed:

1. Data tab → Get Data → From File → From JSON.
2. Power Query opens. Click Into Table, then use the expand button (⤢ icon in the column header) to unfold records into columns.
3. Close & Load puts the result in a worksheet, which you can then save as CSV if you need the file.

Power Query handles nesting interactively — each expand click flattens one level — which makes it the best option when you need to explore unfamiliar JSON rather than script a known shape. The downside is that it's manual; for a repeated job, script it in Python instead.

Option 4: jq

For pipelines and one-liners:

jq -r '(.[0] | keys_unsorted) as $keys
  | $keys, (.[] | [.[$keys[]]])
  | @csv' data.json > data.csv

The @csv filter handles quoting and escaping correctly. Like the Python DictWriter approach, this takes its columns from the first object — rows with different keys need the union-of-keys treatment first.

The four gotchas that corrupt conversions

Nested objects. A cell can't hold a tree. Converters either stringify the object into the cell ({"name":"Ada"} — technically lossless, practically useless in a spreadsheet) or flatten paths into columns (user.name). Know which yours does before trusting the output.

Arrays inside rows. "tags": ["a", "b"] has no natural CSV representation at all. Common strategies: join into one cell (a;b), one column per index (tags.0, tags.1), or one row per array element (which changes your row count). All are lossy in different ways — pick deliberately.

Inconsistent keys. JSON objects in the same array aren't required to have the same keys. CSV columns are fixed. Any converter that reads columns from the first row will drop fields that appear only later.

Embedded commas, quotes, and newlines. A value like "Kuala Lumpur, MY" must be quoted in CSV, quotes inside values must be doubled, and multi-line strings must stay inside one quoted cell. Hand-rolled ",".join(...) scripts get all three wrong. Use a real CSV writer — every option above uses one.

Going the other way

CSV back to JSON is the same mapping in reverse, with one extra trap: CSV has no types, so everything arrives as a string, and a converter has to guess whether 01234 is a number (losing the leading zero — the classic ZIP-code bug) or a string. The JSON ↔ CSV tool does the round trip in the browser; check numeric-looking identifier columns after any conversion, whatever tool you use. And if the JSON you're starting from won't parse at all, fix it first with the JSON Repair tool or find the exact error with the JSON Validator.