JSON to Excel Spreadsheet

From nested JSON to a flat grid

Paste an array of records and each one flattens to leaf paths: {"customer":{"name":"Ada"}} produces a column named customer.name, and a tags array becomes tags[0], tags[1] and so on. The two notations are deliberately distinguishable — with dots alone, a.0 cannot say whether a was an array or an object with a key called 0 — which is what makes the round trip through CSV to JSON reversible.

Two further decisions matter in a spreadsheet. Arrays of scalars can be joined into one cell (priority, gift) instead of exploded into columns — easier to read, but lossy, so it applies only to scalars; an array of objects is always exploded, because joining it would destroy it. And null becomes an empty cell rather than the text null, which a spreadsheet would treat as a string. Quoting follows RFC 4180: fields are quoted only when they contain the delimiter, a quote or a newline, and quotes inside fields are doubled.

The UTF-8 BOM quirk in older Excel

A CSV downloaded from this tool is UTF-8 without a byte order mark — the modern default, and correct for every program that reads encodings properly. Older Excel (2016 and earlier in particular) does not: with no BOM present it assumes ANSI, a legacy Windows code page, and every character above 127 garbles — é becomes é, Curaçao becomes Curaçao, names and addresses turn to soup. The file is fine; the assumption is wrong.

The fix is the import path: Data → From Text/CSV (Data → From Text in older versions), choose the file, and set the encoding to 65001: Unicode (UTF-8) in the dialog. Modern Microsoft 365 builds usually detect UTF-8 unaided, but the dialog is the reliable route on any version — and the same dialog overrides the delimiter, which solves the next quirk in the same step. Pasting the raw CSV text into a sheet lands it in one column until Text to Columns splits it, so the import path is also the faster one for anything with more than a few fields.

Which delimiter to choose

Comma unless you have a reason — but reasons are common, because the delimiter and the locale are entangled. In much of Europe and South America the comma is the decimal separator, and Excel there lists the semicolon as its list separator: a comma-delimited file opens as one giant column, while a semicolon file opens cleanly. The decision table:

Excel's import heuristics: dates, zeros and long numbers

Even a perfectly formed CSV can be mangled by Excel after it opens — by the importer, not the file. Values that resemble dates (2-2024, 3/4) are converted into real dates; product codes and postcodes with leading zeros lose them (007 becomes 7); long numeric IDs switch to scientific notation and their trailing digits are gone for good. On double-click these reinterpretations are one-way.

The defence is the same dialog: Data → From Text/CSV lets you set columns to Text before anything is imported, so each value arrives as written. For pasted data, format the destination column as Text first. It is worth insisting on because the damage is silent — a postcode column of numbers looks correct until someone notices the missing leading zeros, and by then the original CSV is long gone.

Practical limits, stated plainly

The converter parses JSON strictly, so a trailing comma or a smart quote is reported with a line and column rather than silently tolerated. Input is capped at 8 MB because the conversion runs on the main thread — past that it declines rather than freezing the tab. And CSV itself has hard edges no converter can remove: a cell cannot distinguish the string 22201 from the number 22201, or an empty cell from an absent field, so the round-trip page documents those two caveats instead of promising losslessness.