CSV, JSON and YAML

What each one is for

CSV is a table: rows and columns, nothing else. It is universally readable, trivially small, and cannot express anything nested. JSON is a tree — objects inside arrays inside objects — and is what almost every web API speaks. YAML expresses the same structures as JSON but using indentation instead of braces, which makes it far pleasanter for humans to read and edit, and is why configuration files have drifted towards it.

The type coercion trap

This is the one that actually costs people money. A CSV contains only text — there is no way to mark a value as a number, a date or a string. So converters guess, and the guesses are frequently wrong:

• A postcode like 01234 becomes the number 1234, losing the leading zero.
• A product code like 1-2 becomes a date.
• A long account number becomes floating point and loses its final digits.
• NA, meaning North America, becomes "not available".

Every one of those is silent. This is why a careful CSV-to-JSON conversion should leave every value as a string and let you decide — which is what this site's converter does deliberately, even though it looks pedantic at first.

What does not survive the trip to CSV

Nesting. A JSON array of flat objects becomes a clean table. The moment a value is itself an object or an array, there is no column to put it in. The options are to flatten with compound keys, to serialise the nested part back into text inside its cell, or to drop it — and dropping it silently is the worst of the three. Writing it back as JSON text inside the cell at least keeps the data recoverable.

YAML's sharp edges

YAML is pleasant to read and unusually easy to get wrong.

• Indentation is structure, and tabs are not allowed. A misplaced space changes the meaning rather than causing an error.
• Some bare words are booleans. In older YAML, y, n, on, off, yes and no all parse as true or false — so a country column containing NO for Norway becomes false. Quote them.
• Comments vanish on conversion to JSON, because JSON has no way to represent them.

Round-tripping is not guaranteed

Converting JSON to YAML and back usually returns what you started with. Converting CSV to JSON and back usually does not: quoting style, column order for records with differing shapes, and the representation of empty values are all choices, and different tools choose differently. If a round trip has to be exact, verify it rather than assuming.

To move between them: convert CSV to JSON, convert JSON to CSV, or convert JSON to YAML — and check the result, since round-tripping is not always exact.