Convert JSON to CSV

How to use it#

1. Paste a JSON array, or a single object for a one-row table.
2. Pick the delimiter your spreadsheet expects, and decide whether nested objects get their own columns.
3. Copy the CSV or download it as a file.

Working out the columns#

JSON records do not have to agree on which properties they carry, and a CSV table does. The columns here are the union of every key across every object, in the order the keys were first encountered rather than alphabetically, because first-seen order usually matches how the data was designed.

A record missing one of those properties gets an empty cell. This is worth knowing because an empty cell in CSV is ambiguous: it could mean the property was absent, or present and null, or present and an empty string. All three come out the same way, and the distinction is not recoverable from the file.

If that distinction matters, CSV is the wrong format for the job and the answer is to keep the JSON.

Nested objects, and the two ways to handle them#

JSON nests and CSV does not, so something has to give. By default a nested object is written into its cell as JSON text, which keeps the data complete and makes the cell unpleasant to read. Turning on flattening instead gives every leaf its own column, named by its path: a user object containing a name produces a column called user.name.

Flattening is usually what you want when the nesting is shallow and consistent, which covers most API responses. It works badly when records nest differently from each other, because the column count grows with the union of every path that appears anywhere.

Arrays are left as JSON either way. Giving each element its own column would make the header depend on whichever record happens to have the longest array, and that is rarely useful.

Opening the result without breaking it#

Excel decides how to split a CSV using the list separator from your system’s regional settings, which is a semicolon in much of Europe. A comma-separated file opened on such a machine arrives with every row crammed into one column. Choosing the semicolon delimiter here avoids that, as does importing the file rather than double-clicking it.

The other classic problem is Excel reformatting values on the way in. Long numbers become scientific notation, anything resembling a date becomes a date, and leading zeroes are stripped. None of that is caused by the CSV, which is plain text and holds exactly what is shown here. Using the import wizard and marking those columns as text is the way to keep them intact.