Converting Excel to JSON With Python for Clean Output

JSON is the format the web speaks, and a lot of spreadsheets exist because someone wanted data that an API now needs. Turning a sheet into JSON looks like a one-liner and mostly is. The trouble is the details a one-liner hides: header cells that make poor keys, dates that become integers, empty cells that become NaN, identifiers that lose their zeros, and integers that turn into floats. This post builds a conversion that is explicit about each, shows records, nested and line-delimited output, and ends with the checks. It is a spoke of converting Excel to CSV, JSON, XML and YAML.

The sample

A sheet Orders:

The goal is:

[
  {"order_no": "0007", "order_date": "2025-03-14", "customer": "Acme", "qty": 2, "notes": null},
  {"order_no": "0012", "order_date": "2025-03-15", "customer": "Globex", "qty": null, "notes": "rush"}
]

Five decisions are in that output: lower-case keys with underscores, the identifier kept as text, the date as an ISO string, the integer quantity kept as an integer with a null for the blank, and empty cells as null.

Make the keys

Header cells are written for people: Order No, Unit Price ($), a trailing space. They make awkward JSON keys. Convert them with one function and check the result for duplicates:

import re
import pandas as pd

def key(h):
    s = re.sub(r"[^0-9a-zA-Z]+", "_", str(h).strip()).strip("_").lower()
    return s or "col"

df = pd.read_excel("orders.xlsx", sheet_name="Orders", dtype={"Order No": str})
df.columns = [key(c) for c in df.columns]
assert len(set(df.columns)) == len(df.columns), "duplicate keys after cleaning"

Two headers can collapse to one key (Amount ($) and Amount (%) both become amount), so check uniqueness after the change. Keep the original header text somewhere, such as a separate mapping file, if people need to relate the JSON back to the sheet. A header that starts with a digit is fine in JSON; it is a nuisance only in languages that build objects from keys.

Types

Set the types before writing. The values pandas infers are often right and sometimes wrong.

df["order_date"] = pd.to_datetime(df["order_date"]).dt.strftime("%Y-%m-%d")
df["qty"] = df["qty"].astype("Int64")                  # nullable integer
df["notes"] = df["notes"].astype("string")

JSON has no date type. Dates are written as ISO 8601 strings by convention (2025-03-14 or 2025-03-14T09:30:00Z for date-times with a zone). pandas' own to_json writes dates as epoch milliseconds by default, which most consumers do not expect; pass date_format="iso" or convert yourself as above. State the time zone for date-times, because Excel has none (converting Excel serial dates to Python datetimes).

An integer column that contains blanks is read as float64 (because NaN is a float), so 2 becomes 2.0. The nullable Int64 type keeps integers and uses <NA> for blanks, which to_json writes as null. Without it, the JSON would show 2.0, and a strict consumer may reject a float where an integer is expected.

Identifiers must be strings: dtype={"Order No": str} on the read preserves 0007. If the cell held a number, the zeros are already gone (keeping leading zeros in Excel). Large integers beyond 2^53 are not safe as JSON numbers, since many parsers (notably JavaScript's) use doubles; write them as strings. Monetary amounts: either numbers with a fixed number of decimals or strings, and say which; floating-point representation can introduce 0.1 + 0.2 type noise, so round before writing.

Write the records

df.to_json("orders.json", orient="records", force_ascii=False, indent=2)

orient="records" gives an array of objects. force_ascii=False keeps accented characters as themselves, not é escapes. indent=2 is for humans and doubles the size; omit it for machines. Missing values are written as null. Because to_json has some surprising behaviors (it escapes forward slashes by default as \/; use json.dumps if that matters), many people prefer the standard library for final control:

import json

records = json.loads(df.to_json(orient="records", date_format="iso"))
with open("orders.json", "w", encoding="utf-8") as f:
    json.dump(records, f, ensure_ascii=False, indent=2)

Round-tripping through to_json and loads gives plain Python types (None for nulls) that json.dump writes cleanly. Decimal values need default=str or a conversion first. Never use NaN in JSON; it is not valid JSON, though Python's json module will write it by default. Pass allow_nan=False to json.dump to make that an error.

Nested JSON from repeated parents

Many sheets repeat the parent on each row: an order number and customer on every line of the order. An API usually wants the lines nested under the order:

nested = []
for (order_no, customer), g in df.groupby(["order_no", "customer"], sort=False):
    nested.append({
        "order_no": order_no,
        "customer": customer,
        "lines": g[["sku", "qty"]].to_dict(orient="records"),
    })

[
  {"order_no": "0007", "customer": "Acme", "lines": [{"sku": "A1", "qty": 2}, {"sku": "B2", "qty": 1}]},
  {"order_no": "0012", "customer": "Globex", "lines": [{"sku": "A1", "qty": 5}]}
]

Check that the parent fields really are the same across the rows of one parent. Group by the key only and look for conflicts: df.groupby("order_no")["customer"].nunique().max() should be 1. If a parent has two customer names, the nesting hides an inconsistency. The reverse conversion, flattening, is covered in flattening nested JSON in pandas. If a sheet has a column of delimited values (red, blue), split it into a JSON array on purpose.

JSON Lines for large data

A single JSON array must be parsed as a whole. For large conversions use JSON Lines, one object per line, which streams and appends:

df.to_json("orders.jsonl", orient="records", lines=True, force_ascii=False, date_format="iso")

Each line is a complete JSON object. Consumers can read it line by line, split it into chunks and load it into databases and tools that understand it (JSON Lines for data pipelines). For a sheet too large for pandas, stream with openpyxl read-only mode and write each row with json.dumps (reading a 500 MB Excel file without running out of memory).

Several sheets

A workbook with several sheets can become one JSON file with an object keyed by sheet name, or one file per sheet:

sheets = pd.read_excel("workbook.xlsx", sheet_name=None)
out = {name: json.loads(df.to_json(orient="records", date_format="iso")) for name, df in sheets.items()}

Prefer one file per sheet when the sheets have different shapes or sizes. Sanitize sheet names used as keys or file names.

Validate the output

A conversion is not finished until it has been checked.

1. Counts: the number of objects equals the number of data rows in the sheet.

2. Keys: every object has the same set of keys (a schema check), and none is null where a value is required.

3. Types: dates parse, integers are integers, identifiers are strings.

4. Totals: the sum of qty in the JSON equals Excel's.

5. A schema: write a JSON Schema for the output and validate each record, which makes the expectations explicit and reusable (validating JSON in Python with jsonschema).

6. Spot checks of the first, last and a messy row.

Compare also a round trip: load the JSON back into pandas and compare it with the cleaned DataFrame.

When JSON is the wrong target for a sheet

The data is flat and tabular. CSV or Parquet is smaller and loads into databases directly.

The sheet is a report. Merged cells, subtotals and titles do not map to a clean structure; clean first (cleaning Excel data before analysis).

Order and formatting matter. JSON objects have unordered keys, and cell styles are lost.

Consumers need streaming of huge files. Use JSON Lines or a columnar format.

See the pandas reference for DataFrame.to_json and the Python documentation for the json module. To check a small sample, use the CSV to JSON converter or the JSON formatter.