JSON to CSV for Excel: nested objects, arrays and encoding

CSV is a table: rows and columns of plain values. JSON is a tree: objects inside objects, lists inside lists. Converting one to the other means deciding how the tree becomes a table, and then getting past a few Excel quirks. This guide covers both.

1. Start from an array of objects

The natural shape is an array where each object becomes a row and each key a column:

[
  { "id": 1, "name": "Ada",   "city": "London" },
  { "id": 2, "name": "Grace", "city": "Arlington" }
]

id,name,city
1,Ada,London
2,Grace,Arlington

API responses often wrap the array, as in {"data": [...], "total": 2}. Use the inner array. The converter does this automatically when an object holds exactly one array of objects.

If objects have different keys, the columns are the union of all keys, in the order first seen, and missing values are left empty.

2. Flatten nested objects into dotted columns

A nested object becomes several columns named by their path. {"address": {"city": "London", "zip": "N1"}} becomes the columns address.city and address.zip. This keeps every value in its own cell, so you can sort and filter by it.

Watch for collisions: a key that already contains a dot, like "address.city", would land in the same column as the nested path. A careful converter keeps both, naming the second address.city (2).

3. Decide what to do with arrays

Lists inside a row don’t fit one cell. There are three common choices:

• Keep them as JSON text in one cell, like ["admin","editor"]. No data is lost and the row count stays the same. This is the default here.
• Spread them over columns (roles.0, roles.1). Works for short, fixed-length lists; awkward when lengths vary.
• One row per item, repeating the parent’s values. Good for analysis (each order line as a row), but the table gets longer and totals double-count.

4. Make Excel read it correctly

Accents and emoji: UTF-8 with a BOM

Excel opens a plain UTF-8 CSV as if it were in an old Windows encoding, so Zürich turns into Zürich. Starting the file with a byte order mark (the bytes EF BB BF) tells Excel it’s UTF-8. Google Sheets and Numbers handle either.

Commas or semicolons

In countries that write decimals with a comma (much of Europe and South America), Excel expects semicolons between columns. If everything lands in column A, export with a semicolon delimiter, or tab-separated.

Quoting

Cells that contain the delimiter, quotes or line breaks must be wrapped in double quotes, with inner quotes doubled (RFC 4180): "She said ""hi""".

Long numbers and leading zeros

Excel turns 12345678901234567890 into 1.23457E+19 and drops leading zeros from postcodes and phone numbers. Import through Data → From Text/CSV and set those columns to Text to keep them.

5. Don’t let a cell run as a formula

A cell starting with =, +, - or @ can run as a formula when the CSV is opened, and a formula like =HYPERLINK(...) from untrusted data can mislead whoever opens the file (CSV injection). Prefixing such cells with a single quote makes Excel show them as text. The converter does this by default (“Escape formulas”); turn it off if you need the raw values.

Do it in one step

The JSON to CSV converter flattens nested objects, keeps arrays as JSON, unwraps API responses, adds the BOM and escapes formulas, all in your browser:

Check the JSON is valid first if the converter reports an error, or explore its structure to decide what should become rows:

On the command line

For a flat array, jq can write CSV directly (you choose the columns):

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

With pandas, json_normalize flattens nested objects into dotted columns, and utf-8-sig adds the BOM for Excel:

import json, pandas as pd

with open("data.json") as f:
    df = pd.json_normalize(json.load(f))
df.to_csv("data.csv", index=False, encoding="utf-8-sig")