Converting CSV to JSON Correctly: Quoted Fields, Delimiter Detection and Type Inference

CSV looks like the simplest file format there is: values separated by commas, one record per line. That impression lasts until the first customer name with a comma in it. DevKit, my free toolbox for developers, has a CSV to JSON converter that runs entirely in the browser, so the data you paste never leaves your machine. Writing it meant working through every way a "simple" CSV file can go wrong.

This article walks through the converter's code in the order it runs: parsing quoted fields, guessing the delimiter, turning text into numbers and booleans, and building JSON objects from the header row. Every example output below comes from running the real functions from DevKit.

Why split(',') is not a CSV parser

The first version most of us write splits the text into lines, then splits each line on commas. Here is what that does to a two-line file where one name contains a comma:

name,city
"Mehta, Raj",Pune

// text.split('\n').map(line => line.split(','))
[["name","city"], ["\"Mehta", " Raj\"", "Pune"]]

The second row now has three columns instead of two, the quotes are part of the data, and every column after the name is shifted one place to the right. The rules CSV actually follows are written down in RFC 4180, and they need a parser that reads one character at a time.

The parser: a loop with one flag

DevKit's parseCsv walks through the text once and tracks a single piece of state: whether it is currently inside a quoted field. Outside quotes, a delimiter ends the field and a newline ends the row. Inside quotes, both are just ordinary characters. Here is the heart of it:

if (inQuotes) {
  if (ch === '"') {
    if (text[i + 1] === '"') {   // "" is an escaped quote
      field += '"';
      i += 2;
      continue;
    }
    inQuotes = false;            // closing quote
  } else {
    field += ch;                 // commas and newlines are data here
  }
  i++;
  continue;
}
if (ch === '"' && field === '') inQuotes = true;
else if (ch === delimiter) { row.push(field); field = ''; }
else if (ch === '\n' || ch === '\r') { /* end the row */ }
else field += ch;

That short loop covers all of the cases that break a naive split:

Three details are worth calling out.

• Line endings. Files exported from Excel on Windows end lines with \r\n. The parser treats \r as a line break and skips a \n that immediately follows it, so one CRLF produces one row break, not a row break plus an empty row.
• Quotes only open at the start of a field. The check field === '' means a quote in the middle of a value, like the inch mark in 5" screen, is kept as an ordinary character. Strictly, RFC 4180 says such a field should have been quoted, but real exports often get this wrong, and keeping the character is far more useful than refusing the whole file.
• A missing closing quote is an error. If the text ends while still inside quotes, the parser throws Unterminated quoted field. This is deliberate. The alternative is silently swallowing every row after the stray quote into one giant field, which is much harder to notice than an error message.

Guessing the delimiter

"Comma-separated" is a loose description. Spreadsheets in countries that write decimals as 1,5 export with semicolons, database tools often produce tab-separated output, and some systems use pipes. DevKit's delimiter dropdown defaults to Auto, which runs detectDelimiter:

const firstLine = text.split(/\r?\n/, 1)[0] ?? '';
const candidates = [',', ';', '\t', '|'];
// pick the candidate that appears most often in the first line

The first line is usually the header, and header names rarely contain punctuation, so counting there works well in practice:

• name;amount;city → ;
• a⇥b⇥c (tabs) → tab
• single (one column, nothing to count) → falls back to ,

It is a heuristic, and it has a known blind spot: it counts characters without understanding quotes. A first line of name|"Mehta, Raj, Sr"|city has two pipes and two commas inside the quoted value, and because comma is checked first and wins the tie, the result is a comma. That is why Auto is a default rather than the only option. When the guess is wrong, picking the delimiter by hand fixes it immediately. Making detection quote-aware would be more code for a case that rarely appears in a header row.

Type inference: when is "42" a number?

Every value in a CSV file is text. JSON has numbers, booleans and null, and most people converting CSV want "42" to become 42. With Infer types switched on, each value goes through inferValue. The interesting part is what it refuses to convert:

The rule for numbers is a regular expression that matches the number syntax from the JSON spec:

/^-?(0|[1-9]\d*)(\.\d+)?([eE][+-]?\d+)?$/

The (0|[1-9]\d*) part is what protects leading zeros. This matters more than it looks. Indian mobile numbers written with the trunk prefix (09876543210), PIN codes, employee IDs and account numbers all start with zeros, and converting them to numbers silently deletes the zero. You can't get it back from the JSON afterwards. JavaScript's own Number('09876543210') would happily return 9876543210, which is exactly why the converter checks the format before calling Number.

The second guard handles large integers. A JavaScript number is a 64-bit float, which represents every integer exactly only up to 2⁵³ − 1 (9,007,199,254,740,991). Above that, Number('9007199254740993') returns 9007199254740992: a different value, with no error. So the code only converts an integer if Number.isSafeInteger is true, and keeps longer digit strings, such as database IDs, as text. Decimals and exponents skip this check, because they are approximate by nature.

From rows to objects

With Header row on, the first row supplies the keys and every following row becomes an object. Real files are rarely perfectly rectangular, so csvToJson handles the gaps instead of dropping data:

• Blank header cells get a positional name. The header id,,x gives keys id, column2, x.
• Rows longer than the header keep their extra values under column3, column4 and so on, instead of losing them.
• Rows shorter than the header fill the missing keys with empty strings, so every object has the same shape.

A worked example

Here is a small contacts export with a quoted name, a phone number with a leading zero, a missing value and a short final row:

id,name,phone,active
1,Asha,09876543210,true
2,"Rao, K",,false
3,Vik

DevKit converts it to:

[
  { "id": 1, "name": "Asha",   "phone": "09876543210", "active": true },
  { "id": 2, "name": "Rao, K", "phone": "",            "active": false },
  { "id": 3, "name": "Vik",    "phone": "",            "active": "" }
]

The IDs became numbers, the booleans became booleans, the comma stayed inside "Rao, K", the phone number kept its zero, and the short row still has all four keys. With Header row switched off, the same file comes out as an array of arrays instead, which is handy when the first line is data.

Why it all runs in the browser

CSV files are often exports of customers, invoices or employees: exactly the data you shouldn't paste into a random website that uploads it to a server. Every DevKit tool, including this one, is plain TypeScript running in your browser tab. The parse happens on your machine, and Download builds the data.json file locally from a Blob. There's no server round trip to wait for either, so the output updates as you type.

Lessons that apply beyond CSV

• Losing data quietly is worse than failing loudly. An unterminated quote throws an error, and a value that might lose information (a leading zero, a 17-digit ID) stays as text.
• Be lenient with input you don't control. Stray quotes and ragged rows are common in real exports, and handling them sensibly beats rejecting the file.
• A heuristic needs an override. Delimiter detection is right most of the time, and the dropdown covers the rest.
• Follow the spec you're converting to. Using JSON's own number grammar decides most of the edge cases for you.

DevKit is free and needs no sign-up. Along with CSV to JSON it has a JSON formatter, SQL formatter, regex tester, Base64 and URL encoders, a UUID v4/v7 generator, a Unix timestamp converter and a hash generator.