Commas inside values: quoting, escaping, and the four ways it breaks

A CSV separates fields with a comma. Data contains commas. That contradiction is the oldest problem in the format, and quoting is the answer. When quoting is done correctly everything works; when it is done sloppily you get a file that parses without error and has the wrong values in the wrong columns from row 4,000 onward.

The rules, in four lines

RFC 4180 is short and the parts you need are shorter. A field may be wrapped in double quotes. A field must be wrapped if it contains the delimiter, a double quote, or a line break. A double quote inside a quoted field is written as two double quotes. Whitespace outside the quotes is part of the field, which surprises people.

id,name,note,city
1,"Smith, John","he said ""fine""","Berlin, Germany"
2,Jane Doe,ok,Paris
3,"Lee, Ann","line one
line two",Tokyo

Four data rows in six physical lines. Row 3's note contains a newline, which is legal because the value is quoted. Row 1's note contains escaped quotes. Row 2 needs no quoting at all and correctly has none.

Everything after this is a way that writers get one of those four lines wrong.

Break 1: an unescaped quote inside a quoted field

Cause. A writer wrapped the value in quotes but did not double the quotes inside it. What the parser sees is a field that ends early, followed by garbage:

id,note
1,"he said "fine" and left"

The parser reads he said as the complete field, then finds text where it expected a comma. Some parsers throw. Some quietly resynchronize at the next comma, which shifts every subsequent value one column left. That second behavior is the dangerous one, because there is no error.

Symptom to look for. A row where a value has landed in the wrong column, often a date sitting in an amount field or a fragment of a sentence in a status column. It affects one row, or all rows from one point onward, depending on whether the quote count rebalances.

Fix. Find the offending row and repair it at the source. A validator that compares each row's field count against the header finds it immediately, because a broken quote almost always changes the field count.

Fix it: list every row whose field count is off →

Break 2: backslash escaping

Cause. Someone wrote the file with a JSON mindset:

id,note
1,"he said \"fine\" and left"

CSV has no backslash escape. A standards-following parser sees the backslash as an ordinary character and the quote as a real closing quote, so this is just break 1 with an extra backslash in the output. It is common in files written by hand-rolled exporters, and in files produced by code that built the CSV with string concatenation instead of a library.

Fix. Rewrite the file with a real CSV writer. If you cannot, and the backslash convention is consistent throughout, some parsers accept an escape character setting. DuckDB's reader takes an escape option; set it to a backslash and the file parses. Treat that as a rescue, not a plan. The next export from that system will have the same problem.

Fix it: load the file with an explicit escape character →

Break 3: smart quotes

Cause. Someone edited the file in a word processor, or the data itself came from one, and the straight quote " became a curly quote. Those are different characters: U+201C and U+201D versus U+0022. A CSV parser only recognizes the straight one.

The effect is the reverse of break 1. Fields that should be quoted are not, as far as the parser is concerned, so a value containing a comma splits into two columns. And because curly quotes look almost identical on screen, you will stare at the row for a while before you see it.

Fix. Replace the curly quotes with straight ones, or, if the curly quotes are legitimate content rather than field delimiters, leave them alone and make sure the fields around them are quoted properly. Then stop editing CSVs in word processors. A plain text editor cannot introduce this problem.

Fix it: find and replace the curly quote characters →

Break 4: a space after the comma

Cause. This one is subtle and produces a file that parses cleanly and is still wrong.

id, name, city
1, "Smith, John", Berlin

Strictly, the second field is "Smith including the leading space, because the quote is not the first character of the field and therefore is not a quote at all: it is data. Parsers differ wildly here. Some skip the space, some do not. The file behaves differently depending on which library reads it, which is the worst possible property for a data format.

Fix. Do not put spaces after delimiters when writing. When reading, most parsers offer a skip-initial-space option; DuckDB has ignore_errors and related tolerances, and Python's csv module has skipinitialspace=True. Also trim your string columns after loading, because the same file usually has stray whitespace elsewhere.

SELECT TRIM(name) AS name, TRIM(city) AS city
FROM data;

Fix it: trim whitespace across every text column at once →

Check what is really in the file

Quoting problems have one reliable signature: rows whose field count differs from the header's. Almost every break above changes the number of fields on the affected row, so one check finds all of them.

From a terminal, on a comma-delimited file with no embedded newlines, this gives you a quick histogram of field counts:

awk -F, '{print NF}' yourfile.csv | sort -n | uniq -c

If one count dominates and a handful of rows differ, those rows are your suspects. Be aware that this naive check counts commas inside quoted fields too, so a file with legitimate quoted commas will show noise. That is exactly why a real validator, which parses rather than counts, is worth using instead.

A last piece of advice that saves more time than any of the fixes above: never write a CSV by concatenating strings. Every language has a CSV writer that handles quoting correctly, and every hand-rolled writer eventually meets a customer named O'Brien, Inc. from "Springfield".

Fix it: run the validator and get the line numbers →