How to convert nested JSON to Excel with multiple sheets

Why nested JSON breaks a flat sheet

Take two orders, each with a list of items:

{
  "orders": [
    { "id": 1234567890123456789, "status": "shipped", "total": 27.50,
      "items": [ { "sku": "MUG-RED",  "qty": 2, "price": 8.75 },
                 { "sku": "TEA-500G", "qty": 1, "price": 10.00 } ] },
    { "id": 1234567890123456790, "status": "packing", "total": 12.50,
      "items": [ { "sku": "TEA-500G", "qty": 1, "price": 10.00 },
                 { "sku": "SPOON",    "qty": 2, "price": 1.25 } ] }
  ]
}

A sheet is flat, so a simple converter picks one of two bad shapes.

Bad shape 1: a column for every item

An order with 30 items adds 60 columns. You also cannot total qty, because it is spread over many columns.

Bad shape 2: a row for every item

Each order total now appears twice. A sum of the total column gives 80.00, but the real total is 40.00. With two lists on one order, such as items and payments, the rows multiply: 3 items and 2 payments make 6 rows.

The shape that works: one sheet per list

Give each list its own sheet. Put the parent's ID on every row of the child sheet. A database stores the same data this way, and Excel's lookups and pivot tables work on it.

Sheet: orders

Sheet: items

Three ways to do it

Method 1: jsonloupe, with no code

1. Open the JSON to Excel converter.
2. Paste your JSON, or open the file.
3. Check the sheets it found. You see real rows from your file, and you can rename headings or drop columns.
4. Download one .xlsx file, or one CSV per sheet.

The conversion runs in your browser, so the file is not uploaded. An ID past 15 digits goes into a text cell, so Excel cannot round it. You can save the setup, and next month's file gives you the same sheets.

Method 2: Power Query in Excel

1. Select Data → Get Data → From File → From JSON and choose the file.
2. Select To Table, then use the expand arrow to show the fields.
3. Duplicate the query, so you have one for orders and one for items.
4. In the orders query, remove the items column.
5. In the items query, keep id and items, then expand items to new rows and expand the fields.
6. Select Close & Load to put each query on its own sheet.

Power Query reads JSON numbers as floats, so an ID past 15 digits can lose digits before it reaches a cell. If your IDs are that long, ask for them as strings or use another method.

Method 3: Python and pandas

import json
import pandas as pd

with open('orders.json') as f:
    orders = json.load(f)['orders']

order_sheet = pd.json_normalize(orders).drop(columns='items')
item_sheet = pd.json_normalize(
    orders, record_path='items', meta=['id'], meta_prefix='order_'
)

# Text cells keep every digit. Number cells keep 15.
order_sheet['id'] = order_sheet['id'].astype(str)
item_sheet['order_id'] = item_sheet['order_id'].astype(str)

with pd.ExcelWriter('orders.xlsx') as writer:
    order_sheet.to_excel(writer, sheet_name='orders', index=False)
    item_sheet.to_excel(writer, sheet_name='items', index=False)

This needs the pandas and openpyxl packages. Add one json_normalize call for each extra list.

Join the sheets back in Excel

With order_id in column A of the items sheet, this finds the status of each item's order:

=XLOOKUP(A2, orders!A:A, orders!B:B)

Do not use COUNTIF or SUMIF on long IDs. These functions turn text that looks like a number back into a number, so they compare only the first 15 digits. In the example above, they treat both orders as the same order. To count exact matches, compare the cells directly:

=SUMPRODUCT(--(A2:A100=A2))

Common questions

Why does Excel show 1.23457E+18?

The cell holds a number, and Excel keeps only 15 significant digits. The rest become zeros, and you cannot get them back from that cell. The ID must be text before it reaches Excel. Long IDs have the same problem in code.

What happens to a plain list, such as "tags": ["a", "b"]?

A list of plain values does not need a sheet. jsonloupe joins it into one cell: a; b.

What if the parent has no ID?

jsonloupe adds a _parent_row column that holds the row of the parent, so the sheets still join.

How many rows fit on a sheet?

An Excel sheet holds 1,048,576 rows, including the heading. jsonloupe tells you before it writes the file if a list is too long for one sheet.

Try it on your own file. The converter runs in your browser, with no account and no upload. Open the converter →

More guides

• Why JSON.parse rounds long IDs, and how to fix it
• How to open a large JSON file