Convert JSON to CSV (and back)

Converting JSON to CSV is one of the most common data tasks: someone needs an API export in a spreadsheet, or a list of records loaded into a tool that only reads CSV. The conversion is easy when the JSON is a flat list of similar objects, and tricky when it isn't. This guide covers the simple case, nested objects, arrays, and the CSV escaping rules that break naive converters.

The easy case: a flat array of objects

json
[
  { "id": 1, "name": "Asha", "email": "asha@example.com" },
  { "id": 2, "name": "Ben", "email": "ben@example.com" }
]

becomes:

text
id,name,email
1,Asha,asha@example.com
2,Ben,ben@example.com

Each object is a row, and each key is a column. Collect the header from the keys of all rows, not just the first, because later objects may have keys the first one lacks. Leave cells empty where a row has no value.

Nested objects: flatten with dotted keys

CSV is two-dimensional, so nested objects have to be flattened. The usual convention joins key paths with dots:

json
{ "id": 1, "address": { "city": "Austin", "zip": "78701" } }

text
id,address.city,address.zip
1,Austin,78701

Arrays: pick a strategy

Arrays don't fit into a single cell, so you have to decide:

Strategy	Example output	Best when
Join into one cell	tags = admin;editor	Short lists of simple values
Index columns	tags.0, tags.1	Arrays with a small, fixed maximum length
One row per item	Repeat the parent fields for each item	Line items, such as orders and their products
Separate file	orders.csv + order_items.csv linked by ID	Loading into a database or BI tool

For an order with three line items, "one row per item" produces three rows that repeat the order ID and customer. That's what most spreadsheet users expect.

CSV escaping rules

Most broken CSV comes from skipping these rules (RFC 4180):

1. If a value contains a comma, a double quote or a line break, wrap it in double quotes.
2. Inside a quoted value, write a double quote as two double quotes.
3. Use the same line ending throughout. CRLF (\r\n) is the standard; most tools also accept LF.

text
id,comment
1,"Said ""hi"", then left"
2,"Line one
Line two"

Never build CSV by joining values with commas. Use a CSV library, which handles quoting for you.

Excel quirks

• Encoding: Excel may misread UTF-8 files with accented characters unless the file starts with a byte order mark (BOM). If names come out as Café, add a BOM or import through Data → From Text/CSV.
• Leading zeros and long numbers: Excel turns ZIP code 01234 into 1234, and 16-digit IDs into scientific notation. Import those columns as text.
• Formula injection: a cell that starts with =, +, - or @ can run as a formula when opened. If you export untrusted data, prefix such values with a single quote (').

Converting in code

Python with the standard library:

python
import csv, json

rows = json.load(open("users.json", encoding="utf-8"))
fields = sorted({key for row in rows for key in row})
with open("users.csv", "w", newline="", encoding="utf-8-sig") as f:
    writer = csv.DictWriter(f, fieldnames=fields)
    writer.writeheader()
    writer.writerows(rows)

utf-8-sig writes the BOM that Excel needs. For nested JSON, pandas.json_normalize(rows) flattens objects into dotted columns.

jq for flat objects:

bash
jq -r '(.[0] | keys_unsorted) as $k | $k, (.[] | [.[$k[]]]) | @csv' users.json

@csv handles quoting. This version takes the header from the first object only, so it suits uniform data.

Going the other way: CSV to JSON

Every CSV value is a string, so decide how to type the columns: "42" vs 42, "true" vs true, and empty cell vs null. Libraries such as Papa Parse (JavaScript) offer dynamicTyping, but check that IDs and ZIP codes stay strings.

Before you convert

Format the JSON in the JSON Formatter and switch to table view, which shows an array of objects as rows and columns. That's a quick way to spot inconsistent keys and nested fields before you choose a strategy. For very large exports, see Handle large JSON.