The CSV Edge Cases That Break Naive CSV-to-JSON Converters

CSV looks trivial — split each line on commas — right up until a real-world file breaks that assumption. There's no single enforced CSV standard (RFC 4180 describes common practice, but plenty of exporters don't fully follow it), which is exactly why "just split on commas" parsers fail so often in practice.

1. Commas inside quoted fields

name,bio
"Smith, John","Engineer, based in Seoul"

A field itself can legitimately contain a comma, as long as the whole field is wrapped in double quotes. A naive split(",") sees four fields on that second line instead of two, silently shifting every column after the first quoted field. A correct parser has to track whether it's currently inside a quoted field and ignore commas until the closing quote.

2. Embedded newlines

name,notes
Alice,"Line one
Line two"

A quoted field can contain a literal newline, meaning a single CSV "row" can legitimately span multiple physical lines in the file. A parser that reads line-by-line and treats each line as one record will split this single record into two, corrupting both. Correct parsing has to be done at the character/token level, not the line level.

3. Escaped quotes

"She said ""hello"" to me"

A literal double-quote character inside a quoted field is represented by doubling it (""), not by backslash-escaping it (\") as most programming languages do for strings. A parser written with backslash-escaping assumptions (a very natural mistake if you're used to JSON or most languages' string literals) will misparse any field containing a quotation mark.

4. Inconsistent column counts

Real-world exports sometimes have rows with fewer or more fields than the header — a trailing comma got dropped, or an export job changed mid-file. A strict converter has to decide: pad short rows with empty/null values, reject the row, or throw an error — there's no universally "correct" behavior, which is why different tools handle the same malformed file differently.

5. Encoding and the BOM

Files exported from different sources use different encodings — from Excel on Windows, you'll often get files with a UTF-8 byte-order-mark (BOM) at the very start, or in older cases, a legacy encoding like Windows-1252 instead of UTF-8. A parser that doesn't detect and strip the BOM will see it as part of the first column's header name — producing a header that looks like name when printed but doesn't === the string "name" in code, because it's actually "name". This is a classic source of "the column exists, but my code says it doesn't" bugs.

6. Type ambiguity going into JSON

CSV has no type system — every cell is just text. Converting "042" to JSON: is it the number 42, the string "042" (a zip code, an ID with a meaningful leading zero), or a date-like string? A converter that aggressively "helpfully" coerces every numeric-looking string to a JSON number will silently corrupt any leading-zero identifier. The safer default is to only convert values that are unambiguously numeric with no leading zero, and leave the rest as strings.

Why this matters even for "simple" files

Most CSV files never hit these cases — until an address field with a comma, a multi-line note, or an Excel export with a BOM shows up, usually from a different source than whatever generated the last hundred files that worked fine. A converter that handles quoting, embedded newlines, and encoding correctly the first time avoids a debugging session that otherwise looks like "the data is corrupted" when it's actually the parser.

Spellkit's CSV to JSON tool handles quoted fields, embedded newlines, and escaped quotes per RFC 4180, entirely in your browser.