JSON to CSV
Flatten a list of records into a spreadsheet, with nesting handled and quotes that survive Excel.
- Rows
- —
- Columns
- —
- Quoted fields
- —
- File size
- —
A key that isn't there and a key set to null both come out as an
empty cell. CSV has one kind of nothing in it, so the tool never writes the four
characters null in place of a value that was never there — that
string would be indistinguishable from a real one on the way back in.
The column list is a union
Every key in every record gets a place in the header, in the order it first appears. A field that turns up in one record out of four hundred still gets a column. Give the tool this:
[ { "id": 1, "customer": { "name": "Ana" } },
{ "id": 2, "customer": { "name": "Bo" }, "note": "PO 4471" } ]and the header comes back as id,customer.name,note, with an empty third cell on the first row. The alternative — reading the header off the first object and ignoring the rest — is the quiet way a converter loses data, because the file looks perfectly well-formed while missing a field nobody notices until month end. The column count in the stats above is that union, so it is worth checking against what you expected before you ship the file. Four columns when you wanted three means a record further down is carrying something you had not noticed.
Quoting, and the whole of RFC 4180
Nesting is the easy half. The half that breaks files is quoting, and the rule is short: a field is wrapped in double quotes when it contains the delimiter, a double quote, a carriage return or a line feed, and an interior double quote is written twice. Nothing else needs quoting, and nothing else should be, because a file where every field is quoted is harder to read by eye and no more correct.
That gives a record that contains a comma and a line break this shape — the second line is part of the value, not a new row:
1,"Smith, Ana","Back order
Ships Friday"which is the reason both of the common shortcuts fail. Splitting the file on commas shifts every column after the comma along by one. Splitting it on newlines turns one record into two. A reader that tracks whether it is currently inside quotes handles both, and that is the only reader worth using on a file like this.
The delimiter decides which fields need quoting, not the other way round. Switch the output to semicolons and the rules follow: a value holding a comma goes in bare, a value holding a semicolon gets wrapped. Semicolon output exists because several European locales use the comma as a decimal separator, and Excel in those locales reads a comma-delimited file as a single column of text.
Where it goes quietly wrong
Two failures are worth knowing about because neither raises an error anywhere.
The first is a key containing a dot. Flattening turns a: { b: 1 } into a.b, and a record that genuinely has a top-level key called "a.b" produces the same column name. Both values are real, only one wins, and the file has no way to record that a collision happened. If your keys can contain dots, the flattened output is ambiguous and you should check the column count against the key count.
The second is a leading zero. "007" is written to the file as 007, exactly as it arrived, and the file is correct. It is Excel that re-types the cell when it opens, turns it into the number seven, and drops the zeros. Nothing in the CSV standard lets a value declare that it is an identifier rather than a quantity, so the fix belongs at import: Data → From Text/CSV, and set that column to Text before the file loads. Padding the cells afterwards will not put back digits that were never kept.
When a list wants to be two files
An array of objects inside a record — order lines, tags, addresses — is the one structure CSV handles badly, because a variable-length list in a fixed-column format has to go somewhere. The flattening spreads it sideways: items.0.sku, items.1.sku, and a record with five items moves every column after it along. That is honest, and it is usually not what the person opening the sheet wanted.
The better shape is two files joined on a key: orders with an order_id, and line items carrying the same order_id on every row. That is what a spreadsheet pivot and every database loader expect, and it keeps every row the same shape. The other escape hatch is a JSON string in one cell — valid, parseable, and impossible to sort or total. CSV is at its best when every row has the same shape, and at its worst when they do not.
If the payload does not parse at all, the line and column in the message come from the JSON engine's own error rather than from a guess, so they point at the actual problem. Sending it through the JSON Formatter first is worth the extra step when the document is large: it will show you the structure and the exact offset of the fault.
Reference
What a CSV cannot carry
| In the JSON | What the CSV does with it | In Excel | The fix |
|---|---|---|---|
| customer: { name: "Ana" } | Flattened to a dotted path. Every leaf gets a column of its own: customer.name, customer.city. | A header cell reading customer.name, which is not a name anyone types into a filter. | Nothing breaks. Rename the header cell if a person has to read the sheet, or fold the two fields back into one column. |
| items: [ { sku: "A" }, { sku: "B" } ] | items.0.sku, items.1.sku and so on. The column count follows the longest record, so it depends on the data. | Columns that only the longer records fill. The spare cells are empty, and empty is also what a missing key looks like. | Serialise the array into one cell as JSON text, or split it into a second file — line items with an order id — and join on that key. |
| "007" | Written as 007, exactly as it arrived. The file has no way to declare that the value is a string. | The number 7. The zeros are gone as soon as the cell is typed, and nothing in the file warns you. | Import through Data, From Text/CSV and set that column to Text before the file loads. Padding the cells afterwards will not put back digits that were never kept. |
| "4029001234567891" | Sixteen digits, straight through. Arriving as a quoted string, nothing in the pipeline rounds it. | A number. Excel keeps fifteen significant digits, so the last one comes back as a zero, and a narrow column falls back to scientific notation. | Set the column to Text on import, and leave the value quoted in the JSON as well: a bare number above 9007199254740992 is already rounded by the parser. |
| "Smith, Ana" | Quoted, because the value holds the delimiter. Interior quotes are doubled rather than backslash-escaped. | One cell reading Smith, Ana. The quotes are not part of the value. | Write with RFC 4180 quoting, which this tool does, and read with a parser that honours it. Splitting on commas first shifts the rest of the row along by a column. |
| "Back order\nShips Friday" | Legal inside a quoted field. The newline stays in the record, and the record ends at the closing quote. | One cell holding two lines, which needs Wrap Text before both are visible. | Read with a parser that tracks whether it is inside quotes. Splitting the file on newlines first turns one record into two. |
| "Zoë" | UTF-8 bytes, which is what JSON itself is encoded in. The file carries no statement about its own encoding. | Zoë in Windows Excel, which reads the file in the system codepage when there is no byte order mark. | Switch on Excel BOM, which puts three bytes (EF BB BF) ahead of the first cell. A parser that does not strip them shows them glued to the first header. |
| tags: ["red", "blue"] | One column per element, the same flattening an array of objects gets: tags.0, tags.1. | A list spread sideways, so a record with five tags moves every column after it along. | Join the list into one cell with a separator, or explode it into one row per tag and let the sheet filter on that instead. |
Every value above is fine in the JSON. What goes wrong happens later — in a format that stores text and nothing about it, and in a program that reads the file and guesses.
A CSV records values and nothing else. There is no type declaration, no way to mark a field as an identifier rather than a quantity, and nowhere to record that two columns arrived as one object. Every cell is text, and nothing in the file says that 007 was a product code rather than the number seven. That accounts for almost every row above: the value survives, the meaning around it does not.
The other half of the problem belongs to whatever opens the file. Excel, Numbers, a database loader and a script each guess from the same characters, and they disagree: the same file keeps the leading zeros in one program and drops them in another. Nothing in the file settles that — the disagreement is about types the file never stored.
The one fix that holds for a code that has to stay a string is to declare the type at import — Data, From Text/CSV, column type Text — rather than trust the file. Where you control the source, change the data instead: keep the account number quoted in the JSON, and put a repeating group in a second file joined on a key. CSV is at its best when every row has the same shape, and at its worst when they do not.
Questions
CSV, answered plainly
What happens to a key that only some records have?
It gets a column, and the records that don't carry it get an empty cell. The column count in the stats above is the union across every record, so you can check it against the payload before you ship the file. If you expected four columns and the tool reports six, a record further down is carrying two fields you hadn't noticed.
Why does Excel turn my order IDs into numbers?
The file is text; Excel re-types every cell as it opens it. A long ID becomes 1.23457E+18, a postcode loses its leading zero, and anything shaped like a date becomes one. Import instead of double-clicking — Data → From Text/CSV — and set the column type to Text. If accented characters arrive as mojibake, switch on Excel BOM: Excel on Windows reads the file in the local codepage unless it starts with a byte-order mark.
How deep does the flattening go?
As deep as the data. address.geo.lat is a column, and an array of objects becomes items.0.sku, items.1.sku and so on, so records with different numbers of items produce different numbers of columns. Two honest limits: a key that already contains a dot is indistinguishable from a nested path, so a literal "a.b" next to an a: { b: … } object fights over one column and the later value wins.
Can I use semicolons or tabs instead?
Yes, and the quoting rules follow the choice: with semicolons, a value containing a comma is left bare while a value containing a semicolon is quoted. Semicolon is what Excel expects in locales that use the comma as a decimal separator — a comma-delimited file opened there puts every row in a single column. Tab is for pasting straight into a spreadsheet with no import step at all.
What if the JSON isn't a list?
A single object becomes a one-row file, which is what you want for a config dump. An array of plain values — [1, 2, 3] — becomes one column called value. A top-level string, number, true or null is refused rather than guessed at. When the JSON doesn't parse at all, the line and column in the message come out of the engine's own error, so they point at the actual problem instead of at the start of the file.
Is the record set uploaded anywhere?
No. There is no endpoint on this site to upload it to: the conversion is JSON.parse and a loop running in the page you already have open, and the drop zone reads a file with the browser's own FileReader. If the records are sensitive enough that you're asking, convert one and watch your network panel stay empty.
Why is my first column header showing a strange character?
A byte order mark. EF BB BF sits at the start of the file so Excel knows the encoding, but a parser that does not strip it reads those three bytes as part of the first field, so a header of name arrives as name. If the file is going into a script, a database loader or another converter, write it without the BOM; if it is going into Excel on Windows, leave it on and expect the first cell to look odd in everything else.
Can I keep the nested objects instead of flattening them?
Yes, as a JSON string inside one cell. The cell holds a valid document and anything reading the file can parse it back, but the sheet can no longer sort, filter or total anything inside it, and a spreadsheet cell stops at 32,767 characters, so a large object will not survive the trip. Flattening is the better default; the JSON string in a single cell is the escape hatch for a field that has to stay whole.
What if my JSON is an array of arrays rather than objects?
Then there are no keys to name the columns, and the header has to be invented from the positions: 0, 1, 2 and so on, which is what this tool writes, because a position is the only thing the data carries. Rename the header row in the sheet afterwards if the columns have meanings that you know and the file does not. An inner array of objects still flattens the usual way, so a row of [[{"sku":"A"}]] comes out as 0.0.sku.