Converting CSV to JSON Correctly: Quoting, Types and Encoding

line.split(',') works on the CSV you write in a test and fails on the CSV your users upload. Here is what real CSV contains.

Quoting is the whole problem#

id,name,notes
1,"Hopper, Grace","She said ""hello"""
2,Ada,"line one
line two"

Three features in four lines, all part of RFC 4180:

• A quoted field can contain the delimiter ("Hopper, Grace" is one field).
• A quote inside a quoted field is escaped by doubling it (""hello"").
• A quoted field can contain literal newlines, so one record is not one line.

That last point is why line-based processing fails: you cannot split the file on \n before you have parsed the quoting.

// Wrong for row 1: yields ['1', '"Hopper', ' Grace"', ...]
const fields = line.split(',');

Use a real parser. In JavaScript, PapaParse handles all of the above:

const result = Papa.parse(csv, { header: true, skipEmptyLines: 'greedy' });

Type coercion is a choice, not a default#

Converting "42" to 42 is often useful and sometimes destructive:

The safe default is to leave everything as a string and coerce specific columns deliberately. Blanket dynamicTyping is convenient for exploration and risky for production imports.

Delimiters are not always commas#

Excel uses a semicolon as the CSV delimiter in locales where the comma is the decimal separator — so a French or German export of "CSV" is semicolon-delimited. Tab-separated files are also common. Detect rather than assume, and let the user override.

Encoding: the BOM and Excel#

Two encoding issues account for most "the accents are broken" reports:

A UTF-8 BOM at the start of the file. Excel adds it. If you do not strip it, your first column name becomes id instead of id — which looks identical in a console and never matches a lookup.

const clean = text.replace(/^/, '');

Windows-1252 rather than UTF-8. Older Excel exports use the legacy code page, so é arrives as a different byte sequence. There is no reliable way to detect this from content alone; the practical answer is to ask, or to try UTF-8 with fatal: true and fall back.

Nested JSON in the other direction#

Going JSON → CSV requires flattening, because CSV is inherently flat:

{ "id": 1, "contact": { "email": "a@b.com" }, "tags": ["x", "y"] }

becomes

id,contact.email,tags
1,a@b.com,"x; y"

Dot notation for nested objects; a joined string for arrays of primitives. Exploding the array into rows would change the record count and break the one-object-per-row contract.

One more detail that matters: build the column list from the union of all keys across all records, not from the first record. Real API payloads are not uniform, and using the first object's keys silently drops columns.

Frequently asked questions#

What is the maximum CSV size I can convert in a browser?#

Practically tens of megabytes. Beyond that, stream it — PapaParse supports a streaming mode with a step callback so you never hold the whole file in memory.

Should the header row always be present?#

It should. Positional CSV is fragile: inserting a column breaks every consumer silently. If you must accept headerless files, require the caller to declare the columns.

How do I handle a field containing the quote character?#

Double it: She said ""hello"". Backslash escaping is not RFC 4180 and many parsers reject it.

Why does my CSV open wrong in Excel?#

Usually the delimiter/locale mismatch above, or missing CRLF line endings. Strict RFC 4180 specifies CRLF, and some older Excel builds require it.

Related reading#

• JSON Formatting Best Practices
• Convert either direction with CSV to JSON and JSON to CSV.

References#

• RFC 4180: Common Format and MIME Type for CSV Files
• PapaParse documentation