JSON to CSV: how nested objects, quotes and big ids come out in the file

The CSV mode in the JSON Formatter applies five rules to a list of objects:

• Each object in the array is one row. A single object is one row too.
• Columns are the keys, in the order they first appear across all rows. A key that first shows up in a later object goes to the end of the header.
• A nested object becomes dotted columns, so address.city is one column name. The dots are only part of the name.
• A key a row doesn't have gives an empty cell.
• An array, or an empty object, goes into one cell as compact JSON.

The sample has a nested address, a missing address.state, a note with a comma and quotes, a tags array and a 16-digit id, so most of the rules above show up in it. The tool's Load sample JSON button loads this same data:

[
  { "id": 9007199254740993, "name": "Ada Lovelace", "address": { "city": "London" }, "note": "Wrote the first program, \"on paper\"" },
  { "id": 2, "name": "Grace Hopper", "address": { "city": "New York", "state": "NY" } },
  { "id": 3, "name": "Linus, the kernel author", "address": { "city": "Helsinki" }, "tags": ["dev", "oss"] }
]

id,name,address.city,note,address.state,tags
9007199254740993,Ada Lovelace,London,"Wrote the first program, ""on paper""",,
2,Grace Hopper,New York,,NY,
3,"Linus, the kernel author",Helsinki,,,"[""dev"",""oss""]"

The output has 3 rows and 6 columns, and its first line is the header. The file follows RFC 4180: rows are separated by CRLF, and the last row has no line break after it, which the RFC allows. A cell with a comma, a double quote or a line break goes in double quotes, and every double quote in the cell is written twice. That's why the note on the first row reads "Wrote the first program, ""on paper""", and the name on the last row reads "Linus, the kernel author". The tags array stays in one cell as compact JSON, "[""dev"",""oss""]".

CSV has no types and no nesting, so some things don't survive the trip. null and an empty string both become an empty cell, so a missing value and a blank one look the same. The string "1" and the number 1 both come out as 1. The dots in address.city are only part of the name, and an array of objects inside a field isn't spread into rows. It stays JSON in one cell. The tool stops, instead of writing a file, in these cases:

• Two keys would share one column name. {"a.b": 7, "a": {"b": 8} } gives Two different keys both become the column "a.b". Rename one of them so no value gets lost.
• An item isn't an object. [{"a": 1}, 5] gives $[1] is a number, not an object. Every item needs to be an object.
• The array is empty. [] gives The array is empty, so there are no rows to export.
• The JSON is a plain value. 42 gives CSV needs an array of objects, or one object. This JSON is a number.
• The objects have no fields. [{}] gives The objects have no fields, so there are no columns to export.

The tool copies numbers as written, so the id 9007199254740993 stays exact in the CSV. A converter that runs JSON.parse first would write 9007199254740992 instead. For more on that, see the large numbers guide.

Excel is the other trap, and it changes the data itself. Microsoft says "any numbers past the 15th digit are rounded down to zero," so the last digit of this id becomes a zero once it lands in a number cell. Format the column as Text before you paste the values in. According to Microsoft, that format only affects numbers entered after it's applied, so the order matters. To set it, select the range, press Ctrl+1, and pick Text on the Number tab.

A worksheet holds at most 1,048,576 rows and 16,384 columns, according to Microsoft, so split a long array into several files before you open it. Watch for formulas too. A cell that starts with =, +, -, @, a tab or a carriage return can be read as a formula, which OWASP calls CSV injection. The tool copies values as they are and adds no guard, so a cell like that comes out unchanged.

Keep the JSON as the source, and export CSV from it whenever you need a table. Avoid editing the CSV and treating it as the record, because a flat file can't rebuild the original shape.

RFC 4180 admits the format was never formally documented, so other programs may read variations. Avoid assuming that a file that opens in one tool will behave the same in another, and test it in the program that will read it.