How to Convert JSON to Excel in Python (pandas, json_normalize, and openpyxl)

Python is the right tool for this when the conversion needs to happen more than once — a nightly export, a batch of a hundred files, or an API response that changes shape and needs handling in code rather than by clicking.

Everything below uses pandas and openpyxl. Install both:

pip install pandas openpyxl

openpyxl is the engine pandas uses to write .xlsx files. You will not import it directly in most scripts, but without it to_excel() raises ModuleNotFoundError.

The simple case: a flat array of objects

If your JSON looks like this:

[
  { "id": 1, "name": "Alice", "role": "admin" },
  { "id": 2, "name": "Bob", "role": "editor" }
]

then three lines is genuinely all you need:

import pandas as pd

df = pd.read_json("data.json")
df.to_excel("output.xlsx", index=False)

index=False matters. Without it, pandas writes its row index as an unnamed first column, and you get a stray column of 0, 1, 2 in the spreadsheet that nobody asked for.

The real case: nested objects

Most API responses look more like this:

[
  {
    "id": 1,
    "name": "Alice",
    "address": { "city": "London", "country": "UK" }
  }
]

pd.read_json() will happily load that, but the address column will contain Python dictionaries. When written to Excel, each cell reads {'city': 'London', 'country': 'UK'} — technically the data, practically useless.

The fix is json_normalize:

import json
import pandas as pd

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

df = pd.json_normalize(data)
df.to_excel("output.xlsx", index=False)

This produces address.city and address.country as separate columns. It recurses to any depth, so an object five levels down becomes a.b.c.d.e.

Changing the separator

If dots are awkward downstream — some tools treat address.city as a formula reference — use a different separator:

df = pd.json_normalize(data, sep="_")
# produces address_city, address_country

Digging into a wrapped response

APIs rarely return a bare array. They wrap it in metadata:

{
  "status": "ok",
  "page": 1,
  "results": [
    { "id": 1, "name": "Alice" },
    { "id": 2, "name": "Bob" }
  ]
}

Passing this straight to json_normalize gives you one row with a results column containing a list. Use record_path to point at the array that holds your rows:

df = pd.json_normalize(data, record_path="results")

To keep fields from the wrapper alongside each row, add meta:

df = pd.json_normalize(
    data,
    record_path="results",
    meta=["status", "page"]
)

Now every row carries its own status and page value — useful when you concatenate several pages of a paginated API into one sheet.

Arrays of objects inside each row

This is the structure that causes the most trouble:

[
  {
    "order_id": 1001,
    "customer": "Alice",
    "items": [
      { "sku": "A-1", "qty": 2 },
      { "sku": "B-7", "qty": 1 }
    ]
  }
]

There is no single correct flattening here, because one order has many items. You have to decide what a row means. Two options:

One row per item (long format)

df = pd.json_normalize(
    data,
    record_path="items",
    meta=["order_id", "customer"]
)

Gives two rows, each with order_id, customer, sku and qty. The order fields repeat. This is what you want for pivot tables and analysis.

One row per order (wide format)

df = pd.json_normalize(data)
df = df.join(
    pd.DataFrame(df.pop("items").tolist())
      .add_prefix("item_")
)

Gives one row per order with item_0, item_1 columns. Keeps orders on single lines but produces ragged columns when item counts differ. Use this only when the maximum item count is small and predictable.

Controlling data types

pandas infers types, and its guesses can quietly corrupt identifiers. A column of values like "00721" becomes the integer 721. Zip codes, product codes and account numbers all lose their leading zeros.

Force them to stay strings:

df = pd.json_normalize(data)
df["zip_code"] = df["zip_code"].astype(str)
df["product_code"] = df["product_code"].astype(str)

Note that this alone is not enough — Excel will re-interpret the value when the file opens. To make the text stick, write the column with an explicit format:

with pd.ExcelWriter("output.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, index=False, sheet_name="Data")
    sheet = writer.sheets["Data"]
    for row in sheet.iter_rows(min_row=2, min_col=3, max_col=3):
        for cell in row:
            cell.number_format = "@"   # @ means text

Dates

JSON has no date type — dates arrive as strings. Convert them explicitly so Excel treats them as dates rather than text:

df["created_at"] = pd.to_datetime(df["created_at"], errors="coerce")

errors="coerce" turns unparseable values into NaT (missing) instead of raising. That is usually what you want in a batch job — one malformed row should not stop the run. Check afterwards with df["created_at"].isna().sum().

Writing multiple sheets

When your JSON has several distinct collections, put each on its own sheet rather than forcing them into one grid:

with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    pd.json_normalize(data["users"]).to_excel(
        writer, sheet_name="Users", index=False)
    pd.json_normalize(data["orders"]).to_excel(
        writer, sheet_name="Orders", index=False)
    pd.json_normalize(data["products"]).to_excel(
        writer, sheet_name="Products", index=False)

Converting a folder of files

from pathlib import Path
import json
import pandas as pd

frames = []
for path in Path("exports").glob("*.json"):
    with path.open(encoding="utf-8") as f:
        frames.append(pd.json_normalize(json.load(f)))

combined = pd.concat(frames, ignore_index=True)
combined.to_excel("combined.xlsx", index=False)

pd.concat aligns columns by name and fills gaps with NaN, so files with slightly different fields still combine cleanly. Add sort=False if you want to preserve first-seen column order.

The 1,048,576 row limit

An .xlsx worksheet holds 1,048,576 rows and 16,384 columns. Exceeding either raises an error from openpyxl. If you are near that ceiling, either split across sheets or reconsider the format — CSV or Parquet handle large volumes far better, and nobody meaningfully analyses a million rows by scrolling.

CHUNK = 1_000_000
with pd.ExcelWriter("big.xlsx", engine="openpyxl") as writer:
    for i in range(0, len(df), CHUNK):
        df.iloc[i:i + CHUNK].to_excel(
            writer, sheet_name=f"Part{i // CHUNK + 1}", index=False)

For files large enough that loading them at all is the problem, see working with large JSON files.

Common errors

When not to use Python

If this is a one-off — someone sent you a file and you need it in a spreadsheet this afternoon — installing pandas and writing a script is more work than the task deserves. A browser-based converter does the same flattening in a few seconds with nothing to install. Reach for Python when the conversion is going to repeat, needs to run unattended, or requires type handling that a general-purpose tool cannot know about.