JSON to Excel: Four Approaches and Where Each One Loses Fidelity

Bottom line: a flat array of objects or an object wrapping an array converts fastest through the online tool; use Power Query when the data refreshes weekly, pandas when there are many fields to clean, and a Node script only when the conversion must join a pipeline — but decide what "one row" means before touching nested data or inconsistent keys.

The conversion itself is not hard; what is hard is discovering afterwards that numbers became text and that several nesting levels collapsed into one. Read the data structure before choosing a method and you save yourself a rework.

1. First, work out which kind of JSON you have

Open the file and look at the outermost level:

The first two convert directly with close to a 100% success rate; for the last three, decide what "one row" means first, otherwise the resulting sheet is unusable.

2. Approach 1: online tool (1 minute)

Open JSON to table, paste the data → pick the delimiter and header → export CSV → open in Excel. Suitable for a few dozen to a few thousand rows of well-structured data.

Three things you must watch out for:

• You get CSV, not xlsx. When Excel opens a CSV it guesses the encoding from the system locale, so Chinese text may become mojibake. Using "Data → From Text/CSV" and explicitly selecting UTF-8 is more reliable.
• Identifiers starting with 0 lose their zeros. "007" is text in the CSV, but Excel converts it to the number 7 automatically. Set that column to text during import, or add a prefix to it in JSON.
• Long numbers become scientific notation. National IDs and order numbers longer than 15 digits lose precision; such fields should already be strings in JSON, and must stay in text format after import.

3. Approach 2: Excel's built-in Power Query (reusable)

When your data is refreshed every week, use the Power Query built into Excel: configure it once, then just click "Refresh":

1. "Data" → "Get Data" → "From File" → "From JSON";
2. After selecting the file you land in the Power Query editor, where you see List / Record structures;
3. Click the expand icon on the right side of a column header to expand nested fields level by level;
4. Once the column names and types look right, "Close & Load".

The upside is that nesting can be expanded into multiple columns layer by layer, and types can be assigned manually; the price is that the first setup requires understanding the difference between List and Record — List is an array, Record is an object, and they expand in different directions.

4. Approach 3: Python + pandas (many fields, needs cleaning)

复制import pandas as pd

# Object wrapping an array: first reach the list level
rows = pd.read_json('data.json')['data']

# Expand nested fields into multiple columns
frame = pd.json_normalize(rows, sep='_')

frame.to_excel('out.xlsx', index=False)

The key parameter of json_normalize is sep, which determines the column-naming style after expanding nested fields; user_name is easier to use in Excel than user.name. Arrays of arrays (such as tags) need an extra explode step:

复制frame = frame.explode('tags')

This step splits one row into several, the only way to preserve a one-to-many relationship — at the cost of duplicating the remaining fields.

5. Approach 4: a Node script (when it must join a pipeline)

复制import { readFileSync, writeFileSync } from 'node:fs';

const rows = JSON.parse(readFileSync('data.json', 'utf8'));
const headers = [...new Set(rows.flatMap((row) => Object.keys(row)))];
const escapeCell = (value) => `"${String(value ?? '').replaceAll('"', '""')}"`;
const lines = [
  headers.join(','),
  ...rows.map((row) => headers.map((key) => escapeCell(row[key])).join(',')),
];

writeFileSync('out.csv', '\uFEFF' + lines.join('\n'), 'utf8');

Two details you should not skip: write \uFEFF (a BOM) at the start so Excel recognizes UTF-8, and escape double quotes inside cells as "", otherwise the columns shift.

6. Three things conversion always loses

Also, null and "field absent" look the same in a sheet (both are empty cells) but mean different things in the business domain. When you need to tell them apart, explicitly convert null to the string "null" in JSON, or add a _has_field marker column.

7. Verifying afterwards: don't just look at the first row

After the conversion, spot-check at least three things:

1. Record count: the number of CSV lines should equal the array length + 1 (the header); a missing line usually means the trailing newline was swallowed;
2. The column with the highest null rate: lots of empty cells indicate inconsistent key names — go back and check whether the original JSON uses two spellings (userId and user_id);
3. The longest row: nested arrays usually land there; confirm nothing was truncated.

When you need the reverse operation (Excel/CSV back to JSON), use CSV to JSON, and remember that the header is treated as the key names — changing the header is changing the field names.

FAQ

How do I choose between the online tool, Power Query, and pandas?

Use the online tool for a few dozen to a few thousand rows of well-structured data you convert once. Use Power Query when the data refreshes every week, since it is configured once and refreshed with a single click. Use pandas with json_normalize when there are many fields to clean, and a Node script when the conversion must join a pipeline.

Why does Chinese text turn into mojibake in a CSV?

The export is CSV, not xlsx, and Excel guesses the encoding from the system locale, so UTF-8 without a BOM shows mojibake. Write a \uFEFF BOM at the start of the file, or open it through "Data → From Text/CSV" and select UTF-8 explicitly.

Should nested objects be flattened or split into extra rows?

Flatten them level by level rather than pasting them as strings: Power Query expands nested fields into columns, and json_normalize with sep does the same in pandas. Arrays of arrays need an extra explode step that splits one row into several. Passing a deep object to String(obj) only yields [object Object].