JSON vs CSV

What CSV cannot represent

CSV has rows and columns, and every cell is text. It has no nesting, no arrays, no types, no null distinct from empty, and — despite RFC 4180 — no universally honoured rules about quoting, line endings or the delimiter.

A JSON document that is an array of flat objects converts to CSV perfectly. Anything else requires a decision.

Flattening

Nested objects are usually flattened into dotted column names, which is lossless and reversible as long as no key already contains a dot.

Arrays are the hard case. There are three usual answers and none is right for every dataset: join the elements into one cell, produce one column per index (tags.0, tags.1 …), or emit one row per element and repeat the parent fields. The third is what a database would do; the first is what a spreadsheet user usually wants.

[
  { "id": 1, "user": { "city": "Oslo" }, "tags": ["a", "b"] },
  { "id": 2, "user": { "city": "Lima" }, "tags": [] }
]

id,user.city,tags
1,Oslo,"a,b"
2,Lima,

Types come back as guesses

Going the other way, every CSV cell is a string and the converter has to decide what it meant. Type inference is convenient and destroys data in a well-known set of cases: a leading-zero product code becomes a number, a long id loses precision, "TRUE" becomes a boolean, and a date is reformatted into something the source never said.

When the data is identifiers rather than measurements, turn inference off and keep everything as strings.

The empty-versus-null problem

CSV has one way to write "nothing": an empty cell. JSON has three — an absent key, null, and an empty string — and they usually mean different things. Any conversion collapses them on the way to CSV, and has to pick one on the way back. Decide explicitly rather than accepting the default.

Excel specifics

• Excel needs a UTF-8 BOM to read non-ASCII text correctly from a .csv file.
• In several locales Excel expects a semicolon, not a comma, as the delimiter.
• Excel converts anything that resembles a date, and long digit strings lose precision past 15 digits. Prefix a cell with an apostrophe, or use TSV and paste, when the values are identifiers.