CSV to JSON: the five things that go wrong

Commas inside fields

This is the first thing to break and the reason naive parsers cannot be trusted.

CSV allows a field to contain the delimiter as long as the field is quoted. So this row has three fields, not four:

1,"Smith, John",London

A parser that splits on commas produces four values and silently shifts every column after it. Nothing errors — you just get an object where the city is John" and the data is quietly wrong.

A quote inside a quoted field is escaped by doubling it, so "She said ""hello""" is one field reading She said "hello". Any parser that does not implement that rule will mangle quoted speech, inch marks and anything with an apostrophe convention that uses double quotes.

Line breaks inside fields

A quoted CSV field can contain actual newlines. A multi-line address in one cell is completely legal:

1,"221B Baker Street
London
NW1 6XE",UK

This defeats the other common shortcut — reading the file line by line. One record now spans three lines, and a line-based reader produces three broken records from it.

This is why CSV has to be parsed character by character with a state machine tracking whether you are inside quotes. It is not a format you can split your way through, however much it looks like one.

Numbers that must not become numbers

JSON distinguishes "01234" from 1234. CSV does not. A converter that helpfully detects numeric columns will destroy several kinds of data:

Postcodes, phone numbers, product SKUs, account numbers, ISBNs and anything with a leading zero all need to stay strings. This is the same failure that makes spreadsheets turn gene names into dates — automatic type detection is a guess, and it guesses wrong on identifiers.

Ragged rows and missing headers

Real CSV files are rarely rectangular. Rows come up short, come up long, carry trailing empty columns from a spreadsheet export, or repeat the header partway through because two exports were concatenated.

A converter has to decide what to do, and the right answer depends on your data:

• Short rows — fill missing keys with null, or omit them. Omitting is smaller; filling is easier to consume, because every object then has the same shape.
• Long rows — usually a quoting bug upstream rather than real data. Worth surfacing as an error rather than silently dropping the extras.
• Duplicate headers — two columns both called name cannot both be a key. One will overwrite the other.
• Empty trailing columns — a spreadsheet artifact. Strip them before converting or every object carries a stray empty field.

If the file is messy enough that rows repeat or blank lines litter it, clean it first — Duplicate Line Remover and Text Cleaner handle that faster than fixing the JSON afterwards.

Encoding, BOMs and delimiters that are not commas

Three smaller traps that account for most of the remaining failures.

Encoding. A file saved as Windows-1252 and read as UTF-8 turns every accented character into mojibake. Excel on Windows still exports in the system codepage unless you explicitly choose UTF-8.

The byte order mark. A UTF-8 BOM at the start of the file becomes part of the first header name, so your first key is invisibly id rather than id and every lookup for id fails. This one costs people hours because the two strings look identical in every log.

The delimiter. In locales where the comma is the decimal separator, "CSV" files are semicolon-delimited. Tab-separated files are also routinely named .csv.

A converter worth using detects the delimiter, strips the BOM, and tells you which encoding it assumed. The CSV to JSON tool does the parse locally, which matters when the CSV is a customer export — that is exactly the file you should not be pasting into a random web form.

Frequently asked questions