Skip to content
TabShedKitsMy desk

Turn JSON from an API into a Spreadsheet-Ready CSV, and Back Again

Updated 5 min readBy

Convert an array of JSON objects to CSV and CSV to JSON: how headers, nested values, delimiters and number types are handled, and what to fix first.

JSON to CSV ConverterConvert between JSON and CSV
Open JSON to CSV Converter
On this page
  1. JSON to CSV: what shape works
  2. Many API responses wrap the list
  3. Columns come from all objects
  4. Nested values
  5. Delimiter and headers
  6. CSV to JSON: types
  7. Step by step
  8. Problems and fixes
  9. When this is not enough
  10. Frequently asked questions

An API returns a few hundred records as JSON. Your colleague wants them in a spreadsheet, and the spreadsheet cannot open JSON. Or the opposite: you have an export in CSV and a script that wants JSON. The JSON to CSV Converter does both directions in the browser. The conversions look simple, but the details, such as nested values, missing keys, delimiters and number types, decide whether the result is usable. This guide covers them.

JSON to CSV: what shape works

CSV is flat: rows and columns. JSON can be anything. The tool expects an array of objects, each object becoming one row and the keys becoming the columns. Anything else is rejected with a message: "JSON must be an array of objects" if the top level is not an array, and "Every item of the array must be an object" if the array contains numbers, text, null or other arrays.

Here is an example that converts cleanly:

[{"name": "John", "age": 30, "city": "New York"}, {"name": "Jane", "age": 25, "city": "Paris"}]

This becomes a CSV with the header name,age,city and two rows. The "Load sample" button puts this very example in the box.

Many API responses wrap the list

An API often returns an object such as {"total": 2, "items": [ ... ]}. The converter will refuse that, because the top level is an object. Copy only the array that sits under the key and paste that. If you need to look inside a large response first, the JSON Parser shows it as a tree, so you can find the right array.

Columns come from all objects

The header row is built from every key found in every object, in order of first appearance. If the first record lacks a field that the second has, the column still appears, and the first row gets an empty cell. This matters because many APIs omit empty fields. You do not need to make the records uniform before converting.

Nested values

A value that is an object or an array, such as "tags": ["a", "b"], cannot be a single plain cell. The tool writes it as JSON text inside the cell, quoted properly. The spreadsheet shows the text ["a","b"]. If you need each tag in its own column, flatten the data before converting, or split the column in the spreadsheet. Values that are null or missing become empty cells. Dates are plain text; the converter does not parse them.

Delimiter and headers

SettingOptionsWhen to change it
DelimiterComma, semicolon, tab and othersMany European spreadsheet settings expect a semicolon because the comma is the decimal mark. Tab is useful when values contain commas, and for pasting into a sheet.
Include headers (first row)On or offJSON to CSV: off removes the header row. CSV to JSON: off treats the first row as data and names the columns column1, column2 and so on.

Values that contain the delimiter, quotes or line breaks are quoted, and quotes are doubled, following the CSV standard RFC 4180. The downloaded file is data.csv and starts with a byte order mark, so that Excel reads accents and non-Latin text correctly.

CSV to JSON: types

CSV has no types, so the converter guesses, and does it conservatively. A cell becomes:

  • a number only if it is written as a plain decimal number whose text is exactly what the number prints as, so 30 and 3.5 become numbers but 007 and 1.50 stay as text. That protects zip codes, phone numbers and IDs with leading zeros;
  • true or false when the cell says exactly that;
  • an object or array when the cell starts with { or [ and is valid JSON, which makes a round trip from JSON to CSV and back work for nested values;
  • otherwise text. An empty cell becomes an empty string, not null.

Check IDs and codes in the result: a long numeric ID with more digits than a computer number can hold exactly would stay as text, which is what you want.

Step by step

  1. Choose the direction with the Direction control: JSON → CSV or CSV → JSON.
  2. Paste the data into the input, or load a file.
  3. Set the delimiter and whether the first row holds headers.
  4. Convert. The output appears on the right with a count of records.
  5. Copy it, or download data.csv or data.json. The swap button moves the output into the input and flips the direction, which is a quick way to test a round trip.

Problems and fixes

  • "Error converting JSON to CSV". The JSON is invalid or not an array of objects. Check the syntax with the JSON Formatter.
  • The spreadsheet puts everything in one column. The delimiter does not match your spreadsheet's setting. Convert again with a semicolon or tab.
  • Accents look broken in Excel. Use the downloaded file, which has a byte order mark, rather than a copy pasted into a text file without one.
  • Numbers lost leading zeros. That happens in the spreadsheet when it opens the CSV. Import the column as text.
  • Large files are slow. Everything runs in the browser. For files of many megabytes, use a command-line tool.

When this is not enough

Deeply nested JSON with several levels of lists cannot become one clean table without choices about how to flatten it. In that case write a small script, or convert each list to its own CSV. For one-off spreadsheets of flat records, the tool is faster than writing anything.

Frequently asked questions

Is my data uploaded?

No. The conversion runs in your browser.

Can I convert a single object?

Put it in square brackets to make an array of one object.

Does it keep the order of columns?

Yes, by first appearance of each key.