Convert JSON, CSV, YAML and XML

Four formats, two shapes

JSON, YAML and XML are trees: values can hold lists and other objects, nested as deep as needed. CSV is a flat table: rows and columns, nothing inside a cell but text. Converting between the tree formats keeps everything. Converting a tree into CSV has to flatten it, and that is where the choices are.

From JSON to CSV

The converter expects a list of objects, one per row, such as an API response with an array of users. A single top level key holding that list, as in {"users": [...]}, is unwrapped automatically. Each key becomes a column; with Flatten nested data into columns on, a nested object becomes several columns named by their path:

{"name": "Ada", "address": {"city": "London"}, "tags": ["math", "code"]}

name,address.city,tags[0],tags[1]
Ada,London,math,code

With flattening off, nested values are written into one cell as JSON text, which keeps them intact but is harder to read in a spreadsheet. Rows with different keys still line up: every key that appears anywhere becomes a column, empty where a row has no value.

From CSV back to JSON

• First row is a header uses the first row for key names; off, rows become plain lists.
• Detect numbers and booleans turns 42 into a number and true into a boolean instead of text. Switch it off for codes that must keep leading zeros, such as postal codes and phone numbers.
• Rebuild nested objects from dotted headers turns address.city columns back into nested objects, reversing the flattening above.
• Delimiter is detected automatically: commas, semicolons as Excel writes them in much of Europe, tabs or pipes.

XML specifics

XML has attributes as well as elements. With Treat @_ keys as attributes on, keys starting with @_ become attributes, so {"item": {"@_id": "7", "name": "Pen"}} becomes <item id="7"><name>Pen</name></item>, and reading XML gives the same shape back. Set the name of the Root element that wraps the document when converting to XML.

Many files at once

Switch to Files to convert a batch, for example a folder of JSON exports to CSV, with the same settings for all. For spreadsheets, Excel to CSV reads and writes XLSX files, and the CSV viewer opens large CSVs to sort and filter. Our comparison of CSV, Excel and JSON measures how the formats differ in size and speed.

If something goes wrong

• Leading zeros disappear. Detect numbers is on. Switch it off for that file.
• Accents look wrong when the CSV is opened in Excel. Excel guesses the encoding. Use Data, From Text/CSV and choose UTF-8, or open the file through the CSV viewer.
• Hundreds of columns appear. The JSON has long arrays inside each row, and every item becomes a column. Switch flattening off to keep each array in one cell.