Working with Large JSON Files: What Breaks, and How to Fix It

"Large" is not a fixed number. What matters is the relationship between the file on disk and the memory needed to hold it as parsed objects — and those two figures are further apart than most people expect.

Why a 100 MB file needs far more than 100 MB of RAM

When you call JSON.parse() or json.load(), the text is turned into in-memory objects. Each object carries overhead the text file does not: type tags, hash tables for keys, pointers, alignment padding.

A rough rule from experience: expect parsed JSON to occupy 6 to 10 times the file size in memory. Records with many short keys sit at the worse end, because every key string and every object header is paid for on every single row.

This is why browser-based converters, including ours, publish a file-size limit. It is not an arbitrary restriction — a browser tab typically gets between 1 and 4 GB of address space, and it also has to hold the generated spreadsheet at the same time. Our converter is capped at 10 MB because that is the size at which conversion stays comfortably instant on ordinary hardware, including phones.

The Excel ceiling, which arrives sooner than you think

Before optimising anything, check whether the destination can hold the data at all. An .xlsx worksheet is limited to:

• 1,048,576 rows
• 16,384 columns (column XFD)
• 32,767 characters in a single cell

A 500 MB JSON file of typical API records is often several million rows. No amount of clever conversion puts that on one sheet. If you are past a million rows, the honest answer is that Excel is the wrong destination — you want a database, Parquet, or CSV feeding a BI tool.

Step one: find out what is actually in the file

Do not open it in a text editor. Editors load the whole file and build syntax-highlighting structures on top, which is how a 200 MB file freezes a machine with 16 GB of RAM.

Read the first few hundred bytes instead:

# macOS / Linux
head -c 500 big.json

# Windows PowerShell
Get-Content big.json -TotalCount 1 | ForEach-Object { $_.Substring(0, 500) }

That tells you the shape — whether the root is an array or an object, and what the fields are. Then count the records without parsing:

# if the file is JSON Lines, one object per line
wc -l big.json

Streaming: parse without loading everything

A streaming parser reads the file in chunks and emits each record as it is encountered, so memory use stays flat regardless of file size.

Python with ijson

pip install ijson openpyxl

import ijson
from openpyxl import Workbook

wb = Workbook(write_only=True)
ws = wb.create_sheet("Data")

headers = None
with open("big.json", "rb") as f:
    # "item" yields each element of the root array in turn
    for record in ijson.items(f, "item"):
        if headers is None:
            headers = list(record.keys())
            ws.append(headers)
        ws.append([record.get(h) for h in headers])

wb.save("output.xlsx")

Two details make this work at scale. ijson.items(f, "item") never holds more than one record at a time. And Workbook(write_only=True) tells openpyxl to stream rows to disk instead of building the whole sheet in memory — without it, you have simply moved the memory problem from parsing to writing.

Node.js with stream-json

npm install stream-json

const fs = require('fs');
const { parser } = require('stream-json');
const { streamArray } = require('stream-json/streamers/StreamArray');

let count = 0;
fs.createReadStream('big.json')
  .pipe(parser())
  .pipe(streamArray())
  .on('data', ({ value }) => {
    count++;
    // process one record at a time
  })
  .on('end', () => console.log(`processed ${count} records`));

Splitting a large file into manageable pieces

If you would rather keep using familiar tools, split the file first and convert each piece:

import ijson, json

CHUNK = 50_000
chunk, part = [], 1

with open("big.json", "rb") as f:
    for record in ijson.items(f, "item"):
        chunk.append(record)
        if len(chunk) == CHUNK:
            with open(f"part_{part}.json", "w", encoding="utf-8") as out:
                json.dump(chunk, out)
            chunk, part = [], part + 1

if chunk:
    with open(f"part_{part}.json", "w", encoding="utf-8") as out:
        json.dump(chunk, out)

Each part_N.json is small enough for any converter, including browser-based ones. This is often the pragmatic answer: one streaming script you run once, then normal tools.

Filtering before converting

Usually the fastest fix is to convert less. jq is built for this and streams by default:

# keep only the fields you need
jq '[.[] | {id, name, email}]' big.json > small.json

# keep only rows matching a condition
jq '[.[] | select(.status == "active")]' big.json > active.json

# pull out a nested array as the new root
jq '.results' wrapped.json > results.json

Dropping from forty fields to five typically cuts the file by 80 percent or more, and the result often falls under a converter's limit without any further work.

Convert to CSV instead

CSV has no row limit, streams naturally, and every analysis tool reads it. If the destination is really a database, a BI tool, or pandas, going through .xlsx adds cost for no benefit.

import ijson, csv

with open("big.json", "rb") as fin, \
     open("out.csv", "w", newline="", encoding="utf-8") as fout:
    writer = None
    for record in ijson.items(fin, "item"):
        if writer is None:
            writer = csv.DictWriter(fout, fieldnames=list(record.keys()))
            writer.writeheader()
        writer.writerow(record)

The trade-offs between the formats are covered in JSON vs CSV vs Excel.

Choosing an approach

A note on validating large files

Online validators load the whole document, so they fail on exactly the files you most want to check. Validate from the command line instead:

# exits non-zero and prints the location of the first error
jq empty big.json

# Python equivalent
python -c "import json,sys; json.load(open(sys.argv[1], encoding='utf-8'))" big.json

Both still parse the whole file, but they do it without a UI and without syntax highlighting, which is where editors spend most of their memory. For the errors these will report, see common JSON errors and how to fix them.