CSV to JSON: the three cases that break every naive parser

A CSV file is not comma-separated. It is comma-separated, with a quoting ruleset that almost every hand-written parser ignores. Three inputs break the naive version, and all three fail the same way: your columns silently shift by one.

Case 1 — a comma inside quotes

sku,name,price
00127,Widget, small,45.20

Naive line.split(',') gives you four fields for three columns. The price lands in a fourth column you do not have, and 45.20 disappears.

The rule: a field may be wrapped in double quotes, and inside those quotes a comma is literal data.

function parseLine(line) {
  const out = [];
  let cur = "";
  let inQuotes = false;
  for (let i = 0; i < line.length; i++) {
    const ch = line[i];
    if (inQuotes) {
      if (ch === '"') {
        if (line[i + 1] === '"') { cur += '"'; i++; }   // escaped quote
        else inQuotes = false;
      } else cur += ch;
    } else if (ch === '"') {
      inQuotes = true;
    } else if (ch === ",") {
      out.push(cur); cur = "";
    } else cur += ch;
  }
  out.push(cur);
  return out;
}

Case 2 — doubled quotes are an escape, not a close

sku,title
00127,"9"" x 12"" Poster"

That single field is 9" x 12" Poster. If you treat the second " as "closing quote", the rest becomes garbage and — worse — the parser never re-enters quote mode, so every following comma splits.

The tell is in the bytes: "" inside a quoted field is always one literal ".

Case 3 — the BOM nobody mentions

Excel on Windows writes UTF-8 files with a byte-order mark: EF BB BF before the first character.

EF BB BF 73 6B 75 ...   →   "sku"

Your first header becomes \uFEFFsku, so row["sku"] is undefined for every row. Nothing throws. You just get a JSON file full of null.

Two lines fix it:

function stripBom(s) {
  return s.charCodeAt(0) === 0xFEFF ? s.slice(1) : s;
}

Case 4 — CRLF, because it is always there

split("\n") leaves a trailing \r on every line, so your last column is "45.20\r". parseFloat handles it, JSON.stringify gives you "45.20\r", and your downstream comparison fails.

Normalise first: text.replace(/\r\n?/g, "\n").

The order that actually works

1. Strip the BOM.
2. Normalise line endings.
3. Split into logical records — respecting quotes across newlines (a quoted field may contain \n).
4. Parse each record with the quote state machine.
5. Only now map the first row to headers.

Step 3 is the one people skip. A quoted field containing a newline is legal CSV, and it is how every product description with a bullet list breaks.

What I checked

Ten assertions on a fixture built from real supplier exports: quoted commas, doubled quotes, BOM header, CRLF, an embedded newline in a description, and a row with trailing empty cells. All ten fail on the naive parser and pass on the one above.

The limitation worth stating: this is not a full RFC 4180 implementation. It does not handle a mix of , and ; delimiters (which European Excel uses for decimal commas), and it does not attempt to sniff the delimiter. If your files come from one tool, hard-code the delimiter; if they come from users, sniff it on the first line before parsing.

If you just want to convert a file right now without writing this: IIX Lab's CSV to JSON converter runs the same algorithm in your browser and never uploads the file. And if you are fighting the next problem after conversion — duplicate rows and leading zeros eating your SKUs — that is written up in the dedupe notes.