Convert CSV to JSON

What a CSV file really is

CSV looks simple, but real files vary. A value that contains the delimiter, a quote mark or a line break is wrapped in double quotes, and a quote mark inside it is written twice, as in "She said ""hi""". In countries that write decimals with a comma, Excel uses a semicolon as the delimiter, so one person's CSV is another person's single column. Files saved by Excel as "CSV (Comma delimited)" are not UTF-8 (only its "CSV UTF-8" option is).

The converter detects the delimiter by trying a comma, tab, semicolon and pipe on the first rows and choosing the one that splits them into the same number of fields most consistently, ignoring delimiters inside quotes. It reads the encoding from the bytes, so accents survive. Rows with fewer values than the header are padded with empty cells, extra values go into extra columns called column5, column6 and so on, and a repeated header such as name, name becomes name, name_2. Rows with no content are skipped, except in a file with a single column, where a value written as "" is a row with an empty value.

A quote mark inside a quoted value has to be written twice. If one is not, as in "x"y,z, the value does not end there but runs on to the next closing quote, possibly over several lines, and a note names the line and column where it went wrong.

How numbers, true and false, and empty cells are typed

CSV has no types, so the converter decides one column at a time. A column becomes numbers only when every filled cell in it is a clean number. A column of true and false (in any capitalisation) becomes booleans. Empty cells become null. Anything else stays text.

• 42 and -3.5 become numbers, and 3.50 is written as 3.5.
• 007 and 02134 stay text, so IDs and ZIP codes keep their zeros.
• 12345678901234567890 stays text, because a JSON number would be rounded after about 15 digits.
• 1,5 and 1.234,50 stay text, because the same text means different numbers in different countries.
• A column with 10, 20 and n/a stays text in every row, so a column never mixes numbers and words.

Choose Keep as text if you need every cell exactly as written.

Getting nested JSON out of a flat CSV

Name the columns by path and the converter builds the structure. The header name,address.city,address.zip,tags.0,tags.1 gives {"name": ..., "address": {"city": ..., "zip": ...}, "tags": [..., ...]} for each row. Empty items at the end of a list are dropped, and with Leave out empty cells under More options, empty cells do not create a key at all.

If you do not want nesting, turn off Rebuild nested values from column names and the headers stay as flat keys. A header is only taken as a path when every part between the dots is a real name, so headers such as No. of items, Dr. Smith or Amt. (a space next to the dot, or a dot at the end) stay plain keys. If the dot is part of other column names, such as v1.2, pick a different separator under More options. Choose A list to get one JSON list per row, with the column names as the first list.