Convert JSON to CSV

How the JSON becomes a table

CSV is a flat table; JSON can be any shape. The converter maps one onto the other with a few clear rules:

• Rows. Each item in the top-level array becomes one row. A single object becomes a table with one row.
• Columns. Every key found in any object becomes a column, in the order the keys first appear. Objects do not need matching keys: where one lacks a key, its cell is left empty.
• Header. The first line holds the column names.
• Values. null becomes an empty cell, true and false are written as words, and numbers as digits.
• Arrays of plain values, such as [1, 2, 3], become a single column called value.

So [{"id": 1, "name": "Ada"}, {"id": 2, "email": "g@example.com"}] gives three columns, id, name and email, with the gaps left blank.

Nested objects and arrays

APIs rarely return perfectly flat data. When a value is itself an object or an array, the converter keeps it whole and writes it into the cell as JSON text. An address like {"city": "Oslo", "zip": "0150"} lands in one cell exactly like that, and tags like ["new", "sale"] stay a list in a single cell.

Nothing is lost that way, and code can turn the cell back into data later. If you want the city in a column of its own, flatten the data before converting: bring the fields you need up to the top level in your query or script, so each one becomes a key of its own.

Numbers deserve a look too. Whole numbers are exact up to 9,007,199,254,740,991; longer IDs stored as JSON numbers, such as 19-digit order numbers, lose their last digits when read. IDs stored as strings in quotes are copied exactly. Excel adds its own limit of 15 significant digits, so keep long IDs as text there as well.

Quoting, separators and opening it in a spreadsheet

CSV has one escaping rule, set out in RFC 4180: a value that contains the separator, a double quote or a line break is wrapped in double quotes, and each double quote inside it is doubled. Octoolo quotes every value that contains a comma, semicolon, tab, quote or line break, whichever separator you chose, so He said "hi", then left is written as "He said ""hi"", then left".

To work with the result as a spreadsheet, save it as a .csv file and open it in Octoolo's Spreadsheets, which guesses the separator, reads UTF-8 and turns numbers and dates into real values. From there, save it as an .xlsx workbook.

Keeping exports private, and PowerShell's way

JSON headed for a spreadsheet is usually real data: customers from a CRM, orders from a shop's API, users from a database. Converting it in an app on your PC, with no website involved, keeps that data where it already is.

Windows has a built-in route as well. In PowerShell, (Get-Content data.json -Raw | ConvertFrom-Json) | Export-Csv data.csv -NoTypeInformation writes a CSV file directly and works well for flat data. Two differences are worth knowing. Export-Csv takes its columns from the first object only, so keys that appear only in later objects are dropped. And nested values come out as PowerShell's own text, such as @{city=Oslo} or System.Object[], rather than JSON. Octoolo's converter collects every key and keeps nested values as JSON.