Convert JSON to CSV: nested objects, arrays and Excel gotchas

Converting a flat list of JSON objects to CSV takes one line in any language. Real JSON is rarely flat, though, and the CSV usually ends up in Excel, which has its own ideas about what your data means. This post covers both halves: getting nested JSON into rows and columns, and getting those rows into a spreadsheet without losing zeros, digits or dates.

For a quick conversion, paste or open the file in the JSON to CSV converter. The rest of this post explains the choices it makes and how to make different ones in code.

Start with one question: what is a row?

CSV is a single table. Before converting, decide what one row stands for. Take this export:

[
  { "id": "00417", "name": "Ana Silva", "address": { "city": "Porto", "zip": "4000-123" },
    "tags": ["admin", "beta"], "signup": "2026-03-04", "card": "4111111111111111" },
  { "id": "00418", "name": "Ben \"Benny\" Ott", "address": { "city": "Austin" },
    "tags": [], "signup": "2026-03-05", "note": "=1+1" }
]

One row per user is the obvious answer. But if the question is "which users have which tags", one row per user-tag pair is more useful. Both are valid CSVs of the same JSON, and no converter can pick for you. Nearly every tricky case in JSON-to-CSV comes down to this decision.

Nested objects become dotted columns

Objects inside a row are the easy part. Flatten them into columns named after their path:

Two details matter. Different rows can have different keys (Ben has no zip, Ana has no note), so the column list has to come from all rows, not the first one. And the separator should be something that can't appear in your keys. A dot is the usual choice; if your keys contain dots, use / or __.

Arrays: three options

Arrays are where converters differ, because there's no single right answer.

Join them into one cell. ["admin","beta"] becomes admin;beta. Easy to read and filter in a spreadsheet; you lose the difference between an empty list and a missing one. Good for short lists of plain values like tags.

Keep them as JSON text. The cell holds ["admin","beta"]. Nothing is lost and you can convert back, but it's awkward to work with in Excel. Good for arrays of objects you don't need to analyse.

Explode them into rows. One row per element, with the parent's fields repeated: one row per order item, each carrying the order ID. This is the normal choice for line items, events and anything you'll sum or pivot. For several unrelated arrays per record, write a separate CSV for each, linked by ID, like tables in a database.

Some tools create numbered columns instead (tags.0, tags.1). It works for fixed-length arrays such as coordinates, and gets messy otherwise: a record with 40 tags produces 40 columns for everyone.

With jq

jq's @csv formats an array as a CSV line and handles quoting. It refuses objects, which is the first error most people hit:

$ jq -r '(.[0] | keys_unsorted) as $k | $k, (.[] | [.[$k[]]]) | @csv' users.json
jq: error (at users.json:6): object ({"city":"Po...) is not valid in a csv row

The reliable approach is to name the columns you want:

jq -r '["id","name","city","zip","tags"],
  (.[] | [.id, .name, .address.city, .address.zip, (.tags | join(";"))])
  | @csv' users.json

"id","name","city","zip","tags"
"00417","Ana Silva","Porto","4000-123","admin;beta"
"00418","Ben ""Benny"" Ott","Austin",,""

Note the doubled quotes around Benny: that's correct CSV escaping. Missing values come out as empty cells.

To flatten everything without listing columns, build dotted keys from paths:

jq -r '
  map([paths(scalars) as $p
       | {key: ($p | map(tostring) | join(".")), value: getpath($p)}]
      | from_entries)
  | (map(keys_unsorted) | add | unique) as $cols
  | $cols, (.[] | [.[$cols[]]])
  | @csv' users.json

This gives address.city, address.zip and also tags.0, tags.1 (the numbered-column option), with columns sorted alphabetically because unique sorts. Empty arrays and objects disappear, since paths(scalars) only visits leaf values.

Exploding an array into rows is short in jq (if the syntax is new, jq vs JSONPath vs JMESPath walks through it):

jq -r '["order_id","sku","qty"],
  (.orders[] | .id as $id | .items[] | [$id, .sku, .qty])
  | @csv' orders.json

With Python

The standard library is enough. This keeps column order as first seen and stores arrays as JSON text:

import csv
import json

def flatten(obj, prefix=""):
    out = {}
    for key, value in obj.items():
        name = f"{prefix}{key}"
        if isinstance(value, dict):
            out.update(flatten(value, name + "."))
        elif isinstance(value, list):
            out[name] = json.dumps(value, ensure_ascii=False)
        else:
            out[name] = value
    return out

with open("users.json", encoding="utf-8") as f:
    rows = [flatten(r) for r in json.load(f)]

columns = list(dict.fromkeys(k for r in rows for k in r))
with open("users.csv", "w", newline="", encoding="utf-8-sig") as f:
    writer = csv.DictWriter(f, fieldnames=columns)
    writer.writeheader()
    writer.writerows(rows)

Two lines in there exist for Excel. newline="" stops blank lines appearing between rows on Windows. encoding="utf-8-sig" writes a byte order mark, which tells Excel the file is UTF-8; without it, Excel on Windows often shows é where you had é.

With pandas, json_normalize does the flattening, and record_path does the exploding:

import json
import pandas as pd

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

items = pd.json_normalize(
    data["orders"],
    record_path="items",
    meta=["id", ["customer", "name"]],
)
items.to_csv("order_items.csv", index=False, encoding="utf-8-sig")

sku,qty,id,customer.name
KB-01,1,A-100,Ana
MS-02,2,A-100,Ana

One pandas trap on the way back: pd.read_csv("users.csv") turns 00417 into 417 too. Pass dtype=str (or a per-column dict) for ID columns.

Excel gotchas

The CSV can be perfect and still look wrong once Excel opens it, because double-clicking a CSV makes Excel guess every column's type. The usual damage:

The fixes, from most to least reliable:

1. Don't double-click. Use Data > From Text/CSV (Get Data), set File Origin to UTF-8, and set ID-like columns to Text before loading. Excel then keeps exactly what's in the file.
2. In recent Microsoft 365 versions, File > Options > Data > Automatic data conversion lets you turn off removing leading zeros, truncating to 15 digits, scientific notation and date conversion. That fixes it for you, not for whoever you send the file to.
3. Write .xlsx instead of CSV when the file is for Excel users. A library like openpyxl or pandas' to_excel stores text as text, so nothing is guessed.
4. Google Sheets has the same behaviour; when importing, untick "Convert text to numbers, dates, and formulas".

Formula-looking cells are also a security issue, not only a display one. If the JSON comes from users (names, comments, support tickets), a value like =HYPERLINK(...) becomes a live formula when someone opens the CSV. This is called CSV or formula injection. When exporting untrusted data for spreadsheets, prefix cells that start with =, +, -, @, tab or carriage return with a single quote, as the OWASP guidance suggests.

Tricks like writing ="00417" into the CSV do keep the zeros in Excel, but every other tool then sees the literal text ="00417". Avoid them unless Excel is the only consumer.

In the browser

GigaJSON's JSON to CSV tool flattens nested objects into dotted columns (address.city), keeps arrays inside a row as JSON text, collects columns from every row in first-seen order, and quotes what needs quoting. If you give it an object holding a single list ({"results": [...]}), it uses that list as the rows. It runs in your browser, so the file isn't uploaded, and it handles files of hundreds of megabytes. It doesn't add a byte order mark or escape formula characters, so for Excel use the From Text/CSV import described above, and treat user-supplied data with care.

If you need to explode an array or pick columns first, open the file in the app: the Table view shows a list of objects as a grid you can sort and filter, and the Query tab lets you select just the part you want before exporting. Going the other way, CSV to JSON keeps values like 007 as text instead of turning them into numbers.

A checklist before you send the CSV

• Decide what one row means, and explode arrays you need to count or sum.
• Build the column list from every record, not the first.
• Keep IDs, ZIP codes, phone and card numbers as text all the way through.
• Write UTF-8 with a BOM if the file goes to Excel on Windows.
• Escape leading =, +, - and @ in user-provided values.
• Open the result the way the recipient will, and look at the ID column.