What happens to nested objects and lists
A CSV cell holds one value, so nested data has to be spread out. Each nested object becomes columns named by their path: {"id": 1, "address": {"city": "Lisbon"}, "tags": ["a", "b"]} gives the columns id, address.city, tags.0 and tags.1.
- Separate columns keeps every list item in its own cell. It is the only choice that converts back to the same JSON.
- Joined into one cell writes
a;b, which is easier to read when lists are short and made of plain values. Lists that contain objects are written as JSON text instead. - JSON text in one cell keeps the whole list exactly, as
["a","b"]. - Turning Flatten nested values into columns off keeps every nested object and list as JSON text, so you get one column per top-level key.
An empty list or object is written as the text [] or {}, and is turned back into an empty list or object when you convert the CSV to JSON again.
Records with different keys
JSON records do not have to share keys. The converter reads all of them and uses the union of their keys as the columns, in the order the keys first appear. A record that lacks a key gets an empty cell, and a null value is written as an empty cell too. Booleans are written as true and false, and numbers in their shortest form.
That means a few columns near the end are often mostly empty when only some records have an extra key. That is correct, not a fault. If you do not want a column, remove it in your spreadsheet.
What spreadsheets expect from a CSV
Excel and Google Sheets open CSV files, but they apply their own rules when they do:
- Delimiter: Excel uses the list separator from the computer's regional settings, which is a semicolon in many European countries. If everything lands in one column, choose a semicolon under Delimiter in the output.
- Accents: Excel for Windows often reads a CSV as ANSI unless the file starts with a byte order mark. Turn on Add a byte order mark under More options.
- Leading zeros and long numbers: Excel turns
00123into 123 and shows numbers with more than 15 digits in scientific notation, rounding them. To keep such values, convert to an Excel file with the Data Converter instead of CSV. - Formulas: text that starts with = + - or @ can be run as a formula when the file is opened. Turn on Protect against spreadsheet formulas if the data comes from people you do not know.
Which part of your JSON becomes the rows
A list at the top level gives the rows, and a list of lists is read as a grid with the first list as the column names. An object that holds a list of objects uses that list, or the longest one if there are several, including lists inside wrappers such as data or results. The other top-level keys, such as status or count, are left out, and a note names them.
A plain object becomes one row. To choose another part, type its path under Rows come from, for example data.items, or a single dot to turn the whole value into one row. The same rules apply to YAML and XML in the Data Converter.