Why Converting Large JSON Files to Excel Is Harder Than It Looks
Most data professionals have been there: a system export drops a massive JSON file on your desk — 1.5GB, deeply nested, and the stakeholder needs it in Excel by end of day. The instinct is to reach for a quick online converter or paste it into a script and call it done. That instinct is usually where the trouble starts.
JSON and Excel represent data in fundamentally different ways. JSON is hierarchical and flexible — a single record can contain nested arrays, objects within objects, and inconsistent field presence across rows. Excel is flat, grid-based, and has hard structural limits. Bridging that gap at scale is not a trivial operation, and doing it carelessly produces truncated values, lost nested data, broken numeric types, and files that quietly omit entire record segments without any warning.
When the output feeds a dashboard, a finance model, or a client-facing report, silent data loss is a serious problem. The gap between a rushed conversion and a properly structured one is the difference between a file someone can trust and one that quietly misleads.
What the Work Actually Requires
A clean JSON-to-Excel conversion at the 1GB-plus scale involves more than running a command. Four things separate a reliable output from a broken one.
The first is schema analysis. Before any transformation runs, the source JSON needs to be audited for structure: how deeply nested are the objects, which arrays repeat, which fields are inconsistently populated, and what data types are in use. Skipping this step means the flattening logic makes silent assumptions that corrupt the output.
The second is a deliberate flattening strategy. Nested objects and arrays cannot map directly to a flat sheet without a decision about how to handle them — whether to normalize into child tables, use dot-notation column names, or concatenate values. Each choice has trade-offs.
The third is chunked processing. A 1.5GB JSON file cannot be loaded into memory all at once by most consumer tools. Processing it in chunks — typically 10,000 to 50,000 records per batch — prevents out-of-memory failures and keeps the pipeline stable.
The fourth is output validation. Once the Excel file is written, the row count, column completeness, and data type integrity need to be verified against the source before the file is handed off.
How to Approach the Conversion Correctly
Audit the Schema First
The right approach begins with parsing a sample of the JSON — typically the first 500 to 1,000 records — to map the full schema before writing a single row to Excel. In Python, the json module combined with pandas makes this practical. A script that reads the first 1,000 objects and runs pd.json_normalize() on them surfaces every field path, including nested ones, as a column header. This audit tells you exactly how many columns the final sheet will have and which fields appear sporadically.
For a 1.5GB file, the file itself is almost certainly structured as either a JSON array at the root level or as newline-delimited JSON (NDJSON), where each line is one record. The distinction matters because the parsing approach differs. A root-level array requires streaming with ijson, a Python library designed for incremental JSON parsing. NDJSON can be read line by line with a standard file handle.
Flatten Nested Structures Intentionally
The pd.json_normalize() function handles one level of nesting well using its record_path and meta parameters, but deeper hierarchies need explicit decisions. A record with a nested address object, for example, produces columns like address.city, address.country, and address.postcode using dot-notation flattening — which is clean and readable. A record where one field contains an array of five transaction objects is a different problem entirely: that array either needs to be exploded into five child rows (a one-to-many relationship), concatenated into a single delimited string in one cell, or split into a separate sheet linked by a key.
For most analytical use cases, exploding arrays into child rows and writing them to a second sheet — say, Sheet1: Customers and Sheet2: Transactions — is cleaner than collapsing them into one. The pd.DataFrame.explode() method handles this in pandas, and the openpyxl library writes both sheets into a single .xlsx workbook.
Process in Chunks to Avoid Memory Failures
At 1.5GB, full in-memory loading will exhaust RAM on most machines. The reliable pattern is a chunked loop: read 25,000 records at a time, normalize them into a DataFrame, and append each batch to the output file using openpyxl's append mode or by writing to a temporary CSV that gets converted at the end.
A practical example looks like this: ijson.items(file_handle, 'item') returns an iterator over each root-level object without loading the full file. Collecting 25,000 items into a list, normalizing that list with pd.json_normalize(), and appending to an output CSV repeats until the iterator is exhausted. Final CSV-to-XLSX conversion via openpyxl keeps the Excel file well within the 1,048,576-row limit of a single sheet. If the dataset exceeds that, splitting by year, region, or record type into multiple sheets is the correct structural response.
Validate the Output Before Signing Off
A row count check is the minimum bar. The total rows in the Excel output, excluding the header, should match the total object count in the source JSON — confirmed with a separate counter during the parsing pass. Beyond row count, spot-checking ten randomly selected records side by side against the source JSON catches field truncation, numeric precision loss (Excel stores numbers as 64-bit floats, which can round large integers incorrectly — fields like IDs exceeding 15 digits need to be stored as text explicitly), and date fields that Excel auto-formats incorrectly on import.
Setting column data types explicitly during the openpyxl write pass — particularly forcing ID columns to text with a leading apostrophe or by setting cell.number_format = '@' — prevents Excel from silently mangling values.
What Goes Wrong When This Is Rushed
The most common failure is skipping the schema audit and running a generic converter directly on the full file. Generic tools — online converters, simple scripts without normalization logic — flatten only one level deep and silently drop everything nested below it. A dataset with nested arrays may arrive in Excel with those fields entirely absent, and there is no error message to catch it.
A second frequent problem is ignoring Excel's 32,767-character cell limit. JSON string fields that contain long text — descriptions, log entries, encoded values — get silently truncated at that threshold when written via some libraries. The openpyxl library raises a warning, but only if warnings are not suppressed. Truncated cells in a key field corrupt any downstream lookup or analysis that depends on them.
Numeric precision loss with large integers is a third overlooked issue. JSON IDs and timestamps stored as integers longer than 15 digits lose precision when Excel interprets them as floating-point numbers. An order ID of 1234567890123456 becomes 1234567890123460 — wrong, and difficult to catch visually. Writing those fields as strings from the start is the only safe path.
A fourth pitfall is treating the conversion as a one-time manual task when the source system generates this export regularly. Without a repeatable, documented script, the next conversion gets redone from scratch — with slightly different column names, different sheet structures, and drift that breaks downstream models.
Finally, rushing the output validation is where silent errors become expensive errors. Comparing input record count to output row count takes under a minute and catches the most damaging class of failure.
What to Remember About This Work
Converting a large JSON file to Excel cleanly is fundamentally a data visualization toolkit problem dressed in a familiar format. The schema audit, the flattening strategy, the chunked processing, and the output validation are not optional polish — they are the work. Every shortcut in those four areas creates a file that looks complete but is not.
If you would rather have this handled by a team that does this kind of structured data work every day, check out how others have tackled similar challenges: learn about merging multiple Excel files and see how teams have successfully handled large-scale Excel data entry projects.