CSV quoting and escaping explained (RFC 4180)

CSV looks like the simplest format in the world until a field contains a comma. The rules that handle that case live in RFC 4180, the specification almost every CSV tool follows. Understanding CSV escaping is what separates a file that imports cleanly from one that silently shifts half its columns.

The three triggers for quoting

RFC 4180 says a field must be wrapped in double quotes whenever it contains one of three things: the delimiter itself (a comma), a double quote character or a line break. A plain value like Berlin needs no quoting. A value like HDMI, 2m must become "HDMI, 2m" so the comma inside it is read as text rather than the boundary between two columns. The same applies to a value with an embedded newline, which would otherwise look like the start of a new row.

Escaping a quote by doubling it

The detail that catches programmers out is how to put a literal double quote inside a field. RFC 4180 does not use a backslash. Instead it doubles the quote: two double quotes in a row inside a quoted field mean one literal double quote. So the value 27" curved is written "27"" curved". The outer pair opens and closes the field and the inner doubled pair collapses to a single quote when parsed. It feels odd at first, but it keeps the format free of any escape character that would itself need escaping.

Why a parser beats find-and-replace

This is the practical payoff. When people convert tab-separated data to CSV by swapping tabs for commas, they ignore quoting entirely, so every value that already contains a comma splits into extra columns and every embedded quote or newline derails the row. A correct converter walks the text character by character, tracking whether it is inside a quoted field, so embedded delimiters, doubled quotes and multi-line values are all handled. That is exactly what the TSV to CSV converter does, which is why it produces output a strict parser will accept rather than the off-by-one column errors a manual swap creates. The CSV vs TSV guide explains why the two delimiters behave so differently here.

Line endings and headers

Two more details round out a clean CSV. RFC 4180 specifies CRLF line endings between records, though most parsers accept a plain LF as well, so a good converter normalises both. And while the spec treats a header row as optional, in practice almost every consumer expects one, so keep your column names on the first line. When the rows are headed for a database rather than another spreadsheet, the CSV to SQL import tool turns them into INSERT statements once the quoting is correct.

Frequently asked questions

When does a CSV field need quotes?
Whenever it contains a comma, a double quote or a line break. Fields without any of those can be left bare.

How do you escape a double quote inside a CSV field?
You double it. Two double quotes in a row inside a quoted field represent one literal double quote, so 27" curved becomes "27"" curved".

Bottom line

CSV escaping comes down to three triggers for quoting and one rule for embedding quotes. Follow RFC 4180 and your files import everywhere; ignore it with a blind find-and-replace and you corrupt any value containing a comma. Let the TSV to CSV converter apply the rules for you and the format stops being a trap.