All posts
May 15, 20265 min read

JSON to CSV: turn an API response into a spreadsheet

You called an API, got back a block of JSON, and now someone wants it in a spreadsheet. This is one of the most common chores in everyday development, and it is mostly mechanical once you understand the shape CSV expects and the small set of rules that govern how values are escaped. This post walks through the whole conversion so you can do it by hand if you need to, or understand exactly what a converter is doing for you.

The shape CSV expects

CSV is a flat, two dimensional format: rows and columns, nothing more. There is no nesting, no types, no concept of an object inside a cell. So before anything can become CSV, the source data has to look like a table.

The ideal input is a JSON array of flat objects, where every object is one row and each key is one column:

[
  { "id": 1, "name": "Ada", "active": true },
  { "id": 2, "name": "Grace", "active": false }
]

That maps cleanly to:

id,name,active
1,Ada,true
2,Grace,false

If your API wraps the array in an envelope, for example { "data": [ ... ], "page": 1 }, you first need to pull out the data array. The thing you convert is always the array, not the wrapper. If the response is a single object rather than an array, treat it as an array with one element.

How columns are derived

Columns come from the keys of the objects. The reliable approach is to scan every object in the array and collect the union of all keys, not just the keys of the first object. APIs are inconsistent: one record might include an email field while another omits it. If you only read the first object you can silently drop columns that appear later in the list.

Once you have the full set of keys, that becomes your header row, and every data row writes its values in that same column order. Key order in JSON is not guaranteed to be meaningful, so converters usually preserve first seen order or sort alphabetically. Either is fine as long as it is consistent across all rows.

The escaping rules

This is the part people get wrong, and it is where malformed CSV comes from. A raw value can be dropped into a cell as is, except in three cases. When a value contains any of the following, the entire field must be wrapped in double quotes:

  • a comma, because the comma is the field separator
  • a double quote character, because it is the quoting character
  • a line break (newline or carriage return), because that would otherwise start a new row

When you wrap a field in quotes, any double quote inside the value is escaped by doubling it. So the string She said "hi" becomes "She said ""hi""". A value like Smith, John becomes "Smith, John". A value containing a newline stays on one logical row because the quotes tell the parser the line break is data, not a row boundary.

These rules come from RFC 4180, the closest thing CSV has to a specification. Following them means the file opens correctly in Excel, Google Sheets, and any compliant parser. Skipping them is how you end up with shifted columns and broken rows.

A few practical notes:

  • Use CRLF (\r\n) line endings for maximum Excel compatibility, though most tools accept \n.
  • If your data is full of commas, some teams prefer a semicolon or tab delimiter. That is a separate convention, and the receiving spreadsheet must be told which delimiter to use.

Nested objects, arrays, and missing fields

Real API responses are rarely flat. When a value is itself an object or an array, there is no single correct answer, only conventions:

  • Nested objects are commonly flattened with dotted keys. A field address: { city: "Paris" } becomes a column named address.city. This keeps everything in one row at the cost of wider tables.
  • Arrays are often serialized back to a JSON string inside the cell, for example ["a","b"], or joined with a separator. Flattening arrays into columns is fragile because lengths vary between rows.
  • Missing fields become empty cells. If one record lacks a key that other records have, that row simply has an empty value in that column. This is why collecting the full union of keys matters.

The trade off is always the same: flatten deeply and the table gets wide and sparse, or stringify nested structures and keep the table narrow but less readable. Pick based on who is opening the file.

Why types collapse to text

CSV has no type system. Numbers, booleans, null, and strings all become plain text in the file. The spreadsheet that opens it guesses types afterward, and those guesses can bite you: leading zeros on ZIP codes or IDs get stripped, long numbers turn into scientific notation, and values that look like dates get reformatted. If a field must survive intact, the safe move is to keep it quoted and be aware the receiving program may still coerce it. Validate your JSON first so you are not converting something subtly broken. The JSON Formatter will flag syntax errors and let you confirm the array shape before you convert.

Do it without uploading your data

When the response contains anything sensitive, you do not want to paste it into a random web service that ships it to a server. The JSON to CSV Converter runs entirely in your browser: the conversion happens locally and nothing you paste is uploaded or stored. It handles the key union, the escaping, and the nested field flattening described above, so you get clean CSV without thinking about RFC 4180 by hand.

If your data is YAML rather than JSON, convert it first with the YAML to JSON converter, then run the result through the CSV step. The same local only, nothing uploaded approach applies to every tool here.