Guide

JSON to CSV and Back: Quotes, Delimiters and Formulas

Short answer: for JSON to CSV, give the JSON ⇄ CSV Converter an array of objects. Each object becomes a row, and the columns are the union of all keys. For CSV to JSON, pick the right delimiter and decide whether the first row is a header. Quoting follows RFC 4180, and everything runs in your browser.

A worked example

This JSON has a comma inside a name and a missing key:

[{"name":"Lee, Ann","age":31,"city":"Oslo"},{"name":"Raj","age":"n/a"}]

It becomes:

name,age,city
"Lee, Ann",31,Oslo
Raj,n/a,

The second object has no city, so that cell is empty. The cell with a comma is quoted.

Quoting rules

Cells that contain the delimiter, quotes or line breaks are wrapped in quotes, and quotes inside are doubled: say "hi" becomes "say ""hi""".

Nested data is not flattened

JSON to CSV does not flatten nesting. A nested object or array is written into one cell as JSON text, for example "[""a"",""b""]". If you need one column per nested field, flatten the data first.

CSV to JSON: why 007 stays text

Numbers, true, false and null are detected only when they round-trip exactly. So in this CSV:

id,price,flag,empty,zip
1,9.50,true,,007

price stays "9.50" and zip stays "007", because turning them into numbers would give 9.5 and 7. Only values that survive the conversion unchanged become numbers. An empty cell stays an empty string.

Delimiters

You choose comma, semicolon, tab or pipe. The tool does not guess. Spreadsheets in countries where the decimal separator is a comma often export semicolons, so a CSV that comes out as one wide column usually needs the semicolon option.

Headers

With "first row is header" on, the first row supplies the keys. Repeated or empty names are reported, because an object holds one value per key and a repeated name keeps only the last column. Rows with extra fields get keys such as column4. With the option off, you get an array of arrays.

Spreadsheet formula injection

A cell that starts with =, +, - or @ can be run as a formula when the file is opened in Excel or Sheets. In the example above, =SUM(A1) in a name column is exactly that. The option Protect against spreadsheet formulas adds a leading apostrophe to such text cells. It is off by default, so turn it on whenever the data came from users and the file will be opened in a spreadsheet.

The protection changes the data: a text value such as -5 gets an apostrophe. Turn it off when the file is for a program rather than a spreadsheet.

How to use the converter

  1. Open the JSON ⇄ CSV Converter.
  2. Choose JSON to CSV or CSV to JSON, and the delimiter.
  3. Set the header, number-detection and formula-protection options.
  4. Paste the input, check the output, then copy or download it.

If the JSON may be broken, validate it first: JSON syntax errors. For a file with one JSON object per line, the NDJSON tool fits better; see the NDJSON validator guide. To see what changed between two JSON documents, read how to compare two JSON files. To read a CSV as a sortable table, use the CSV Viewer.

Limits to know

  • The whole input is held in memory, so very large files need a streaming command-line tool.
  • The delimiter is chosen, not detected.
  • Nesting is stored as JSON text, not flattened.

Frequently asked questions

How do I convert JSON to CSV? Paste an array of objects and choose JSON to CSV.

Why did my CSV become one column? The delimiter is probably a semicolon.

Why is 007 still text? Converting it to a number would change it to 7.

What is CSV injection? Cells starting with =, +, - or @ can run as formulas in a spreadsheet.

Does it follow a standard? Quoting follows RFC 4180.

Related tools and guides

Sources and further reading