Clean Messy CSV and Convert It to JSON

There is no authoritative CSV specification. RFC 4180 is an informational memo published in 2005 that describes common practice; it is not a standard anyone is obliged to follow, and most tools do not follow it completely.

What actually varies between files that all call themselves CSV:

• The delimiter. Comma, semicolon or tab. European locales using a comma as the decimal separator routinely export semicolon-delimited files, which is why a spreadsheet exported in Germany opens as a single column elsewhere.
• The quote character, and how a quote inside a quoted field is escaped — doubled ("") per RFC 4180, or backslash-escaped in files produced by database tools.
• Line endings. \r\n, \n, or a bare \r from very old Mac software.
• The encoding. UTF-8, UTF-8 with a byte-order mark, Windows-1252, or UTF-16 from Excel's "Unicode Text" export.
• Whether there is a header row at all.

A parser that assumes any of these is a parser that will silently corrupt somebody's data. The failures are quiet: a misdetected delimiter produces one column, a misdetected encoding produces mojibake, and a naive split on commas destroys every quoted field containing a comma — which in practice means every address and every company name with a suffix.

The single most common CSV bug is parsing with split. It fails on the first correctly-quoted field:

id,name,notes
1,"Acme, Inc.","He said ""yes"" on Tuesday"

Splitting that second row on commas yields five fields instead of three, and shifts every subsequent column. The row is not rejected — it is silently wrong, which is worse.

Correct CSV parsing requires a character-by-character state machine that tracks whether it is currently inside a quoted field. The rules are few:

1. Outside quotes, a delimiter ends the field and a newline ends the record.
2. A quote at the start of a field enters quoted mode.
3. Inside quotes, delimiters and newlines are literal data, not separators.
4. Inside quotes, two consecutive quotes mean one literal quote.
5. A quote followed by anything other than a quote ends quoted mode.

Rule 3 is the one that surprises people: a single CSV record can span multiple lines when a quoted field contains a newline. Any code that begins by splitting the file into lines has already failed on such a file, before parsing starts.

This is why reaching for a tested parser matters more here than in most formats. The rules are simple to state and easy to get subtly wrong, and every mistake produces plausible-looking output rather than an error.

Before a single field can be read, two things must be established from evidence rather than assumption.

Encoding. Check for a byte-order mark first: EF BB BF is UTF-8, FF FE is UTF-16LE, FE FF is UTF-16BE. If a BOM is present it settles the question. If not, attempt a strict UTF-8 decode and only fall back when it fails:

try {
  text = new TextDecoder('utf-8', { fatal: true }).decode(bytes);
} catch {
  text = new TextDecoder('windows-1252').decode(bytes);
}

The fatal: true flag is essential. Without it, invalid UTF-8 decodes to replacement characters silently, and a Windows-1252 file full of accented names becomes a file full of � with no error raised anywhere.

If a BOM is found, strip it. A leading BOM left in place attaches itself to the first header name, so id becomes \ufeffid and every lookup by that column name fails for reasons invisible in a debugger.

Delimiter. The reliable heuristic is consistency rather than frequency. For each candidate — comma, semicolon, tab, pipe — parse the first several rows and measure how consistent the resulting field count is. The correct delimiter yields the same count on every row; an incorrect one yields a varying count. Choosing by raw frequency picks the comma inside "Acme, Inc." over the real semicolon delimiter.

Converting CSV to JSON means deciding whether "42" becomes 42 or stays "42". Both answers are wrong in some cases, which is why this step needs a stated policy rather than a default.

The values that must not be coerced to numbers:

• Leading-zero identifiers. Postcodes, PIN codes, account numbers and part numbers. 007 becoming 7 destroys the value. India's PIN codes and US ZIP codes both contain them.
• Long numeric identifiers. JavaScript numbers are IEEE-754 doubles with 53 bits of integer precision. An 18-digit order id or a Twitter-style snowflake id loses its final digits — silently, with no error. A credit card number similarly.
• Values with a leading `+`, such as phone numbers.

The values that usually should be coerced: quantities, prices, measurements — anything you intend to do arithmetic on.

Empty fields need a policy too. An empty CSV cell can reasonably become null, "", or be omitted from the object entirely. These are meaningfully different in a downstream JSON consumer, and the choice should be explicit rather than accidental.

The safe default for a general-purpose converter is no coercion: keep everything as strings and let the consumer decide. When a tool does infer types, it should say so and let you turn it off, because the failure mode of wrong inference is silent data loss rather than a visible error.

Most real CSV files need work before the conversion is meaningful.

Duplicate and empty header names. JSON object keys must be unique, so two columns named Name will collide and one will overwrite the other. Empty header cells produce a key of "". Both need resolving — by suffixing duplicates and naming blanks — before any row is converted.

Whitespace in headers. " Email " and "Email" are different keys. Trimming header names is almost always correct; trimming values is a judgement call, since trailing spaces are occasionally meaningful.

Ragged rows. A row with fewer fields than the header is usually truncation; a row with more is usually an unescaped delimiter. Neither should be silently padded or dropped. The right behaviour is to surface the row number so a human can look at it.

Trailing blank lines. A file ending in a newline produces one final empty record in naive parsers, which then appears in the JSON as an object with empty values.

The OpenTools CSV to JSON converter performs the parse in the browser tab, which matters for this format specifically: CSV exports are overwhelmingly customer lists, order histories, payroll extracts and mailing lists. Those are exactly the files that should not be uploaded to a third party to be reformatted, and the conversion requires nothing a server could add.