Answers · Where the data lives

Why does my CSV file fall apart when one of the fields has a comma in it?

In shortBecause the same comma both separates fields and sits inside the value, so a reader splitting on commas finds one field too many and every later column shifts.

Because the character that separates one field from the next is the same character that appears inside the value, and a plain text file holds nothing that says which is which. A reader that splits on every comma therefore finds one field too many, and every column to the right of the break slides along by one. A table is two dimensional and a line of text is one dimensional, so the separator is the only thing marking where a cell ends, and it has to be a character that cannot turn up in the data. A comma can.

FIG 1Three rows, one of which breaks the column count

plaintext

id,name,city,amount
1,Acme Ltd,Leeds,1200
2,"Smith, Jones and Partners",Hull,900
3,Dunn and Co,"Stoke-on-Trent",450

The second data row contains a comma inside the company name. The third row quotes a field that did not need quoting, which is legal and tells a reader nothing.

Take that second row on its own. Four fields were intended: an id, a company name, a city and an amount. Count what is actually in the line rather than what was meant. It reads 2, then a quoted name, then Hull, then 900, and it contains four commas. Four commas delimit five fields, so the flattening has produced one more cell than the header promised. Walk the split by hand: the first comma after 2 closes the id field. The second comma, the one inside the quotes, closes the name field at "Smith. The third closes the city field at Jones and Partners". Hull now sits where the amount belongs, and 900 falls off the end of the line altogether.

n_{\text{fields}} \;=\; n_{\text{separators}} + 1 \;=\; 4 + 1 \;=\; 5

The natural guess is that the parser is broken and a cleverer one would cope. It would not, because the information needed is not in the file to be recovered. A separated text file is readable by everything and defined by nothing. One decision, which character separates fields, is made by looking at the text rather than by being stated, and every other decision is left to whoever opens it. Whether fields may be quoted, which character set the bytes use, what type each column holds, how many rows to expect, whether the first line is a header: all of that is the reader's assumption. Change the reader and the same bytes become a different table. The fault is not in the splitting, it is in expecting a file that settles nothing to behave as though it settled something.

FIG 2What a separated text file leaves undecided

	decided by the file	decided by the reader
which character separate	1	0
whether fields may be qu	0	1
which character set the	0	1
what type each column is	0	1
how many rows there shou	0	1
whether the first line i	0	1

Coded 1 for yes, 0 for no. Only the first line is settled, and even that one by inspection, which is why readers guess between comma, semicolon and tab. This is also where a postal code with a leading zero becomes a number and loses the zero: the type was never stated, so the reader guessed numeric.

The damage rarely announces itself. A shifted row still parses, because the columns past the break still contain something plausible, so the file loads and a handful of records are quietly wrong. The count is worth doing rather than waving at. At two per cent of fifty thousand rows, $0.02 \times 50{,}000 = 1000$ records arrive wrong, and nothing in the load reported it. That ratio, not the loud crash, is what this format is known for.

What quoting settles

The convention that rescues the example is quoting: a field containing a separator is wrapped in double quotes, and a reader that honours quoting treats the wrapped text as a single value. It works, and it is only a convention. A quote inside a quoted field has to be doubled to stay distinguishable, which is a rule a good share of writers in the world never implement. Quoting a field that needs no quoting is also legal, so the presence of quotes tells a reader nothing it can rely on. And the file still does not say which character the separator is, so a comma, a semicolon and a tab all appear in files that look identical at a glance.

FIG 3

form	opens in a text editor	types survive	a comma in a field is safe
separated text, written by hand	yes	no	no
separated text, written by a writer that quotes	yes	no	mostly
one self-describing record per line	yes	yes	yes
a defined column format	no	yes	yes

When it stops mattering

There are two escapes from the ambiguity. The first is to make the format state what the file above leaves open: fixed types, a declared character set, stated quoting rules, a known row count. The cost is that the file no longer opens in a text editor and needs a library to read. The second, cheaper one is to give up the flat table and write one self-describing record per line, each carrying its own field names, so a comma inside a value is just a character inside a string. It is still text, still appendable, still greppable, at the price of repeating the field names on every line, which is nothing at a few million records and a real cost at a few billion.

None of this bites when one program writes the file and the same program reads it back, because the ambiguity is resolved by shared code rather than by the bytes. It bites at the boundary, when a file written by one tool is opened by another that never agreed on quoting, separator or types, and that boundary is where most tables actually travel.

Worth keeping

A comma inside a field shifts every column after it, and the row usually still parses, so the file loads with records quietly wrong rather than failing outright.
Quoting is a convention rather than a rule: it is not stated in the file, quoting an unnecessary field is legal, and an embedded quote must be doubled, which many writers never do.
For a table that crosses between systems, the fix is a format that settles types and quoting, not a cleverer split.

Where this goes next

This question is one step of Bytes, Text and File Formats, a course that works through its subject from the beginning. The page that teaches this part of it is Rows, Columns and the Separator Problem, and it reads the whole way through whether or not you have an account.

What an account changes is the practice: problems standing under every idea, marked with a reason rather than a tick, in a copy of the course that is yours.

Read that page The whole course

Why does my CSV file fall apart when one of the fields has a comma in it?

What quoting settles

When it stops mattering

Worth keeping

Where this goes next

Read alongside