From API response to spreadsheet
Almost every API returns JSON, and almost every person who needs to look at that data wants it in a spreadsheet. CSV is the bridge — universally readable, and the format every analytics tool, database import and finance team accepts without argument.
The conversion is mechanical but fiddly to get right by hand. Columns have to be derived from keys that may not appear in every record, values containing commas must be quoted, and quotes inside those values must be doubled rather than backslash-escaped. Getting any of that wrong produces a file that opens but is subtly misaligned.
How rows and columns are built
- Each array element becomes one row; the header line lists every key found.
- Column order follows first appearance, so the first record sets the layout.
- Keys that appear only in later records are appended as extra columns.
nulland missing values become empty cells, not the literal text "null".- Nested objects and arrays are serialized as compact JSON inside the cell.
Quoting, the part that bites
A value such as Smith, Jane would split into two columns if written bare, so it is wrapped in double quotes. A value containing a quote — He said "hi" — becomes"He said ""hi""", doubling the inner quotes. Values with line breaks are quoted too, which keeps a multi-line description in a single cell exactly as RFC 4180 specifies.
Picking a delimiter
Comma is the default and the right choice for most tooling. Reach for the others when the data or the destination demands it: semicolon for European Excel locales where the comma is a decimal separator, tab for a TSV that pastes cleanly into Google Sheets, andpipe when your values are full of both commas and quotes and you want minimal escaping.
The same job in code
# Python — convert a JSON string to CSV
import csv, io, json
rows = json.loads(payload)
buf = io.StringIO()
w = csv.DictWriter(buf, fieldnames=rows[0].keys())
w.writeheader(); w.writerows(rows)
# jq — array of objects to CSV with a header
jq -r '(.[0] | keys_unsorted), (.[] | [.[]]) | @csv' data.json
# SQL Server — JSON array to comma-separated values
SELECT STRING_AGG(value, ',') FROM OPENJSON(@json)Python's csv module and jq's @csv filter both apply the same quoting rules, so their output matches this tool's. Note that keys_unsortedmatters in the jq version — plain keys alphabetizes your columns.
What CSV cannot represent
CSV is a grid, and a grid has no way to express nesting, types or missing-versus-empty. Every value becomes text the moment it is written, so true, "true" and the string TRUE all arrive at the other end indistinguishable. A null and an empty string both become an empty cell. And a nested object has to be either serialized into a cell, as here, or flattened into columns such as user.name — which works until one record nests two levels deeper than the rest.
None of this is a reason to avoid CSV. It is a reason to treat the conversion as one-way: fine for a report, a spreadsheet or a bulk import, unwise as an interchange format between two systems you control. Where fidelity matters, keep the JSON.
Before you open it in Excel
Two habits save time. Excel interprets a leading =, +, - or@ in a cell as a formula, which is both a display problem and a genuine injection risk when the data came from users — prefix such values with an apostrophe, or import as text. And Excel guesses encoding on double-click, so a file with accented names or emoji is best opened through Data → From Text/CSV with UTF-8 selected explicitly.
Need the opposite direction, or a document that is not an array yet? Start at thestring to JSON converter or reshape it with theformatter.