Convert JSON to CSV
Paste an array of objects and read the table out. The report underneath says what the flattening cost: which columns were absent from which rows, how deep the nesting went, and how many cells a spreadsheet would have treated as code.
How it works
A table is a rectangle and JSON is a tree, so a conversion between them is a set of decisions about how to lose the shape. Nesting has to be spelled out along a path or thrown away. A list inside a cell has to be joined into one string or spread across several columns. Absent keys, nulls and empty strings all end up as the same nothing. None of that is avoidable, and the useful thing a converter can do is make each decision visible and then say what it cost.
Columns come from every row
Reading the keys of the first record and calling that the header is the common shortcut, and it drops any field that only shows up later. The union is taken here instead, in order of first appearance, so a nickname carried by the fourth record still gets a column and the first three rows get an empty cell. Every column absent from at least one row is reported with a count, because a sparse column is either a genuinely optional field or a misspelt key, and those two look identical once the file is written.
Command-line tools mostly decline to guess. In jq, the @csv filter takes an array, so the field list is something you write by hand, and a field you forgot is missing from the output with nothing to flag it. The json_normalize function in pandas does take the union across records and leaves NaN in the gaps, which to_csv then writes as an empty field.
Dot paths, and the key that already has a dot
A city nested two objects down becomes a column called user.address.city. That is the separator json_normalize uses by default, and it reads well until a document contains a key with a dot inside it. A single key spelled user.address.city produces the identical column from a completely different shape, and the CSV keeps no record of which one it was, so the trip back is a guess. Source keys containing a dot are listed in the report for that reason.
Arrays behave the same way under the indexed setting: tags.0, tags.1, one column per position, so two records with different list lengths produce a sparse column instead of a truncated value.
Empty, null, and the cell that cannot tell them apart
A CSV cell has one way to be empty: nothing between the delimiters. JSON has four. A null, an empty string, a key that was never there and an empty object all arrive as the same zero characters, and no reader can sort them out afterwards.
The return trip loses the distinction again from the other side, since read_csv in pandas treats an empty field as NaN unless told otherwise, so a real empty string comes back as missing data. The count of nulls written as empty cells appears in the report, which at least puts the loss in front of the person before a report gets built on top of it.
A leading equals sign is a program
Excel, LibreOffice Calc and Google Sheets all evaluate a cell that starts with an equals sign when the file is opened, and none of them asks first about an ordinary worksheet formula: a field containing =1+1 shows up as 2. Some formulas reach the network. IMPORTDATA in Google Sheets fetches a URL, and a URL can be concatenated with the contents of the cell next door, which sends a row of somebody's data to whoever wrote the CSV. Excel does warn before its DDE form starts another program, and that warning is the exception rather than the rule.
OWASP files this under CSV injection and names the characters that begin it: the four operators, a tab and a carriage return. The mitigation is one leading apostrophe, which a spreadsheet reads as a marker for text and does not display.
It costs something both ways. The apostrophe is visible in a text editor and in anything else that is not a spreadsheet, and a phone number written as +44 20 7123 4567 becomes text rather than the error Excel would otherwise put there. The guard here applies to strings only: a JSON number arrives typed, so -5 is arithmetic and is left alone, while the string "-5" gets the apostrophe. Switching the guard off does not switch the counting off, and the number of cells a spreadsheet would evaluate is reported instead.
Excel wants a byte order mark
A CSV opened by double-clicking it in Excel on Windows is decoded with the machine's own code page rather than as UTF-8, so José arrives as José and a Japanese name arrives as nothing readable. Three bytes at the front of the file, EF BB BF, change that, and Excel then decodes the rest as UTF-8. The mark is on by default for that reason, and it goes on the download alone.
Pasting it causes the mirror-image problem: the invisible character sticks to the first column name, so a script looking for a column called id finds one called \uFEFFid and reports that the column is absent. Python carries a codec named utf-8-sig whose only purpose is to remove it, which is a fair measure of how often this comes up.
When a field gets quotes
A field is wrapped in double quotes when it holds the delimiter, a double quote, a carriage return, a line feed, or a space at either end, and in no other case. A quote inside the field becomes two quotes. Python's csv module calls this minimal quoting and uses it by default, with quote-everything as the alternative; quoting everything is equally valid and doubles the punctuation for no gain.
One difference is deliberate. Minimal quoting in Python does not count an outer space as a reason, and this page does, because a trailing space is data and an unquoted trailing space is the first thing a reader throws away.
Questions
Why does Excel turn my phone number into an error?
Because a value like +44 20 7123 4567 begins with a plus sign, and Excel reads a leading plus as the start of a formula. The formula guard puts an apostrophe in front of it, which Excel treats as a marker for text and hides in the cell, so the number appears as typed. Turn the guard off and the value goes out untouched, with a count of the cells a spreadsheet will evaluate reported in its place.
Why do accented characters arrive mangled?
Excel on Windows decodes a double-clicked CSV using the machine's code page instead of UTF-8, which turns José into José. The byte order mark fixes it: three bytes at the start of the file tell Excel the rest is UTF-8. It is on by default and applies to the download only, since pasting an invisible U+FEFF into an editor makes the first column name unmatchable.
What happened to my nested objects?
Each leaf value becomes its own column, named by the path to it joined with dots, so a city two levels down appears under user.address.city. Lists of scalars either join into one cell with the separator you pick or take a column each, numbered from zero. A list of objects has no scalar form at all: with joining selected it keeps its own JSON text in the cell, and the report says which paths that happened to.
Comma or semicolon?
A comma, unless the file is headed for Excel on a machine whose regional list separator is a semicolon, which covers most of continental Europe. Excel splits a double-clicked CSV on the Windows list separator rather than on the comma, so a comma-delimited file lands there entirely in the first column. A tab avoids quoting for fields full of prose, and breaks on any value that contains a tab.
Can the CSV be turned back into JSON?
Approximately, and not exactly. Types are gone, since every cell is text once written. The difference between a null, an empty string and an absent key is gone, because a cell has one way to be empty. Dot paths can be rebuilt into objects, but not distinguished from a key that contained a dot to begin with. Keep the JSON if the JSON is the record of truth.