How to Merge Multiple JSON Files into One Excel Workbook

You rarely end up with one JSON file. You end up with page_1.json through page_47.json, or a folder of daily exports, or a file per region that three different teams generated. The merge itself is two lines. Everything interesting is in the decisions around it.

First: one sheet or many?

This determines everything else, so decide before you write any code.

One sheet when the files are the same kind of thing split up — pages of one result set, days of one log. You want to filter, sort and pivot across all of it, and that only works if it is one table.

One sheet per file when the files are genuinely different things that happen to live in the same folder — separate reports, unrelated entities, per-client extracts that nobody will ever compare directly.

When unsure, choose one sheet and add a source column. You can always pivot by source; you cannot easily un-split thirty sheets.

Method 1: jq, for a quick combine

If each file contains a top-level array, this concatenates them all:

jq -s 'add' page_*.json > merged.json

-s (slurp) reads all inputs into one array of arrays, and add concatenates them into a single flat array. If instead each file is a single object and you want an array of objects, drop the add:

jq -s '.' record_*.json > merged.json

Where the records sit under a key, reach in first:

jq -s 'map(.results) | add' page_*.json > merged.json

Then drop merged.json into the converter and you have your workbook. This is the fastest route for a one-off, and it is where I start when the files are already well behaved.

Method 2: Python, one sheet with provenance

This is the version worth having in a scripts folder. It handles ragged schemas, records where each row came from, and reports what it did:

import glob
import json
import os
import pandas as pd

frames = []
for path in sorted(glob.glob("data/*.json")):
    with open(path, encoding="utf-8") as f:
        payload = json.load(f)

    # Unwrap the common { "results": [...] } envelope.
    records = payload["results"] if isinstance(payload, dict) else payload

    frame = pd.json_normalize(records)
    frame.insert(0, "source_file", os.path.basename(path))
    frames.append(frame)
    print(f"{os.path.basename(path)}: {len(frame)} rows, {len(frame.columns) - 1} columns")

# sort=False keeps first-seen column order; join="outer" is the default and
# unions the columns, filling gaps with NaN rather than dropping them.
merged = pd.concat(frames, ignore_index=True, sort=False)

merged.to_excel("merged.xlsx", index=False)
print(f"\ntotal: {len(merged)} rows, {len(merged.columns)} columns")

Three things in there are doing real work.

source_file is inserted as the first column. When a row looks wrong three weeks later, this is what tells you which export produced it. It costs one line and it is the single most useful thing you can add to a merged dataset.

pd.concat with the default outer join unions the columns across files. A field that only exists in some files still gets a column, with blanks where it was absent — which is what you want, and is not what naive merging code does.

The per-file print is a cheap sanity check. If one file reports 40 columns where every other reports 12, you have found a schema change before it silently widens your sheet.

Method 3: One sheet per file

import glob
import json
import os
import pandas as pd

with pd.ExcelWriter("workbook.xlsx", engine="openpyxl") as writer:
    for path in sorted(glob.glob("data/*.json")):
        with open(path, encoding="utf-8") as f:
            payload = json.load(f)

        records = payload["results"] if isinstance(payload, dict) else payload
        frame = pd.json_normalize(records)

        # Sheet names: 31 chars max, and : \ / ? * [ ] are forbidden.
        name = os.path.splitext(os.path.basename(path))[0]
        for bad in ':\\/?*[]':
            name = name.replace(bad, "-")

        frame.to_excel(writer, sheet_name=name[:31], index=False)

The sanitising is not optional. Excel refuses to open the entire workbook if one sheet name is too long or contains a reserved character, and it gives you a generic "unreadable content" message rather than naming the sheet.

Duplicate names after truncation will also silently overwrite. If your filenames share a long prefix — export_2026_09_01_region_north.json and friends — truncating to 31 characters can collide. Number them if in doubt: name[:28] + f"_{i}".

When the schemas do not match

Files generated weeks apart drift. A field gets renamed, a value that was a string becomes an object, someone adds a column. Find out before you merge:

import glob
import json
from collections import Counter

fields = Counter()
per_file = {}

for path in sorted(glob.glob("data/*.json")):
    with open(path, encoding="utf-8") as f:
        payload = json.load(f)
    records = payload["results"] if isinstance(payload, dict) else payload

    keys = set()
    for record in records:
        keys.update(record.keys())
    per_file[path] = keys
    fields.update(keys)

total = len(per_file)
for field, count in fields.most_common():
    if count < total:
        missing = [p for p, k in per_file.items() if field not in k]
        print(f"{field}: absent from {total - count} file(s), e.g. {missing[0]}")

Anything printed is a field that does not exist everywhere. Sometimes that is fine — an optional field. Sometimes it is customer_id renamed to customerId halfway through, which will give you two columns that should be one. Normalise those before concatenating:

frame = frame.rename(columns={"customerId": "customer_id"})

Deduplication

Overlapping pagination and re-run exports both produce duplicates. Drop them on the natural key, not the whole row — two records for the same entity may differ in a timestamp field while still being the same record:

# Keep the last occurrence, assuming files are in chronological order,
# so later exports win over earlier ones.
merged = merged.drop_duplicates(subset=["id"], keep="last")

Count first, so you know whether the duplicates were expected:

dupes = merged["id"].duplicated().sum()
print(f"{dupes} duplicate ids out of {len(merged)} rows")

If that number is a surprise, stop and find out why before dropping anything. A large duplicate count usually means the pagination loop that produced the files was re-requesting the same offset.

Many files, or very large ones

pd.concat on a list of frames holds every frame in memory plus the result. For a few dozen files that is fine. For hundreds, or for files large enough to be uncomfortable on their own, write rows out as you go instead:

import glob
import json
from openpyxl import Workbook

book = Workbook(write_only=True)
sheet = book.create_sheet("Merged")

headers = None
for path in sorted(glob.glob("data/*.json")):
    with open(path, encoding="utf-8") as f:
        payload = json.load(f)
    records = payload["results"] if isinstance(payload, dict) else payload

    for record in records:
        if headers is None:
            headers = ["source_file"] + list(record.keys())
            sheet.append(headers)
        sheet.append([path] + [record.get(h) for h in headers[1:]])

book.save("merged.xlsx")

write_only=True is what keeps openpyxl from building the whole worksheet in memory. The trade-off is that headers come from the first record seen, so this version does not handle schema drift — run the field audit above first. For the general large-file playbook, see working with large JSON files.

Merging JSON Lines files

If each line of your files is its own JSON object, concatenation is literally file concatenation — no parsing required:

cat events_*.jsonl > merged.jsonl

That is one of the real advantages of the format. Then convert with pd.read_json("merged.jsonl", lines=True), or see converting JSON Lines to Excel for the full treatment.

Do check that each file ends with a newline. Without it, the last record of one file and the first of the next end up on the same line, and that line parses as neither.

A checklist before you distribute the workbook

1. Does the row count match the sum of the per-file counts?
2. Is there a source_file column?
3. Did the field audit report any column present in only some files?
4. Are there unexpected duplicate keys?
5. Are you under 1,048,576 rows?
6. Do identifier and date columns still look right? See why Excel changes your data.