CSV to JSON

CSV is not as simple as it looks

Everyone's first instinct is to parse CSV by splitting each line on commas, and that approach breaks on real data almost immediately. Values legitimately contain commas — 'Smith, John' is one field, not two — so they get wrapped in quotes. Values contain quotes, so those get escaped by doubling. Values contain line breaks, so a single record can span multiple physical lines, which means you cannot even reliably split the file into records by newline. Add the fact that CSV was never formally standardised until 2005, long after everyone had already implemented their own variant, and you have a format that is trivial to read by eye and genuinely fiddly to parse correctly. This is why you should always use a real parser rather than a split.

The type-inference trap

CSV has no types — every cell is just text — and JSON does, so converting between them forces a guess. Usually the guess is helpful: the string '42' becomes the number 42, which is almost certainly what you meant. Sometimes the guess is catastrophic. A US zip code stored as 02134 becomes the number 2134, and the leading zero is gone forever. A phone number, a product SKU, a bank account, an ISBN — all of these look numeric and none of them are numbers, because you would never do arithmetic on them and their leading zeros are meaningful. Anyone who has watched Excel eat the leading zeros from an entire column of identifiers knows exactly how expensive this is. Where a field is an identifier rather than a quantity, keep it a string.