📄 Format & Conversion · 8 min read

Convert JSON to Excel: Nested Data and a Folder of Files (2026)

Excel cannot open a .json file directly, because JSON is a tree and a spreadsheet is a grid. The fastest route is Data > Get Data > From File > From JSON, then expanding the columns Power Query marks as Record or List. If you want a flat sheet and nothing else, convert to CSV first. If a whole folder of files has to become one spreadsheet, do it in code, because the manual route breaks the moment one file has an extra field. All four routes are below, in the order most people should try them.

The request that brings people here usually includes a folder, not a file. A first-time JSON user asking for help on Reddit put it this way: “I am working with JSON files for the first time and need to extract specific data from a batch of files into an excel spreadsheet.” The awkward part is not the conversion. It is deciding what a row is once you have several files and a nested structure.

Why JSON does not behave like CSV

RFC 8259, the JSON specification, defines six kinds of value: object, array, string, number, boolean and null. There is no table, no column and no row. An object is an unordered set of name-value pairs, and values may be objects or arrays themselves, nested as deep as whoever generated the file wanted. CSV is the opposite: RFC 4180 describes a rectangle of fields separated by commas, with a fixed number of columns per line.

That mismatch is the entire problem. Converting JSON to Excel is really a decision about which part of the tree becomes the rows. A file with 200 order objects and five line items each is either 200 rows or 1,000 rows, and both answers are correct depending on what you plan to do with the sheet.

Excel does not leave you without tools, though. Since Excel 2016 there is a JSON connector behind Get Data, and it handles nesting properly. It just does not announce itself.

Which shape is your JSON?

Read the first two lines of the file before choosing a method. The outer structure decides almost everything.

Method 1: Power Query, built into Excel

1. Open a blank workbook and go to Data > Get Data > From File > From JSON.
2. Pick the file. Power Query opens a preview with one column, usually named after the wrapper key.
3. Click the expand icon in the column header and tick the fields you want. Nested objects come back as Record and nested arrays as List. Expand them the same way, one level at a time.
4. For a folder, use Data > Get Data > From Folder instead, then combine the files and expand. Keep a column with the file name, since that is often the only thing separating one batch from another.
5. Close & Loadwrites the result into a sheet. The transformation is saved, so next month's file only needs a refresh.

Two things go wrong here often enough to be worth naming. Expanding a List column creates one row per element, so row counts jump, and there is no warning when it happens. And expanding nested records produces duplicate column names such as name twice; rename them before loading or reference them by position afterwards, which is a habit that ages badly.

Method 2: Convert to CSV first, then open it

If the JSON is flat, this is the least work. A JSON to CSV converter gives you a file that Excel, Sheets and every database already understand, with quoting handled by RFC 4180 rules. The reverse direction, CSV to JSON, is the one to use when you are heading back to an API.

The limitation is the same one that makes CSV popular: it is flat. Nested arrays have to be flattened, repeated across rows, or dropped before export. Choose deliberately, because dropping a field during conversion and finding out three weeks later is a common way to lose data quietly.

Method 3: Python, for batches and repeat jobs

When the folder has forty files in it, read the JSON in code and write one spreadsheet. Two functions do most of the work: json_normalize flattens nested records into columns, and concat stacks the frames into a single table.

import json, glob
import pandas as pd

frames = []
for path in glob.glob("exports/*.json"):
    with open(path, encoding="utf-8") as f:
        data = json.load(f)
    df = pd.json_normalize(data, sep="_")   # nested keys become addr_city, addr_zip
    df["source_file"] = path
    frames.append(df)

pd.concat(frames, ignore_index=True).to_excel("combined.xlsx", index=False)

For JSON Lines files, where each line is a complete object, skip the loop over lines and read the file in one call instead. It is faster and it handles large files without holding a parsed copy of the whole document in memory.

Keep an eye on the Excel row limit of 1,048,576 rows before the export, not after. It is easier to split the output into one file per month than to explain to someone why the last quarter of their data is missing. Writing to CSV instead of XLSX removes the ceiling entirely.

Method 4: Google Sheets, with one caveat

Sheets has no JSON importer. IMPORTDATA and IMPORTHTML are not options here, and pasting raw JSON into a cell gives you text, not columns. The practical route is to convert the file to CSV first and import that, or to write a small Apps Script that fetches the URL and writes rows. Apps Script is genuinely useful if the JSON comes from an API you check weekly, because the sheet then refreshes on a trigger.

Sheets does raise the size ceiling: Google documents a limit of 10 million cells and 18,278 columns, both well above Excel's grid. What it does not do is guess your structure.

When the sheet comes out wrong

The last row of that table causes the most damage, because nothing looks broken. A 19-digit account number read as a double comes back with the final digits changed. If the field is an identifier rather than a quantity, it is text, whatever the JSON thinks.

A note on encoding

JSON for interchange is UTF-8 by definition, so a file that renders correctly in an editor and turns into mojibake in Excel has usually been through a second encoding on the way in. Importing through Power Query rather than opening the file lets you confirm the encoding before anything is written to the sheet. If it has already gone wrong in a CSV, the fix is in garbled CSV in Excel.

Once the data is in a sheet, the next question is usually about the data itself rather than the file format. Loading it into the Excel data analysis tool lets you ask for totals, outliers and trends in plain language, without a second conversion step, and the CSV analyzer handles the same job when you exported to CSV instead. If you went the other way and now have a flat file you are not sure about, CSV vs Excel covers when each one is the right container.