Home / Guides / How to Convert a JSON to CSV

How to Convert a JSON to CSV

Converting JSON to CSV means turning an array of objects into a table: one row per object, one column per key, with the keys written as a header row. Everything else on this page is about the two places that mapping has to make a decision — nested objects and arrays — and the one place your spreadsheet will undo your work.

[
  { "id": 1, "name": "Alice",
    "user": { "city": "NYC" } },
  { "id": 2, "name": "Bob",
    "user": { "city": "LA" } }
]

id,name,user.city
1,Alice,NYC
2,Bob,LA

That is the whole conversion. The rest of this page is the detail that decides whether the file you download is the file you actually wanted.

What the conversion actually does

CSV has no nesting, no types and no metadata other than an optional header row. JSON has all three. So a conversion is not a translation between equals — it is a flattening, and a flattening has to make decisions. There are only three, and they are always the same three:

1. What is a row? Each element of the array is one row. If your JSON is a single object rather than an array, it becomes a single row.
2. What is a column? Each key is one column. Nested keys get joined into a path with a dot, so {"user":{"city":"NYC"}} produces a column named user.city.
3. What happens when objects differ? The header row is the union of every key the converter sees, in the order the keys first appear. An object that is missing a key leaves an empty cell in that column.

Everything that surprises people about JSON-to-CSV conversion is one of those three rules being applied in a way they did not expect. The most common surprise is rule two, because arrays are not covered by it: an array value never becomes columns. There is no sensible way to turn "tags":["red","blue"] into a fixed grid, so a converter writes the array into one cell as JSON text. If your JSON is full of arrays of objects, a single CSV is the wrong target format and no converter will make it right — more on that under when a converter is the wrong tool.

Step 1 — Get the JSON in

There are three ways in, and they all end up at the same place:

• Paste it. Fastest for an API response you already have on screen. A browser-based converter converts as you type, so you see the result before you commit.
• Upload a file. Works with .json and plain .txt files, including a .json file you exported from somewhere else.
• Fetch from a URL. Useful for a public API endpoint. This is the one method with a caveat: the browser fetches the URL directly, so the server has to allow cross-origin requests. A URL that works in your browser bar but returns an error in the converter is almost always a missing Access-Control-Allow-Origin header, not a bad URL.

Four input shapes are accepted, and the converter works out which one you have:

Step 2 — The two settings that change the output

Flatten nested: on or off

This is the setting that decides whether your CSV is usable. Both answers are correct in different situations, so it is worth seeing both outputs side by side.

id,user.city,user.zip
1,NYC,10001

id,user
1,"{""city"":""NYC"",""zip"":""10001""}"

With flattening on, every nested object is expanded to its leaves and the depth is unlimited — {"a":{"b":{"c":1}}} becomes a single column named a.b.c. That is what you want for analysis, sorting and pivot tables.

With flattening off, the nested value is written into its cell as a JSON string. That is the right choice when you intend to convert the CSV back to JSON later and need the structure preserved exactly, because the cell round-trips. It is the wrong choice for anything you want to read: notice the doubled quotes in the example above. Every " inside a CSV field has to be escaped by doubling it, so {"city":"NYC"} is stored as "{""city"":""NYC""}". That is correct CSV, and it is unreadable in a spreadsheet. If you see doubled quotes all over your output, that is the signal that flattening is off and should be on.

Delimiter

Comma is the default and is what almost everything expects. Change it in one situation: if your computer's regional settings use a comma as the decimal separator, Excel expects a semicolon-delimited file and will dump a comma-separated one into a single column. Tab and pipe are also offered, and a custom character is available for the rare file that needs one.

The delimiter also decides when a field has to be quoted. With a comma delimiter, the value Berlin, DE is written as "Berlin, DE" because it contains the delimiter. Switch to semicolons and the same value needs no quotes at all, because a comma is no longer special. That is not a bug in either case; it is the rule being applied correctly to two different files.

Header row and force quotes

Leave Header row on unless the file is being consumed by something that has its own schema and expects bare data rows. Force quotes wraps every field — including numbers — in double quotes. It is useful when a downstream parser is happier with a uniform file, and it is the wrong default because it turns numbers into text for strict importers.

Step 3 — Download it and open it without wrecking it

The conversion is done. What happens next is where most people conclude that "the converter produced a broken file", when in fact the CSV was correct and the spreadsheet changed it on the way in.

The file is written as UTF-8 with no byte order mark, with CRLF line endings and RFC 4180 quoting. Those are the right choices for the file itself — no BOM is what makes a CSV safe to read in Python, psql, jq and every other tool that would otherwise choke on three invisible bytes at the start of the header. It does mean Excel on Windows has to guess the encoding, and it guesses from your system locale.

So the fix is not to change the file. The fix is to stop double-clicking it:

1. Excel (Windows or Mac): go to Data → From Text/CSV, choose the file, set the delimiter, then click Transform Data. In the power query editor, set the columns that hold identifiers to type Text instead of Whole Number, then load. The encoding dropdown on the first screen is where you pick UTF-8 if your data has accents or emoji.
2. Google Sheets: File → Import → Upload, then untick "Convert text to numbers, dates and formulas". Leaving it ticked is what silently turns a ZIP code into a number and a 17-digit order ID into scientific notation.
3. Anything else: open the file in a text editor first. If the header row and the first two data rows look right as plain text, the file is right, and any weirdness after that belongs to the program you opened it in.

What actually comes out: 24 input shapes, measured

Conversion rules are easy to describe and easy to get wrong in the details. So rather than describe them, here is the exact output produced by the converter on this site, run against 24 input shapes. The middle column is the literal output, byte for byte.

Three invariants hold across all 24 cases and are worth memorising, because they are what you check when an output looks wrong:

• Line endings are CRLF (\r\n), the RFC 4180 convention. Unix tools read it fine; the reverse is not always true.
• Field order is deterministic. The same input always produces the same column order, so two runs can be diffed.
• Row count equals array length. Ragged objects and null values never add or remove rows, only empty cells.

A 60-second check before you trust the file

Open the downloaded CSV in a text editor, not a spreadsheet, and look at the header row and the first two data rows. Six things cover almost every real problem:

1. Count the header columns and compare with the widest row. They should match. If a data row has more fields than the header, your JSON has a key the header does not, which usually means two different object shapes are mixed in one array.
2. Look for doubled quotes. Any "" in the output means a nested object or array was written as JSON text. Turn flattening on if you wanted columns.
3. Check the longest numeric ID. If it ends in zeros it was never a safe JSON number. Quote it in the source and convert again.
4. Check the first character of every field if the JSON came from users or an untrusted API. A cell starting with =, +, - or @ is treated as a formula by Excel and Google Sheets. This is the CSV injection class of bug, and quoting does not prevent it — a quoted "=1+1" is still evaluated. Strip or prefix those characters in the source data, or import the file as text.
5. Check the row count against the length of your source array. If they differ, the input was not the shape you thought — most often a wrapper object that was auto-unwrapped.
6. Check for a stray BOM if another tool is reading the file. There is none, which is deliberate; if a downstream tool demands one, add it there rather than here.

When a converter is the wrong tool

A converter is the right answer for one-off and occasional conversions, for inspecting an API response, and for anything up to a few hundred thousand rows. It is the wrong answer in three situations, and recognising them early saves a lot of frustration:

• Arrays of objects that each need their own table. {"order":{"items":[{"sku":"A"},{"sku":"B"}]}} is two related tables, not one. Flattening it produces a single cell full of JSON, and un-flattening it produces duplicated rows that will corrupt any sum you calculate. Load it into a database, or split it into two CSVs keyed by order.id, before you analyse it.
• A conversion you will run again tomorrow. If the answer is a script, write the script. It runs on a schedule, it can be tested, and it can fail loudly. A converter is for the times you cannot be bothered to write one.
• Very large files. A browser-based converter holds the parsed data in memory, which is what keeps your data private and also what limits it. Tens of megabytes are fine on a normal machine; gigabytes are a job for a command-line tool.

Questions people ask next