Established onRFC 4180: embedded breaks legal in quoted fieldsExcel 365: doubling convention, reads and writes breaksGoogle Sheets, web: same convention
A field containing the separator has to be marked somehow, or the reader will split it. The convention is quotation marks, and the rules around them are where delimited files stop being simple.
The three things that force quoting
A field containing the separator. A field containing a quotation mark. And a field containing a line break, which is legal and is the source of most of the trouble in this entry.
The escape that has two incompatible forms
A quotation mark inside a quoted field is written twice in the common convention and preceded by a backslash in another, and both are widespread. Database export tools frequently use the second. A reader expecting one and given the other produces fields that are wrong from the first quote onwards, usually without complaint.
The line break inside a field
An address or a comment containing a line break is written as a quoted field spanning two lines of the file. The file is correct.
Every tool that treats a line as a record is now wrong about it. Counting lines does not count records. Reading the first ten lines does not read ten records. Splitting the file into chunks by line boundary cuts records in half. Searching for a pattern line by line misses matches that span the break.
This is not a defect in those tools. It is an assumption that the format does not support and that holds for the great majority of files, which is why it survives.
The distinction the format cannot make
Two adjacent separators mean an empty field. Two adjacent separators with a pair of quotation marks between them also mean an empty field. There is no third form, so a value that is an empty string and a value that is absent are written identically.
Any system distinguishing between the two loses the distinction on export and cannot recover it on import. Where that distinction matters — and in a financial or clinical context it usually does — the file needs an agreed marker for absence, which the format will not enforce and both sides must honour.
What a spreadsheet does with all this
Excel writes embedded line breaks as quoted fields spanning lines, and reads them back correctly. It uses the doubling convention for quotes. It writes no marker for absence, and an empty cell and a cell containing an empty string are indistinguishable in the output.
What to do when producing
Remove line breaks from fields at the source, replacing them with a space or a marker, unless a consumer specifically needs them. This costs almost nothing and removes the entire class.
Prefer tab as a separator, which appears in text far less often than a comma and therefore reduces how much quoting is needed at all.
Agree a marker for absence, in writing, with whoever reads the file.
What to do when consuming
Use a reader that understands quoting rather than splitting on the separator, which every spreadsheet, every database import tool and every standard library does, and which hand-written scripts frequently do not.
And do not count lines. Count records after parsing, which the entry on row limits in the first part is also about, for a different reason.
How to test a reader
Make a file with four records: one ordinary, one whose field contains the separator, one whose field contains a quotation mark, and one whose field contains a line break. Any reader that handles those four handles almost everything.
It takes two minutes to build and it is the only way to find out what a particular tool actually does, since the documentation of most of them does not say.
What we cannot verify
The quoting conventions and the legality of embedded line breaks are in the published description of the format. Excel's behaviour on writing and reading them was established by testing on a current build. The prevalence of backslash escaping in database export tools is our observation from practice rather than a measured figure.
The first field, and the invisible characters in it
Two things routinely sit at the very start of a file and belong to nobody: the encoding marker described in the previous entry, and stray whitespace left by whatever produced it.
Both attach themselves to the first column's header, so a query matching on the header text fails to find a column that is visibly present. The symptom is a column that exists on screen and cannot be referred to, and the cause is three bytes nothing displays.
In short
- Quoting is forced by the separator, a quotation mark, or a line break in a field.
- Escaping a quote by doubling and by backslash are both widespread and incompatible.
- A line break inside a field is legal, so counting lines does not count records.
- An empty string and an absent value are written identically and cannot be told apart.
- Remove line breaks at the source and prefer tab, which needs less quoting.
- Four test records covering the awkward cases reveal what any reader actually does.