📄 Format & Conversion · 9 min read

Convert Excel to JSON: Sheets, Dates and Nested Rows (2026)

A workbook is not a text file, which is why JSON tools refuse it. The workable routes are: save one clean sheet as CSV and convert that, read the workbook with pandas and write JSON from the frame, or stay inside Microsoft 365 with Get Data and Office Scripts. Three details cause most of the damage on the way: only the active sheet survives a CSV save, dates arrive as serial numbers, and identifiers longer than 15 digits come back changed. The rest of this page is the four routes in the order worth trying, then the failure list.

The question arriving in developer forums is usually a frustration about the file type rather than the conversion itself. One answer on r/node put the situation plainly: “My suggestion is exporting that Excel spreadsheet to csv which it will be easier to convert to json. Closed formats like Excel are difficult to” handle in a script. That advice is half right. The CSV hop works, and it quietly drops everything the workbook knows beyond the grid: the other sheets, the number formats, and the fact that a column was ever meant to hold a date.

What is actually inside the file

The format is OOXML, published as ECMA-376 and adopted as ISO/IEC 29500. An .xlsx file is a ZIP archive. Unzip one and you find workbook.xml listing the sheets, one worksheet part per sheet, a sharedStrings.xml holding repeated text once, and a relationships file tying it together. Cell values are stored by reference, with the type in an attribute rather than in the text.

That is the whole reason a converter asks for CSV. The older .xls format is worse in this respect, since it is a binary compound file rather than a ZIP of XML, which is why libraries that handle .xlsx cleanly often need a different dependency for .xls.

Excel does impose limits, and they matter once JSON is the output. A worksheet holds up to 1,048,576 rows and 16,384 columns, a cell holds at most 32,767 characters, and formulas are capped by nesting depth rather than length. JSON has no per-cell limit at all. When somebody says a conversion “lost data”, the data usually left the workbook long before the converter saw it.

Method 1: Save as CSV, then convert

File > Save As > CSV UTF-8 (Comma delimited), then run the CSV through any JSON converter. Two minutes of work, and it is the right answer for a single flat table.

It fails in predictable places. The save writes the active sheet only, with no error and no warning about the other tabs. Formulas are replaced by their current values, so the arithmetic is gone. Values are re-typed when the CSV is read back, so a ZIP code column becomes integers, and any identifier with leading zeros loses them. If the JSON output will be read by a program rather than a person, that last point is a data loss bug rather than a cosmetic one. Keeping leading zeros is a fight you can win, but it is easier to avoid the CSV hop entirely.

Method 2: pandas, for anything with more than one sheet

read_excel with sheet_name=None returns a dictionary of DataFrames, one per sheet, keyed by sheet name. From there, one JSON file per sheet, or one object keyed by sheet:

import pandas as pd

sheets = pd.read_excel("orders.xlsx", sheet_name=None)

for name, frame in sheets.items():
    frame.to_json(
        f"{name}.json",
        orient="records",
        force_ascii=False,
        indent=2,
    )

Three settings earn their place in that snippet. orient="records" produces an array of objects, which is what APIs and front-end code expect, instead of the column-oriented default. force_ascii=False keeps accented characters as characters rather than turning them into \u escapes. indent=2 makes the output diffable, which matters the moment the file is committed to a repository.

For large workbooks, read with read_only=True in openpyxl to avoid loading the whole sheet into memory, and pass dtype=str to read_excel for the identifier columns. That single argument prevents the 19-digit problem described below, at the cost of typing every number as text and converting the arithmetic columns yourself.

If a column is empty in the output, the cause is usually formulas. openpyxl documents data_only=True as returning the value stored the last time Excel read the sheet, not a recalculation. A file generated by a script and never opened in Excel has no cached values, so every formula cell reads as None. Either open and save the file in Excel once, or compute that column in pandas instead.

Method 3: Inside Excel, with Microsoft 365

Power Query reads workbooks well through Data > Get Data > From File > From Workbook, and it reads JSON through the same menu. Writing JSON is where it stops: a query loads into a worksheet or the data model, and neither of those is a .json file.

Office Scripts closes that gap in Excel on the web. Microsoft ships a sample called Output Excel data as JSON that takes a table, converts it with JSON.stringify and writes the result out, and a Power Automate flow can run it on a schedule so that the file is regenerated without anyone opening Excel. The requirement is a Microsoft 365 licence that includes Office Scripts.

The manual version still shows up in answers: build the JSON in a helper column with concatenation, then copy the column into a text editor. It works for twenty rows — and breaks on the first value containing a double quote or a backslash, because JSON, unlike CSV, has no quoting convention that makes an unescaped quote safe.

Method 4: An online converter

Fine for a file you would happily post. xlsx parsing runs in the browser in the tools worth using, including the ones built on SheetJS, so it is worth checking whether the upload happens at all before handing over a payroll file. If the sheet contains customer names, anything covered by a data processing agreement, or a column someone would object to seeing indexed, use one of the three routes above.

Which route to take

The five details that break the output

The date problem deserves one more sentence, because it produces silent wrong answers rather than empty ones. Two epoch systems are in circulation: the default 1900 system, which contains a fictional 29 February 1900 so that it can mimic an older spreadsheet program, and the 1904 system still found in workbooks created on older Mac versions. Their outputs differ by 1,462 days, so a converter that guesses the wrong one is wrong by four years, not by four seconds.

Deciding what a record is

“Convert to JSON” hides a design choice. The four shapes that come up:

// 1. Array of objects - the usual answer
[{"id": "A-1001", "total": 249.5}]

// 2. Keyed by sheet, for multi-sheet workbooks
{"January": [...], "February": [...]}

// 3. Keyed by primary key, for lookups
{"A-1001": {"total": 249.5}}

// 4. JSON Lines - one object per line, for streaming
{"id": "A-1001", "total": 249.5}
{"id": "A-1002", "total": 88.0}

Shape 4 is the one to remember for big exports. A JSON Lines file can be written and read one record at a time, so it never needs to fit in memory, and appending a day of data is a file append rather than a reparse. pandas writes it with lines=True. The other three are all perfectly good for anything a spreadsheet can hold in one sitting.

When the workbook is too big

Excel stops at 1,048,576 rows, and a JSON array of that size is a few hundred megabytes of text. Convert to JSON Lines, filter rows in the same script that reads the workbook, or skip the conversion and ask the question you actually need answered. Loading the file into the CSV analyzer gives you totals, outliers and trends in plain language without a file format in the middle, and the Excel data analysis tool does the same for a workbook. If you are still deciding between the two containers, the reverse direction covers the nested JSON that refuses to become a flat sheet, and JSON to CSV is the flatter, cheaper neighbour of this job.