Guide

Getting Started With Python Excel AutomationDeep dive

Convert an Excel Sheet to JSON with Python

Q: Which orient should I use?

records for almost every API — a list of objects, one per row. columns or split are useful when the consumer is another DataFrame, and index only when row labels carry meaning.

Q: How do I get null instead of NaN?

Convert with to_json, which writes null automatically. If you build the structure yourself, replace NaN with None first — json.dumps writes the literal NaN otherwise, and that is not valid JSON.

Q: How do I convert every sheet in one go?

Read with sheet_name=None to get a dictionary of DataFrames, then build a dictionary of records keyed by sheet name and dump it once.

Turn a worksheet into JSON: records versus columns, dates and NaN handling, nested output, every sheet at once, JSON Lines for large files, and validating the result.

Sending spreadsheet data to an API, a JavaScript front end or a message queue means converting it to JSON, and the conversion is where the data's quiet problems become loud: dates turn into epoch numbers, empty cells become NaN that no JSON parser accepts, and numeric-looking identifiers lose their leading zeros. This guide converts a sheet properly — clean types, ISO dates, real nulls — then covers nested output, whole workbooks, and the streaming format for files too big for one array. It belongs to Handling Excel File Formats and Conversions.

Prerequisites

Bash

pip install pandas openpyxl

The straightforward conversion

to_json handles the whole job when the types are already right:

Python

import pandas as pd

df = pd.read_excel("orders.xlsx", engine="openpyxl")
df.to_json("orders.json", orient="records", indent=2, date_format="iso")

orient="records" produces a list of objects — one per row — which is what an API or a front end expects. date_format="iso" is the argument that stops datetimes becoming epoch milliseconds, and it should be in essentially every call.

Get the types right before converting

JSON has no way to record that 00417 was meant to be text, so the fix belongs at the read:

Python

import pandas as pd

df = pd.read_excel(
    "orders.xlsx",
    engine="openpyxl",
    dtype={"order_id": "string", "postcode": "string"},   # keep leading zeros
    parse_dates=["ordered"],
)
df["total"] = pd.to_numeric(df["total"], errors="coerce")
print(df.dtypes)

Without dtype, 00417 becomes the number 417 and the JSON says 417 — a value that will never match the source system's records again. The wider problem is covered in Convert Excel text columns to numbers with pandas.

Deal with empty cells

Empty cells become NaN, which is a float, not a null. to_json renders it as null correctly, but the moment you build the structure yourself, json.dumps writes the literal NaN — which is not valid JSON and will be rejected by strict parsers:

Python

import json

import pandas as pd

df = pd.read_excel("orders.xlsx")

records = df.astype(object).where(df.notna(), None).to_dict(orient="records")
print(json.dumps(records[:2], indent=2, default=str))

where(df.notna(), None) is the idiom worth memorising: it replaces every missing value with None, which serialises to null. default=str catches anything else json cannot handle — a Decimal, a Timestamp, a date.

Choose the shape the consumer wants

orient decides the structure, and the right choice depends entirely on what reads it:

orient	Shape	Use for
`records`	`[{col: val}, …]`	APIs, front ends, message payloads
`columns`	`{col: {idx: val}}`	Reconstructing a DataFrame
`split`	`{index, columns, data}`	Compact round trips
`index`	`{idx: {col: val}}`	Row labels that carry meaning
`values`	`[[val, …], …]`	Plain matrices, no headers

Python

print(df.head(2).to_json(orient="records", date_format="iso"))
print(df.head(2).to_json(orient="split", date_format="iso"))

Convert every sheet at once

Python

import json

import pandas as pd

sheets = pd.read_excel("workbook.xlsx", sheet_name=None, engine="openpyxl")
payload = {
    name: frame.astype(object).where(frame.notna(), None).to_dict(orient="records")
    for name, frame in sheets.items()
}

with open("workbook.json", "w", encoding="utf-8") as fh:
    json.dump(payload, fh, indent=2, default=str, ensure_ascii=False)

ensure_ascii=False keeps accented characters and symbols readable instead of escaping them, which matters when a person will look at the output. Reading every sheet is covered in Read all sheets from an Excel file into DataFrames.

Build a nested structure

Flat rows rarely match an API's schema. Group first, then shape:

Python

import json

import pandas as pd

df = pd.read_excel("orders.xlsx", parse_dates=["ordered"])

payload = [
    {
        "region": region,
        "order_count": len(group),
        "revenue": round(float(group["total"].sum()), 2),
        "orders": [
            {"id": row.order_id, "ordered": row.ordered.date().isoformat(),
             "total": float(row.total)}
            for row in group.itertuples()
        ],
    }
    for region, group in df.groupby("region", sort=True)
]

print(json.dumps(payload[:1], indent=2))

itertuples() is the fast iteration path — several times quicker than iterrows() — and matters once the sheet has more than a few thousand rows.

Stream large sheets as JSON Lines

One giant array must be parsed in full before anything can be read. JSON Lines — one object per line — streams, appends and splits:

Python

import pandas as pd

df = pd.read_excel("large.xlsx", engine="calamine")
df.to_json("large.jsonl", orient="records", lines=True, date_format="iso")

Read it back a line at a time, so memory stays flat regardless of file size:

Python

import json

with open("large.jsonl", encoding="utf-8") as fh:
    for line in fh:
        record = json.loads(line)
        handle(record)

Every mainstream data tool reads this format, and it is what most log and event pipelines expect.

Validate the output before shipping it

If the JSON feeds an API, check it against the contract rather than discovering the mismatch downstream:

Python

from datetime import date

REQUIRED = {"order_id": str, "ordered": str, "total": float}

def validate(records: list[dict]) -> None:
    for i, record in enumerate(records):
        missing = REQUIRED.keys() - record.keys()
        if missing:
            raise ValueError(f"row {i}: missing {sorted(missing)}")
        for key, kind in REQUIRED.items():
            value = record[key]
            if value is not None and not isinstance(value, kind):
                raise TypeError(f"row {i}: {key} is {type(value).__name__}, expected {kind.__name__}")

For anything more elaborate, a schema library does this properly — see Validate Excel data with pandera schemas.

Convert back, and check the round trip

The reverse direction matters as much, because a service that accepts JSON and returns a workbook is the same pipeline pointed the other way:

Python

import json

import pandas as pd

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

df = pd.json_normalize(records)          # flattens nested objects into columns
df.to_excel("orders_roundtrip.xlsx", index=False, engine="xlsxwriter")

json_normalize is the piece that handles nesting: an object like {"customer": {"name": "Acme"}} becomes a customer.name column rather than a cell containing a Python dictionary repr. Pass record_path and meta when the payload has a list inside each object — for example orders inside regions — and it explodes them into rows while carrying the parent fields along.

A round-trip check is the cheapest way to prove the conversion is lossless for the fields you care about:

Python

original = pd.read_excel("orders.xlsx", dtype={"order_id": "string"})
restored = pd.read_excel("orders_roundtrip.xlsx", dtype={"order_id": "string"})

assert list(original.columns) == list(restored.columns)
assert original["order_id"].tolist() == restored["order_id"].tolist()
assert abs(original["total"].sum() - restored["total"].sum()) < 0.01

Keep column names stable

JSON keys are an interface. A header that reads "Total (GBP)" in the spreadsheet becomes an awkward key, and it changes the moment somebody edits the header text. Map the sheet's headers to fixed key names rather than passing them through:

Python

KEYS = {
    "Order ID": "order_id",
    "Ordered": "ordered",
    "Total (GBP)": "total",
    "Customer name": "customer_name",
}

missing = KEYS.keys() - set(df.columns)
if missing:
    raise ValueError(f"sheet is missing expected headers: {sorted(missing)}")

df = df.rename(columns=KEYS)[list(KEYS.values())]

The check before the rename is what turns a silent break into a clear error: if a producer relabels a column, the export fails with the header name rather than shipping JSON with a missing field that a consumer discovers days later.

Common pitfalls and gotchas

Epoch-millisecond dates. Always pass date_format="iso" unless the consumer genuinely wants numbers.
NaN in hand-built JSON. Replace with None before json.dumps; NaN is not valid JSON.
Lost leading zeros. Set dtype="string" on identifier columns at the read.
Decimal and Timestamp objects. json.dumps cannot serialise them; pass default=str or convert explicitly.
Duplicate column headers. pandas renames them col, col.1; JSON objects cannot hold duplicate keys either, so decide the naming deliberately.

Performance and scale notes

The Excel parse dominates, so use engine="calamine" for large inputs. Beyond that, to_json is fast and json.dumps over a list of dictionaries is noticeably slower — prefer the pandas path unless you need custom shaping. For files above a few hundred thousand rows, write JSON Lines and process it in a stream; a single array of that size costs several times the file's size in memory when parsed. If the destination is analytical rather than an API, Parquet is a better target than JSON in every dimension — smaller, typed and faster — as covered in Convert Excel files to Parquet with Python.

Conclusion

A clean Excel-to-JSON conversion is mostly about the read: declare the types, parse the dates, and replace missing values with None. Then pick the orient that matches the consumer, use ISO dates, and switch to JSON Lines once the file is large. Validate the result against the contract it has to satisfy, and the conversion stops being a source of downstream surprises.

Frequently asked questions

Which orient should I use?records for almost every API — a list of objects, one per row. columns or split are useful when the consumer is another DataFrame, and index only when row labels carry meaning.

Why do my dates come out as huge numbers? Those are epoch milliseconds, pandas' default for datetimes in JSON. Pass date_format="iso" to get ISO 8601 strings, which is what almost every consumer expects.

How do I get null instead of NaN? Convert with to_json, which writes null automatically. If you build the structure yourself, replace NaN with None first — json.dumps writes the literal NaN otherwise, and that is not valid JSON.

How do I convert every sheet in one go? Read with sheet_name=None to get a dictionary of DataFrames, then build a dictionary of records keyed by sheet name and dump it once.

What about a sheet with a million rows? Write JSON Lines — one object per line — instead of one array. It streams, it can be appended to, and every big-data tool reads it.

Up: Handling Excel File Formats and Conversions — the other conversions in and out of spreadsheet formats.
Convert Excel to CSV with Python — the flat-text equivalent, and when it is the better target.
Read an Excel file from a URL or bytes in Python — getting the workbook in when it arrives over HTTP.
Fetch API data into Excel with Python requests — the same trip in the opposite direction.
Convert Excel files to Parquet with Python — the better destination when the consumer is analytical.