Converting JSON to CSV (and Back) Without Losing Data
8 min read
Applications speak JSON; spreadsheets, BI tools and finance teams speak CSV. Moving data between the two is a daily task, and it is simple right up until a value contains a comma, a field is nested, or an id has a leading zero. This guide covers how the conversion works and how to avoid the traps that silently corrupt data.
The shape that converts cleanly
CSV is a flat grid: rows and columns. JSON that maps cleanly onto it is an array of flat objects, where each object becomes a row and the union of all keys becomes the header.
[
{ "id": 1, "name": "John", "active": true },
{ "id": 2, "name": "Alice", "active": false }
]becomes:
id,name,active
1,John,true
2,Alice,falseHandling nested objects and arrays
CSV has no concept of nesting, so a field whose value is an object or array cannot map to a single cell cleanly. There are two common strategies: serialise the nested value as JSON text inside the cell, or flatten it into dotted column names (address.city, address.zip). Flattening is friendlier for spreadsheet users; serialising is lossless and reversible. Decide based on who consumes the CSV.
The escaping rules that matter
Following RFC 4180, any value that contains a comma, a double quote or a newline must be wrapped in double quotes, and embedded quotes are doubled. Get this wrong and a single value with a comma shifts every column after it.
Smith, John -> "Smith, John"
She said "hi" -> "She said ""hi"""A good converter handles this automatically, which is exactly why hand-rolling CSV with string concatenation is a bad idea.
The round-trip traps
- Leading zeros: a zip code like
01234is a string in JSON, but on the way back from CSV it looks like a number and loses its leading zero. Keep such fields quoted, or treat them as strings explicitly. - Type coercion: CSV is all text. Converting CSV → JSON means guessing types —
truebecomes a boolean,42a number, empty cellsnull. That guess is occasionally wrong (think phone numbers). - Missing fields: if objects have different keys, the header is the union of all of them and rows that lack a field get an empty cell.
Doing the conversion
The JSON ↔ CSV converter handles both directions in your browser, escaping values correctly and coercing types on the way back. Paste an array of objects, pick a direction, and copy or download the result. Nothing is uploaded, so it is safe for exports containing customer data.
Frequently asked questions
What JSON shape works best for CSV?
An array of flat objects. Each object is a row and the keys become columns. Nested structures need to be flattened or serialised first.
Why did my ID lose its leading zero?
Because CSV → JSON treats a numeric-looking cell as a number. Keep ids that must preserve leading zeros as quoted strings, and do not coerce them to numbers.
Will the CSV open in Excel and Google Sheets?
Yes. The output is standard RFC 4180 comma-separated text that imports cleanly into Excel, Google Sheets, Numbers and any BI tool.
Related
To load the same data into a database instead of a spreadsheet, see JSON to SQL; to reshape it before export, try the jq playground. New to the format? Read What is JSON?
Primary references
This guide is checked against the specifications and runtime documentation below. Examples are also run through JSON Wallet before publication.
Keep reading
What Is JSON? A Beginner’s Guide
JSON is the most widely used data format on the web. This guide explains what it is, how it is structured, and where you will run into it as a developer.
JSON Syntax Rules Explained (With Examples)
JSON has a small but strict grammar. This reference walks through every rule with valid and invalid examples so your data parses the first time.
Common JSON Errors and How to Fix Them
Every developer hits "Unexpected token in JSON" eventually. Here are the most common JSON errors, what causes them, and exactly how to fix each one.
Ready to put this into practice? Open the free JSON editor →