JSON to CSV in 5 Minutes: Open API Data in Excel
2024-05-10
PMs and analysts often need JSON in a spreadsheet. CSV is the universal bridge—one row per record, commas between columns.
Ideal input shape
Top-level array of objects with consistent keys. Flatten nested objects (address.city) before export.
[ { "id": 1, "name": "Alice" }, { "id": 2, "name": "Bob" } ]Manual CSV in four steps
Pick headers, write header row, map each object to a row, quote fields that contain commas or newlines.
id,name
1,Alice
2,BobQuick JavaScript export
Parse JSON, escape per RFC 4180, join with newlines, download as Blob.
const escape = (v) => {
const s = String(v ?? "");
return /[",\n]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s;
};Nested data
One-to-many relations → two CSV files linked by id, not one wide unmaintainable row.
Excel encoding
Use UTF-8 import or BOM on Windows; or export .xlsx with a library if CSV encoding fights you.
Workflow
Validate JSON → confirm keys → generate CSV → import in Excel → automate for recurring exports.