Python Excel AutomationPractical guides for developers
Fix
Getting Started With Python Excel AutomationDeep dive

Fix "Object of type Timestamp is not JSON serializable" When Exporting Excel Data

json.dumps fails on rows read from Excel because pandas hands you Timestamp, numpy int64 and NaN values that the json module does not understand — and NaN that it does write produces invalid JSON. Fix it with to_json and ISO dates, a small default= converter, or a clean conversion step, and check the output parses in strict JSON readers.
Errors & fixesFile formats & conversion

A common pipeline reads a sheet with pandas and posts the rows to an API, writes them to a message queue, or saves them as a JSON file. The obvious code — json.dumps(df.to_dict("records")) — fails on the first date with TypeError: Object of type Timestamp is not JSON serializable. Fix that, and the next run fails with Object of type int64 is not JSON serializable; fix that, and the JSON is written but the receiving JavaScript service rejects it because it contains NaN. All three come from the same mismatch: pandas uses its own and NumPy's types for performance, and Python's json module only knows the built-in types. This page shows each failure and three ways to fix it. It belongs to Troubleshooting Common Python Excel Errors.

Prerequisites

Bash
pip install pandas openpyxl

visits.xlsx has an integer ID, text with accents, a date-time, a time of day, an amount with one blank, and a follow-up date with one blank — a typical mix for a sheet that feeds another system.

Step 1 — Reproduce the three failures

Python
import json
import pandas as pd

df = pd.read_excel("visits.xlsx")
print(df.dtypes.to_dict())

for label, payload in [("records", df.to_dict("records")),
                       ("one scalar", {"id": df.loc[0, "VisitID"]})]:
    try:
        json.dumps(payload)
    except TypeError as exc:
        print(f"{label:10} TypeError: {exc}")

print(json.dumps({"amount": df.loc[1, "Amount"]}))

to_dict("records") hands back Timestamp objects for the date-time column, which json cannot encode. Pulling a single value out with .loc gives a NumPy int64, which fails the same way — to_dict happens to convert integers to Python int, so this one tends to appear in code that builds payloads by hand. The third case does not fail at all: the blank amount is NaN, and json.dumps writes it as the bare word NaN, which is not valid JSON. Python's own json.loads accepts it; JavaScript's JSON.parse, most web frameworks and many databases reject the whole document.

Values from read_excel and what json.dumps does with them A table of value types produced by pandas from an Excel sheet. Python int, float, str and None encode fine. pandas Timestamp, numpy int64 and Decimal raise TypeError. NaN is written as the bare word NaN, which strict JSON parsers reject. datetime.time raises TypeError. json.dumps result int, float, str, None pandas Timestamp TypeError numpy int64 / float64 scalar TypeError (int64) datetime.date / datetime.time TypeError Decimal TypeError NaN NaN — invalid JSON

Step 2 — Fix 1: let pandas write the JSON

DataFrame.to_json knows every pandas type. Ask for ISO dates and records:

Python
text = df.to_json(orient="records", date_format="iso", force_ascii=False)
print(text)

Timestamps become "2026-09-01T10:30:00.000", missing values become null, numbers stay numbers and accented text stays readable with force_ascii=False. Always pass date_format="iso": the default writes dates as milliseconds since 1970 (1788258600000), which the receiver has to know to decode, and pandas 3 warns that this default is going away. Two details to check against what the receiver expects: date-only values from Excel come out as midnight date-times ("2026-09-15T00:00:00.000"), and every timestamp carries milliseconds. If the API wants "2026-09-15", format those columns as strings first (Step 4).

Step 3 — Fix 2: give json.dumps a converter

When the payload is built by hand — nested structures, metadata around the rows — pass a default function that json calls for any object it does not recognise:

Python
import datetime as dt
import decimal
import math
import numpy as np

def to_json_value(obj):
    if isinstance(obj, (pd.Timestamp, dt.datetime, dt.date, dt.time)):
        return obj.isoformat()
    if isinstance(obj, np.integer):
        return int(obj)
    if isinstance(obj, np.floating):
        return None if math.isnan(obj) else float(obj)
    if isinstance(obj, decimal.Decimal):
        return str(obj)
    if obj is pd.NaT or obj is pd.NA:
        return None
    raise TypeError(f"cannot serialise {type(obj).__name__}")

payload = {"source": "visits.xlsx", "rows": df.to_dict("records"), "first_id": df.loc[0, "VisitID"]}
try:
    print(json.dumps(payload, default=to_json_value, ensure_ascii=False, allow_nan=False))
except ValueError as exc:
    print("ValueError:", exc)

The converter handles the types that raised TypeError, but this call still fails — with ValueError: Out of range float values are not JSON compliant: nan — because allow_nan=False caught the NaN. That is the point of the flag: a plain Python float('nan') is a known type, so default is never asked about it, and without allow_nan=False it would slip into the output as NaN. Decimals are written as strings to keep every digit; switch to float(obj) if the receiver wants numbers. To make the hand-built payload pass, replace the missing values before building it:

Python
records = df.astype(object).where(df.notna(), None).to_dict("records")
payload = {"source": "visits.xlsx", "rows": records, "first_id": df.loc[0, "VisitID"]}
print(json.dumps(payload, default=to_json_value, ensure_ascii=False, allow_nan=False)[:160])

Now default turns the timestamp and the NumPy integer into JSON-friendly values, the NaN and NaT cells are already None, and allow_nan=False stays in place as a guard for any value that slips through later.

Step 4 — Fix 3: clean the frame before converting

The most predictable approach is to convert the DataFrame into exactly the shape the receiver wants, then serialise plain Python objects:

Python
clean = df.copy()
clean["Visited"] = clean["Visited"].dt.strftime("%Y-%m-%dT%H:%M:%S")
clean["Follow-up"] = pd.to_datetime(clean["Follow-up"]).dt.strftime("%Y-%m-%d")
clean["Start"] = clean["Start"].map(lambda t: t.strftime("%H:%M") if pd.notna(t) else None)
clean = clean.astype(object).where(clean.notna(), None)
rows = clean.to_dict("records")
print(json.dumps(rows, ensure_ascii=False, allow_nan=False, indent=1))

astype(object).where(notna, None) replaces every NaN and NaT with None, which becomes null. Each date column now has exactly the format the receiving system documents, and allow_nan=False passes because no NaN is left. This is more code than Fix 1, but every field's format is explicit and visible in review — which is what you want when a partner system parses the output.

A row before and after the cleaning step On the left the DataFrame row with a Timestamp, a NaN amount and a NaT follow-up date. On the right the JSON object with an ISO date-time string, null amount and null follow-up. field value Visited Timestamp(2026-09-02) Amount NaN Follow-up NaT key value Visited "2026-09-02T00:00:00" Amount null Follow-up null DataFrame row JSON clean, then json.dumps

Step 5 — Verify the output is strict JSON

Python's json.loads is lenient about NaN and Infinity, so testing with it proves little. Parse with a hook that refuses those constants — it behaves like JSON.parse:

Python
def strict_loads(text):
    def reject(name):
        raise ValueError(f"non-standard JSON constant: {name}")
    return json.loads(text, parse_constant=reject)

for label, text in [("to_json", df.to_json(orient="records", date_format="iso")),
                    ("cleaned", json.dumps(rows, allow_nan=False)),
                    ("naive", json.dumps({"amount": float("nan")}))]:
    try:
        strict_loads(text)
        print(f"{label:8} valid strict JSON")
    except ValueError as exc:
        print(f"{label:8} {exc}")

The to_json and cleaned outputs pass; the naive json.dumps of a NaN fails exactly as a JavaScript or Go service would. Put this check in the export's tests.

Which fix to use For a flat list of rows, use to_json with ISO dates. For a payload built by hand around the rows, use json.dumps with a default converter and allow_nan=False. When the receiver specifies exact field formats, clean the frame first and serialise plain Python values. What are you serialising? just the rows df.to_json(…) orient=records, date_format=iso a custom payload json.dumps(default=…) plus allow_nan=False strict field formats Clean first strftime, None for NaN

Step 6 — Large sheets: write JSON Lines

A single JSON array has to be built in memory and parsed in one go by the receiver. For sheets with hundreds of thousands of rows, JSON Lines — one JSON object per line — is easier on both sides, and pandas writes it directly:

Python
df.to_json("visits.jsonl", orient="records", lines=True, date_format="iso", force_ascii=False)
with open("visits.jsonl", encoding="utf-8") as fh:
    lines = fh.readlines()
for line in lines:
    strict_loads(line)
print(len(lines), "lines, each valid JSON")

Each line is a complete, strict JSON object, so a consumer can stream the file, process rows as they arrive, and resume after a failure at a known line. Most data tools — BigQuery, DuckDB, Elasticsearch bulk loads, pd.read_json(lines=True) — accept the format natively.

Performance notes

to_json is implemented in C and serialises hundreds of thousands of rows per second; json.dumps with a default function calls back into Python for every Timestamp and NumPy value and is several times slower on large frames. The cleaning approach in Step 4 sits in between: the vectorised strftime and where calls are fast, and json.dumps then only sees built-in types. For very large exports, prefer to_json(lines=True) written straight to a file over building one giant string in memory.

Common pitfalls

  • Testing with json.loads. It accepts NaN; use a strict check.
  • Relying on the epoch default. to_json without date_format="iso" writes milliseconds and warns in pandas 3.
  • Scalars from .loc and .iloc. They are NumPy types; convert with .item() or int().
  • Time-of-day values. openpyxl returns datetime.time for time-formatted cells; they need isoformat() or strftime.
  • Text encoding. ensure_ascii=True (the default) escapes ü as ü; valid, but pass ensure_ascii=False and write UTF-8 if people read the file.

Conclusion

json.dumps only understands Python's built-in types, and rows read from Excel contain pandas Timestamps, NumPy scalars, datetime.time, Decimal and NaN. Use df.to_json(orient="records", date_format="iso") for plain row lists, a default= converter with allow_nan=False for hand-built payloads, or an explicit cleaning step when the receiver specifies formats — and verify the result with a strict parser, because Python's own reader accepts NaN that other systems reject.

Frequently asked questions

How do I fix "Object of type Timestamp is not JSON serializable"? Use df.to_json(orient="records", date_format="iso"), or pass a default function to json.dumps that returns obj.isoformat() for Timestamp, datetime, date and time objects.

Why does json.dumps say "Object of type int64 is not JSON serializable"? The value is a NumPy integer, typically taken from a DataFrame with .loc or .iloc. Convert it with int(value) or value.item(), or handle np.integer in a default function.

Why does my exported JSON contain NaN, and why is it a problem? json.dumps writes float NaN as the bare word NaN, which is not valid JSON, so JavaScript's JSON.parse and many APIs reject the document. Replace NaN with None before serialising, and pass allow_nan=False so any leftover NaN raises an error.

What date format does DataFrame.to_json use? By default it writes milliseconds since 1970, a default pandas 3 deprecates. Pass date_format="iso" to get ISO 8601 strings such as 2026-09-01T10:30:00.000.

More on errors & fixes

More on errors & fixes →