Convert CSV to JSON

How to use it#

1. Paste the CSV, or a few rows of it to check the shape first.
2. Confirm the delimiter and whether the first row holds field names.
3. Copy the JSON or download it as a file.

Why splitting on commas is not enough#

The reason CSV parsing is harder than it looks is that a comma inside a quoted field is data, not a separator. A row reading "Smith, John",42 has two fields, and any parser that splits on commas produces three. The same applies to line breaks: a quoted field can contain one, so a CSV file does not have one record per line.

Quotes themselves are escaped by doubling them, so a field containing the word "hi" in quotation marks is written as "She said ""hi""". This converter follows RFC 4180 and reads all of these correctly, which matters mostly for data that came out of a spreadsheet, where free-text columns are full of commas and quotes.

If your data is fully quoted and none of this applies, the result is identical either way. It is the one awkward row in ten thousand that makes the difference, and that row is usually somebody’s address.

What the type conversion will and will not change#

With conversion on, a field reading 42 becomes the JSON number 42 rather than the string "42", and true and false become booleans. Empty fields become null, because CSV has no way to distinguish an empty string from a missing value and null is the more useful of the two readings.

Two cases are deliberately left as strings. A value with a leading zero, such as 007 or a postcode like 01234, is almost always a code rather than a quantity, and converting it would silently delete the zero. So is an integer longer than about sixteen digits, because JSON numbers cannot hold it exactly and converting would quietly change the last few digits. Long account numbers and identifiers fall into this second category.

If you want everything left alone, turn the conversion off and every value comes through as a string.

Ragged rows and repeated headers#

Real exports are not always rectangular. A row with fewer fields than the header gets the missing ones filled with null rather than left out, so every object in the array has the same keys and nothing downstream has to check whether a property exists. Extra fields beyond the header are dropped, since there is no name to give them.

Duplicate column names get a number appended, so a header of id,id,id produces id, id2 and id3. A blank header cell is named after its position, such as column2. Both of these are more useful than the alternative, which is an object where later columns quietly overwrite earlier ones.