Convert CSV to JSON

Where CSV is vaguer than it looks

CSV has no real standard — there is an advisory specification from 2005 that describes what most tools do, and a great deal of software that does something else. So the first job is working out what the file actually is: whether values are separated by commas, semicolons or tabs (European locales export semicolons, because the comma is their decimal separator), whether the first row is a header, and how quoting is handled. A quoted field can legally contain commas, newlines and doubled quote marks, which is why splitting on commas is the wrong way to read one and why a naive parser corrupts any file containing an address.

Types are the second job, and JSON has opinions CSV does not. A CSV cell contains text and nothing else; JSON distinguishes numbers, strings, booleans and null. Something has to decide which is which, and the decisions that matter are the ones that go wrong: a leading zero is significant in a postcode, a phone number or an account reference, so a value like 01234 must stay a string rather than becoming the number 1234. The same applies to long identifiers, which lose precision as JSON numbers beyond about nine quadrillion.

Dates stay as the text they were, because JSON has no date type. 2024-03-05 comes out as the string "2024-03-05", and that is the honest answer rather than a limitation — the alternative is guessing, and guessing is how 03/05/2024 becomes March in one country and May in another. Converting them is a decision for whatever consumes the JSON, which knows the format it wants.

Encoding is the last quiet failure. A CSV carries no note of its character encoding, so a file saved as Windows-1252 and read as UTF-8 produces mojibake in exactly the rows containing accented names — and a byte order mark at the start, which Excel adds, appears as stray characters in the first field name if it is not handled. Both are properties of CSV rather than of any converter.

When you want something else

For files too large to hold in memory, or for anything scripted, csvkit is purpose-built: csvjson --stream data.csv converts incrementally, csvlook lets you see what you have first, and csvclean fixes the ragged rows that break everything downstream. Miller — mlr --icsv --ojson cat data.csv — does the same at considerable speed and handles line-delimited JSON, which is what most data pipelines actually want.

When the types matter more than the convenience, do the conversion in code with the types declared rather than inferred. Python's pandas with an explicit dtype mapping, or a small script with a schema, means a postcode stays a postcode and an identifier keeps every digit — which no automatic inference can guarantee across a file it has not seen all of.