Free JSON to CSV converter – with a separator of your choice

JSON (JavaScript Object Notation) is a way of writing data in which every value carries its own name. It is described by RFC 8259, which the IETF gave the status of Internet Standard STD 90[1]. The format covers four simple types: string, number, boolean and the empty value null. Two structured types come on top of that, the object and the array[1]. Those two hold further values, so data can have several levels: an order holds a list of items, and each item its own set of fields.

CSV (Comma-Separated Values) describes data differently. The file is a table: the first row usually gives the column names, each following row is one record, and a single character separates the values inside a row, a comma in the notation RFC 4180 describes[2]. Field names appear once, at the very top of the file, and govern the whole column beneath them.

The whole conversion follows from that one difference. The tool writes JSON field names into the header row and the values beneath them.

Paste an array of JSON objects into the left panel and click Convert. The tool collects the field names from every object, builds the first row out of them, and adds one row per object. The CSV appears on the right.

Take two people written as [{"name":"John","age":30},{"name":"Anne","age":25}]. The result is three rows. The first is the header name,age. The second reads John,30 and the third Anne,25.

The tool gathers field names from every object in the array. When the second object carries a field the first one lacks, the column is created anyway, and records without it get an empty cell. Data from an API with optional fields is therefore saved in full.

That leaves the character which will separate the columns. You pick it in the CSV field separator list under the input panel, and it decides whether a spreadsheet spreads the data across columns or keeps the whole row in one. Download saves the file, and Copy puts the text on the clipboard.

The tool writes an object or an array from inside a record into a single cell as JSON text. The field {"street":"Baker Street 5"} therefore takes one column and keeps its whole content, ready to be read again. The reason is straightforward: a CSV column holds one value, while an object holds several of them together with their names.

A null value gives an empty cell. So does the place where a record simply has no such field. When a value holds the chosen separator, a quote or a line break, the tool wraps it in quotes and doubles the quote inside — this is the notation described in RFC 4180[2], so spreadsheets and libraries read such a cell correctly. A timestamp from an API, such as 1700000000, goes into the cell as a number, and the Unix timestamp to date converter reads the date from it.

The downloaded file is encoded in UTF-8 and starts with a byte order mark, BOM for short. By those three bytes a program recognises that the rest is written in UTF-8. Microsoft says it plainly: a CSV file in UTF-8 opens normally if it was saved with a BOM[3]. Names and addresses with accented letters therefore display correctly even when you open the file with a double click.

The reverse conversion is done by the CSV to JSON converter, which turns the same table back into records. If the data should keep its levels, use the JSON to XML converter and the JSON to YAML converter, because both formats record multi-level data.

It also happens that the data meant for a spreadsheet is written in XML or YAML. Two steps are needed then: first the XML to JSON converter or the YAML to JSON converter, and then this tool, which finds the list of records in the result by itself.

The commas then show up inside the cell together with the data. It means the spreadsheet was looking for a different separating character than the one in the file. Excel takes that character from the system settings, which tie the list separator to the decimal mark[4].

On British and American settings the decimal mark is a point, so the comma stays free for columns and the default works. On French, German or Czech settings the comma is the decimal mark, which leaves the semicolon for columns. For a file meant for such a spreadsheet, pick Semicolon in the CSV field separator list and convert once more.

You can also set the separating character in the spreadsheet itself: the import on the Data tab lets you choose it by hand, whatever the system settings are. This helps when the file comes from someone else and cannot be produced again.