JSON to CSV and CSV to JSON

JSON and CSV are used in different places

JSON is the normal format for APIs, config files and most things a developer writes. CSV is the normal format for spreadsheets, database imports and most things a non-developer opens. Converting between the two is a very common daily task.

The two have a different shape. JSON can be nested; CSV is flat. JSON has types; CSV has only strings. This tool handles the common case, an array of flat objects. Anything nested more than one level deep loses detail when it goes through CSV.

When to use this tool

• Exporting an API response to a spreadsheet. Paste the JSON, copy the CSV, and paste it into Excel or Google Sheets.
• Importing a spreadsheet into a seed script. Save the sheet as CSV, paste it here, and copy the JSON.
• Building test fixtures. A CSV from the QA team becomes an array of objects you can JSON.parse in a test file.
• Changing data without code. To add a column, edit the CSV. To add a nested value, edit the JSON. The tool keeps both panes in step.

What this tool does

• Header row. The first CSV row becomes the keys. Turn off "Header row" if your data has no header; the keys are then col_1, col_2 and so on.
• Objects with different keys. If row 1 has keys A, B, C and row 2 has keys B, C, D, the CSV header is A, B, C, D and the missing cells are empty.
• Delimiters, quotes and new lines inside a value. A value with the delimiter, a double quote or a new line is wrapped in double quotes, and a quote inside it is doubled. The CSV reader understands the same rules.

Worked examples

• The sample. [{"id": 1, "name": "Tenali Ramakrishna", "role": "minister", "active": true}, ...] becomes id,name,role,active and then 1,Tenali Ramakrishna,minister,true, one line per object.
• Missing keys. [{"a": 1, "b": 2}, {"b": 3, "c": 4}] becomes a,b,c, then 1,2, and ,3,4.
• A comma and a quote in a value. [{"name": "Raman, Tenali", "quote": "He said \"hi\""}] becomes name,quote, then "Raman, Tenali","He said ""hi""".
• Nested values. [{"id": 1, "tags": ["wit", "court"]}] becomes id,tags, then 1,"[""wit"",""court""]". The array is written as a JSON string in one cell.
• CSV back to JSON. id,name and then 1,Tenali becomes [{"id": "1", "name": "Tenali"}]. Every value comes back as a string, because CSV has no types.
• No header row. With "Header row" off, 1,Tenali becomes [{"col_1": "1", "col_2": "Tenali"}].

Common mistakes

• Numbers come back as strings. JSON "007" survives the round trip. Bare 007 is not valid JSON at all. CSV has no types, so a CSV column of 007, 008, ... gives you strings.
• Nested objects end up in one cell. A value that is an object or an array becomes a JSON string in the CSV cell. If you need one column per nested key, flatten the JSON first.
• Excel and the BOM. Excel needs a UTF-8 BOM at the start of a CSV file to show Unicode correctly. This tool does not add one, because the output is meant to be pasted, not downloaded. Add it yourself if you save to a file and open it in Excel.
• Quote style. "Always quote" is safer when a strict CSV parser will read the data again. The default, quoting only when needed, is easier to read and is what most exporters do.
• The top level is not an array. A single object like {"id": 1} is refused. Wrap it in brackets: [{"id": 1}].

FAQ

What JSON shape does this expect?

A top-level array of objects, for example [{"id": 1, "name": "..."}, {"id": 2, "name": "..."}]. All the keys seen across the objects make up the header row.

What if some objects are missing keys?

Missing values become empty cells. The header row has every key seen in any object, in the order they first appear.

How are nested objects handled?

Nested values are turned into text with JSON.stringify and written into a single cell. CSV is flat. If you need to flatten them another way, change the JSON first.

Which delimiter should I use?

Comma is the default. Semicolon is common in countries where the decimal separator is a comma. Tab is the safest when the data has both commas and semicolons. Pipe is sometimes used in finance.

Does the CSV reader handle quotes and escaped quotes?

Yes. A field wrapped in double quotes can hold delimiters, line breaks and "" (a doubled quote), which is read as one ".