JSON to CSV: How to Flatten an API Response into a Spreadsheet
What converts cleanly, how nested objects and missing keys are handled, and the delimiter trap that breaks CSV in Excel.
You pull data from an API, get back a clean JSON array, and then someone asks for it “in a spreadsheet.” That gap, JSON in your terminal and CSV in their inbox, is where this conversion lives. It sounds trivial, and for tidy data it is. The trouble starts when the data isn’t tidy.
Why you end up doing this
Most of the time it isn’t a grand data pipeline. It’s smaller and more annoying than that:
- A product manager who wants to sort 200 API records in Excel, not squint at them in a code editor.
- A one-off export from some tool that only speaks JSON, headed for Google Sheets.
- Quick eyeballing: a CSV opens in a spreadsheet and you can scan a column in seconds, which is hard to do with nested braces.
You could write a csv.DictWriter script or a jq one-liner every time. But for a single array you saw five minutes ago, opening the JSON to CSV Converter, pasting, and copying the result beats reaching for the jq syntax. (I never remember the jq syntax.)
What converts cleanly
An array of flat objects is the happy path. Each object becomes a row, each key becomes a column:
[
{ "id": 1, "name": "Ada", "active": true },
{ "id": 2, "name": "Linus", "active": false }
]Three columns, two rows, header included. The converter walks every object first and collects the full set of keys, so if one record has a phone field and the next doesn’t, you still get a phone column. The missing cell comes out blank instead of shifting the rest of the row sideways. That detail matters more than it sounds: a single skipped field is how CSV files quietly corrupt, with values sliding under the wrong headers from that row down.
Where it gets messy: nested data
Real API responses nest. An order carries a customer object; a user has a roles array. CSV is flat. There’s no honest way to drop an object into a single cell, so you’re left with two compromises:
- Serialize the nested part back to JSON text and put that in the cell. Nothing is lost, but you’ve got JSON inside CSV, which is ugly to read.
- Flatten first, turning
customer.emailinto its own column.
This tool takes the first route: a nested value becomes JSON text in the cell. If you need customer.email as a proper column, flatten the JSON before you paste it. Keeping the data and letting you decide beats silently dropping the customer object on the floor.
The delimiter trap
Here’s the one that costs people an afternoon. You build a perfect comma-separated file, open it in Excel, and every row lands in a single column. Nothing is broken. Excel is reading the file with the list separator from your regional settings, and across much of Europe that separator is a semicolon, not a comma.
The fix is to match the file to whoever reads it. Switch the delimiter to semicolon and convert again, or use tab (TSV), which dodges the comma-versus-semicolon fight because tabs almost never show up inside the actual data. The converter offers all three; pick the one your spreadsheet expects.
Commas and quotes inside values
The other thing that breaks naive converters is a value that itself contains a comma, like Smith, Jane. Written raw, that comma reads as a new column. The CSV rule is to wrap the field in double quotes, and if the value already has a quote, double it. The converter does this for you following RFC 4180, so an address or a sentence with punctuation stays put in one cell.
Paste your array into the JSON to CSV Converter, pick a delimiter, and copy the table straight into Sheets. Nothing is uploaded, and there’s nothing to install.