JSON to Excel without losing fields: four routes tested

Turning a JSON export into a spreadsheet can look fine and still be wrong. We tested on a sample export of 25,000 orders we made ourselves. One recipe lost a field that appeared only in the last record. Another dropped the 6,500 orders that had no line items, and a third stopped with an error after writing the header.

None of the quiet failures printed a warning.

If you pull data from an API or a SaaS export and hand it to people who work in Excel, this is for you. It shows what pandas, jq, DuckDB and Power Query do with nested JSON and where each one loses data. It ends with a short script that writes a two-sheet workbook and refuses to save if a single value went missing. Sources were read and every test was run on October 23, 2026.

JSON is a tree and a sheet is flat, so every converter makes three choices

A JSON record can hold objects inside objects and lists of any length. A worksheet holds one value per cell. Anything that converts one into the other has to decide three things, and most tools decide them for you.

The grain: one row per what. An order with three line items can be one row with the items packed into a cell, three rows with the order repeated on each, or one row on an orders sheet plus three rows on an items sheet. Repeating the order on every item row is what Power Query’s list expansion does by design: Microsoft’s reference for Table.ExpandListColumn says “Values in the other columns are duplicated in each new row created.”

The field list: from every record or from a sample. Some tools read the first record, or the first few thousand, and build the columns from those. A key that shows up later is then not a column at all. DuckDB’s reader, for example, detects the schema from a sample of 20,480 objects by default.

The types: what a number is. JSON itself sets no precision limit, but RFC 8259, the JSON standard, says only integers between -(2^53)+1 and 2^53-1 are safe to exchange, because most software stores numbers as 64-bit floating point. Excel keeps 15 significant digits. An ID with 16 or more digits fits neither.

Our test file: 25,000 orders with six planted traps

We wrote a Python script that generates a fake order export, the shape a shop or billing API returns. Every record has an order ID, an order number, a timestamp and a customer object with a nested address. It also has a list of line items, a list of tags, a total, a note and a shipping field. Into it we planted six problems that break conversions:

1. Long IDs. Each id has 19 digits, like 1840000000000000001, which is above 2^53.
2. Leading zeros. Order numbers such as 000123 and ZIP codes such as 02134 are strings that look like numbers.
3. Empty lists. 6,500 of the 25,000 orders have no line items; the other 18,500 have 37,000 between them.
4. A late key. discount_code appears on one record only, the very last one.
5. Type drift. shipping is the string "standard" on 24,750 orders and an object ({"method": "freight", "cost": 40}) on 250.
6. Awkward text. One note in five contains a comma and double quotes.

The file is 11.5 MB and holds 401,084 values in all, counting each empty list or object as one empty cell. The true sum of the total field is 1,843,984.02. We ran pandas 3.1.0, jq 1.7, DuckDB 2.0.0 and openpyxl 3.1.5 under Python 3.11 on Linux, and LibreOffice 24.2 to open the results. We had no Excel, so Power Query below comes from Microsoft’s documentation and is untested.

The results in one table

Route	Every field kept?	6,500 orders with no items	Line items	19-digit IDs
pandas json_normalize, default	Yes	Kept	Python text in one cell	Exact
pandas json_normalize, record_path="items"	Only the meta fields you list	Dropped	One row per item, order total repeated	Exact
jq, columns from the first record	Recipe 1 stopped; recipe 2 lost discount_code	Kept	JSON text in one cell	Exact
jq, columns from every leaf path	Yes (24 columns)	Kept	Spread across items.0.sku, items.1.sku…	Exact
DuckDB read_json, default sample	discount_code missing	Kept	DuckDB text in one cell	Exact in CSV, a number in .xlsx
DuckDB with unnest	Only the columns you select	Dropped	One row per item, order total repeated	Exact in CSV
Our script (below)	Yes, 401,084 values checked	Kept	Own sheet, linked by order ID	Stored as text

“Every field” means all 17 distinct field names in the file, counting the four inside customer, the three inside each line item and the two in the shipping object. The sections below explain each line.

pandas keeps every key but packs lists into Python text

pandas.json_normalize is the function pandas provides for this. With no options, it gave us 25,000 rows and 15 columns, discount_code included, because it builds columns from every record. Nested objects became dotted names such as customer.address.zip, and the zip codes kept their zeros. The IDs stayed exact in a 64-bit integer column.

But the lists did not flatten. The items cell for the second order read [{'sku': 'SKU-0001', 'qty': 2, 'price': 5.07}]. That is Python’s own notation with single quotes, not valid JSON, so a JSON parser cannot read it back.

The shipping drift became two places to look: a shipping column for the 24,750 strings and shipping.method and shipping.cost for the 250 objects. Because the cost column has blanks, pandas stored the 40 as 40.0.

The usual fix for lists is record_path="items", one row per line item, with order fields pulled in through meta. On our file that produced 37,000 rows from only 18,500 orders: the 6,500 orders with no items were gone. Every row repeated its order’s total, so summing that column gave 4,310,866.30, 2.34 times the true 1,843,984.02.

Asking for the late key in meta raised an error by default: the pandas documentation says errors='raise' “will raise KeyError if keys listed in meta are not always present”. Passing errors='ignore' makes it fill blanks instead.

When to use it: you already work in Python, and you want one row per record with lists you will handle yourself. When not to: you need line items, and orders without items must still count.

jq is exact with numbers until you do arithmetic

jq is a small command-line JSON processor. One short recipe takes the column names from the first record and prints each record with @csv:

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

On our file it printed the header and stopped with object ({"name":"Cu...) is not valid in a csv row, exit code 5. The jq 1.7 manual says @csv takes an array; a nested object inside it is an error.

Turning nested values into JSON text first (if type=="array" or type=="object" then tojson else . end) made the run finish, with all 25,000 rows. But discount_code was not in the output at all, because the first record did not have it. That is the dangerous version: it succeeds.

The version that worked builds the header from every leaf path in every record:

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

It ran in 3.9 seconds and gave 24 columns with every field present. Lists became numbered columns such as items.0.sku and items.1.sku. That reads well for short lists and badly for long ones. unique also sorts the names as text, and our check confirmed that items.10.sku lands before items.2.sku. The whole file is loaded into memory, which matters for exports in the gigabytes.

The 19-digit IDs came through exact. The jq 1.7 manual explains why: numbers are stored as IEEE 754 doubles, but “when parsing a literal jq will store the original literal string”. Touch the number and that ends: .id + 0 printed 1.84e+18. Convert IDs with tostring before any math, sorting or comparison you do not need on them.

When to use it: a quick look, a shell pipeline, or a server without Python. When not to: anyone will maintain the recipe after you, or the export has lists of real length.

DuckDB missed a field it never sampled

DuckDB reads JSON straight into SQL tables, so you can filter and join before anything is exported. With the default settings, read_json('orders.json') produced nine columns. discount_code was not one of them, and SELECT count(discount_code) failed with Referenced column "discount_code" not found. The key sits in record 25,000, past the 20,480 objects DuckDB samples by default. Setting sample_size=-1, which the documentation describes as scanning the entire input, brought it back.

Three other things changed on the way to CSV. The nested customer was written as {'name': Customer 0, 'email': c0@example.com, ...}, DuckDB’s own notation without quotes, not JSON. The shipping drift became a JSON column, so the plain strings were written with their quotes, as "standard". And created_at was detected as a timestamp with time zone: 2026-03-08T01:30:00-08:00 came out as 2026-03-08 09:30:00+00, the same instant in UTC (our session’s zone), with the original offset gone.

The line-item route with unnest(items, recursive := true) behaved like pandas: 37,000 rows, 18,500 orders, totals summing to 4,310,866.30.

DuckDB can also write .xlsx through its excel extension. It wrote our IDs as numbers. LibreOffice then showed them as 1.84E+018, and Excel’s 15-digit limit means the last four digits cannot survive there either. Cast IDs with CAST(id AS VARCHAR) in the SELECT.

When to use it: big files, or when you want to filter, aggregate or join before anything reaches a spreadsheet. When not to: you cannot spare the time to check the detected schema, or you forget sample_size=-1.

Power Query does it inside Excel, with the same choices made by clicks

Power Query is the route for people who never open a terminal. Per Microsoft’s JSON connector page, you choose Get Data, then JSON, pick the file, and the editor opens with “automatic table detection” applied to flatten the data.

A list column then offers two buttons, per Microsoft’s structured column guide. The Expand to New Rows button repeats the order on every item row. The Extract Values button joins the list into one cell with a delimiter you choose.

We could not run any of this, so treat the next two points as readings of the documentation to check on your own file. First, the expand step for a nested object, Table.ExpandRecordColumn, stores an explicit list of field names in the query. That is a fixed column list, the jq first-record problem in another form, so a key that later appears in a refreshed export may need the step edited.

Second, Microsoft’s data types page says the Decimal number type is a 64-bit float with “The largest precision that can be represented … 15 digits long”, while the Whole number type holds 19 digits. After loading, compare one long ID against the raw file by eye, and set ID columns to Text.

A JSON Lines file (one record per line) can make the connector show an “unable to connect” message, per the same Microsoft page. That page gives a short M script that reads such files line by line.

When to use it: the people who use the data live in Excel and want Refresh to pull a new export. When not to: the export has long IDs you cannot verify, or the query must run unattended on a server.

Why the IDs matter: 25,000 orders became 685

It is tempting to treat the ID problem as cosmetic. It is not. We converted our 25,000 IDs to 64-bit floating point, the format RFC 8259 warns about, and counted what was left: 685 distinct values. At 1.84 × 10^18, neighboring doubles are 256 apart, so our IDs, which are 7 apart, pile onto shared values.

Excel’s rule is harsher. Microsoft’s leading zeros page says “any numbers past the 15th digit are rounded down to zero” and that codes of 16 digits or more need a text format. Keeping only the first 15 digits of our IDs left 18 distinct values for 25,000 orders. A VLOOKUP or XLOOKUP on that column returns the wrong order without an error.

The same page covers order numbers and ZIP codes: written as numbers, they lose their leading zeros, and formatting cannot bring them back. Our earlier guide on CSV imports that mangle zeros and dates covers that side in detail.

A script that writes two sheets and checks every value

We wanted a route that keeps every field, keeps orders without items, does not repeat totals and keeps IDs exact. That means two sheets. records has one row per order, with nested objects as dotted columns. Each list of objects (here items) gets its own sheet, one row per element, with a _parent column holding the order’s ID.

Lists of plain values, like tags, are joined with ; . Columns come from every record. Integers longer than 15 digits are written as text, and strings stay strings, so 02134 survives. A string that starts with = is stored as text too, so a field from an outside API cannot turn into a live formula.

Before saving, the script counts the values in the input and the values it placed in cells. If they differ, it saves nothing. It also reopens the saved file and checks the row count of every sheet.

"""Turn a JSON export (a list of records) into an .xlsx file without losing fields.

Usage: python json2xlsx.py input.json output.xlsx [--root data.items] [--id id]

One row per record on the "records" sheet. Nested objects become dotted columns
(customer.address.zip). Lists of objects get their own sheet, one row per
element, with a _parent column holding the record's --id value (or its row
number on the records sheet). Lists of plain values are joined with "; ".
Columns come from every record, not a sample. Text stays text, and integers
longer than 15 digits are written as text. Before saving it checks that every
value in the input landed in a cell, and saves nothing if one did not.
"""
import json, sys
from openpyxl import Workbook, load_workbook

MAX_ROWS, MAX_COLS, MAX_CELL = 1_048_576, 16_384, 32_767

def die(msg):
    sys.exit(f"error: {msg}")

def leaves(x):
    """Count the values in x; an empty object or list counts as one (an empty cell)."""
    if isinstance(x, dict) and x: return sum(leaves(v) for v in x.values())
    if isinstance(x, list) and x: return sum(leaves(v) for v in x)
    return 1

def put(row, col, val, weight=1):
    if col in row:
        die(f"two fields flatten to the same column name '{col}'")
    if isinstance(val, int) and not isinstance(val, bool) and len(str(abs(val))) > 15:
        val = str(val)  # spreadsheets keep 15 significant digits; keep long IDs exact as text
    if isinstance(val, str) and len(val) > MAX_CELL:
        die(f"a value in '{col}' is longer than {MAX_CELL} characters")
    row[col] = (val, weight)

def flatten(obj, prefix, row, children, link):
    """children is a dict for top-level records and None inside a child row."""
    for key, val in obj.items():
        col = f"{prefix}{key}"
        if isinstance(val, dict) and val:
            flatten(val, col + ".", row, children, link)
        elif isinstance(val, list) and val and all(isinstance(v, dict) for v in val):
            if children is None:  # a list inside a list: keep it as JSON text in one cell
                put(row, col, json.dumps(val, ensure_ascii=False), leaves(val))
                continue
            for v in val:
                child = {"_parent": (link, 0)}
                flatten(v, "", child, None, link)
                children.setdefault(col, []).append(child)
        elif isinstance(val, list) and val:
            text = "; ".join(v if isinstance(v, str) else json.dumps(v, ensure_ascii=False) for v in val)
            put(row, col, text, leaves(val))
        else:
            put(row, col, None if val in ({}, []) else val)

def main(args):
    if len(args) < 2 or len(args) % 2:
        die(__doc__)
    src, out, opts = args[0], args[1], dict(zip(args[2::2], args[3::2]))
    with open(src, encoding="utf-8") as f:
        data = json.load(f)
    for part in filter(None, opts.get("--root", "").split(".")):
        data = data[part]
    data = [data] if isinstance(data, dict) else data
    if not isinstance(data, list) or not data:
        die("no records found; check --root")
    if not all(isinstance(r, dict) for r in data):
        die("expected a list of objects")
    id_key, records, children = opts.get("--id"), [], {}
    for n, rec in enumerate(data, start=2):  # row 2 is the first data row under the header
        link = rec.get(id_key) if id_key else n
        if link is None:
            die(f"record on row {n} has no '{id_key}'")
        if isinstance(link, int) and len(str(abs(link))) > 15:
            link = str(link)
        row = {}
        flatten(rec, "", row, children, link)
        records.append(row)

    wb, written, shape = Workbook(), 0, {}
    wb.remove(wb.active)
    for title, rows in {"records": records, **children}.items():
        cols = list(dict.fromkeys(c for r in rows for c in r))  # every key, first-seen order
        if len(cols) > MAX_COLS or len(rows) + 1 > MAX_ROWS:
            die(f"'{title}' needs {len(rows) + 1} rows and {len(cols)} columns, over Excel's limits")
        name = title[:31].translate(str.maketrans("[]:*?/\\", "_______"))
        if name in shape:
            die(f"two sheets would both be named '{name}'")
        ws = wb.create_sheet(name)
        ws.append(cols)
        for i, r in enumerate(rows, start=2):
            values = [r[c][0] if c in r else None for c in cols]
            ws.append(values)
            for j, v in enumerate(values, start=1):
                if isinstance(v, str) and v.startswith("="):  # keep it text, never a formula
                    ws.cell(row=i, column=j).data_type = "s"
            written += sum(w for _, w in r.values())
        shape[name] = len(rows) + 1
        print(f"{name}: {len(rows)} rows, {len(cols)} columns")
    expected = leaves(data)
    if written != expected:
        die(f"{expected} values in the input but {written} placed in cells; nothing saved")
    wb.save(out)
    back = load_workbook(out, read_only=True)
    for name, n in shape.items():
        if back[name].max_row != n:
            die(f"'{name}' reads back with {back[name].max_row} rows, expected {n}")
    print(f"saved {out}: {expected} values, all placed and read back")

if __name__ == "__main__":
    main(sys.argv[1:])

Run it on your export

Install the one dependency and point the script at your file:

python3 -m pip install openpyxl
python3 json2xlsx.py orders.json orders.xlsx --id id

If the records sit inside a wrapper, as in {"data": {"items": [...]}}, add --root data.items. On our test file it printed:

records: 25000 rows, 15 columns
items: 37000 rows, 4 columns
saved orders.xlsx: 401084 values, all placed and read back

The final run took 4.9 seconds.

We opened the result in LibreOffice and read it back with openpyxl. The records sheet had all 25,000 orders, with discount_code on the last one and IDs as exact text. Zip codes kept their zeros, and the total column summed to 1,843,984.02. The items sheet had 37,000 rows pointing at 18,500 distinct parent IDs, so the orders with no items are still on the first sheet, counted once.

We then broke it on purpose. A copy of the script edited to skip discount_code stopped with 401084 values in the input but 401083 placed in cells; nothing saved, and no file was written.

An empty list stops with no records found; check --root. A record with both a literal "a.b" key and a nested a → b stops on the name clash instead of overwriting one with the other. A record without the --id field stops and names its row. A test value of =HYPERLINK("http://x") was saved as text, not as a formula.

Know its limits before you rely on it. It loads the whole file into memory, so for exports of several gigabytes DuckDB with sample_size=-1 is the better tool. It makes child sheets for one level of lists only; a list inside a line item is kept as JSON text in one cell.

Type drift is kept, not resolved: like pandas, it gives shipping for the strings and shipping.method and shipping.cost for the objects, and you decide how to merge them. Nested values are not converted, so timestamps stay as the text the API sent.

If the JSON is only a stop on the way from email to a sheet, our guide to getting email data into Excel may cut the step out. If the conversion runs on a schedule, our guide to small automations in code covers running a script like this one unattended.

Which route to use

• Someone in Excel will refresh it every week: Power Query, with ID columns set to Text and the expand steps checked after any change to the export. Untested by us; verify one long ID by eye.
• A one-off look at a small export: jq with the every-leaf-path recipe, never the first-record recipe.
• Large files, or you want to filter and join first: DuckDB with sample_size=-1, IDs cast to VARCHAR, and line items in their own query rather than an unnest that drops empty orders.
• Python already in your stack and one row per record is enough: pandas json_normalize with no record_path, then decide what to do with the list columns.
• The workbook goes to people who will sum, filter and look things up: a two-sheet layout like the script above, so every order appears once and every value is accounted for.

Whichever you pick, run four checks on the result. Compare the row count with the record count, and the sum of one money column with the source. Compare one long ID with the raw JSON.

Then compare the column list with every key in the file; the every-leaf-path jq recipe prints them. On our file, those four would have flagged every lost order, repeated total, broken ID and missing field in the table.

How we made this

On October 23, 2026 we generated a sample export of 25,000 fake orders with a Python script and ran it through pandas 3.1.0, jq 1.7, DuckDB 2.0.0 and our own script (openpyxl 3.1.5) on Linux, opening results in LibreOffice 24.2; every count and error message above comes from those runs. We read the pandas, jq and DuckDB documentation, RFC 8259 and Microsoft’s Excel and Power Query pages on the same day. We did not have Excel or Power Query, so those steps come from Microsoft’s documentation and were not tried, and we tested on our own sample data, not a real company export.

Sources

• pandas: json_normalize reference, version 3.1.0
• jq: jq 1.7 manual
• DuckDB: Loading JSON, version 2.0
• IETF: RFC 8259, The JSON Data Interchange Format, section 6
• Microsoft: Excel specifications and limits
• Microsoft: Keep leading zeros and large numbers
• Microsoft Learn: Power Query JSON connector
• Microsoft: Work with a List, Record, or Table structured column
• Microsoft Learn: Data types in Power Query
• Microsoft Learn: Table.ExpandListColumn and Table.ExpandRecordColumn