CSV Quoting Rules: Why Splitting on Commas Breaks Your Data

CSV looks like the simplest format in the world: values separated by commas, records separated by line breaks. That impression lasts exactly until the first value that contains a comma. A company called "Smith, Jones & Co" or an address like "12 Main St, Berlin" breaks every parser built on splitting text at commas, and the data shifts one column to the right from that point on. The fix is quoting, and the rules are small enough to learn in five minutes. This guide covers what the CSV standard actually requires, shows a naive parser corrupting a real row, and explains the dialect quirks you will meet in the wild. To quote or unquote fields correctly without hand-editing, use the CSV Escape tool.

The rules RFC 4180 actually sets

CSV never had a formal standard for its first few decades. RFC 4180, published in 2005, wrote down the common format that most tools had converged on, and it is the definition the IANA text/csv media type registration points to. Its core rules fit in a short list.

• Each record sits on its own line, delimited by a CRLF line break (carriage return plus line feed). The last record in the file may or may not end with one.
• A field that contains a comma, a double quote, or a line break should be enclosed in double quotes.
• Inside a quoted field, a literal double quote is escaped by doubling it: "" stands for one quote character. There is no backslash escaping in CSV.
• Spaces are part of the field and should not be ignored. A parser that trims whitespace around commas is deviating from the RFC.
• Every record should have the same number of fields, and an optional header line may name the columns.

Why split(",") corrupts rows

The most common CSV bug in application code is treating a row as a string to split at every comma. That works until a quoted field contains the delimiter. Here is one legitimate, RFC-compliant record and what a naive split makes of it.

The row (3 fields):
42,"Smith, Jones & Co","He said ""hello"" twice"

Correct parse (a real CSV parser):
  field 1:  42
  field 2:  Smith, Jones & Co
  field 3:  He said "hello" twice

Naive parse with row.split(",") (5 fields, all wrong after the first):
  field 1:  42
  field 2:  "Smith
  field 3:   Jones & Co"
  field 4:  "He said ""hello"" twice"
  field 5:  (missing, columns are now misaligned)

The split produces five fragments instead of three fields, quote characters leak into the data, and every column after the company name lands under the wrong header. In a file with thousands of rows the damage is silent: most rows parse fine, and the handful containing commas shift sideways. A real CSV parser is a small state machine that tracks whether it is inside quotes, which is exactly the state that split(",") throws away.

Line breaks inside fields

Quoting does more than protect commas. RFC 4180 explicitly allows a quoted field to contain line breaks, which means one logical record can span several physical lines of the file. Splitting the file into rows with a line-based read has the same failure mode as splitting rows at commas: a multi-line comment or address gets chopped into two broken records.

id,comment
7,"First line of feedback
second line of the same field"
8,"Short note"

This file has 2 data records, not 3. The line break after
"feedback" is data, because it sits inside an open quote.

This is why grep, wc -l, and line-oriented scripts give misleading answers on CSV files with free-text columns. If you need to inspect or fix records that span lines, a structure-aware CSV editor shows you the real grid instead of the raw lines.

When quoting is optional and when it is required

Any field may be quoted. Quoting only becomes mandatory when the field contains one of the three characters that would otherwise be misread. This table sums up the decision.

Quoting everything is always valid and is the safest choice for generated files. Quoting nothing is only valid when no field contains a comma, quote, or newline, which you cannot promise about user-entered data. If you are producing CSV by concatenating strings, apply the rules mechanically: wrap the field in double quotes and double any quote inside it. The CSV Escape tool does exactly this in your browser, and can also strip quoting from fields that no longer need it.

Dialects and deviations you will meet

Because RFC 4180 arrived long after CSV itself, plenty of files deviate from it. Most deviations are harmless once you know to expect them; a few will silently change your data.

• Bare LF line endings. The RFC says CRLF, but files written on Linux and macOS usually separate records with a lone \n. Nearly every parser accepts both, and you should too.
• Spaces around delimiters. Per the RFC, the space in "a, b" belongs to the second field. Some parsers trim it anyway, others do not, so a file written with padded commas parses differently in different tools. Do not add cosmetic spaces after commas.
• Single quotes are not CSV quoting. 'Smith, Jones' does not protect the comma; the apostrophes are just data characters, and the field still splits in two. Only double quotes have meaning in CSV.
• Backslash escaping. Some exports write \" or \, in the MySQL style. That is a different dialect: an RFC 4180 parser will keep the backslash as a literal character.
• Other delimiters entirely. In locales where the decimal separator is a comma, Excel reads and writes "CSV" with semicolons. The quoting rules stay the same, only the delimiter changes.

The rules in one paragraph

Quote a field if it contains a comma, a double quote, or a line break. Escape a double quote inside a quoted field by writing it twice. Separate records with CRLF, accept bare LF when reading. Never trim or add spaces around delimiters, and never rely on single quotes or backslashes. Follow those rules when writing, use a real parser instead of split(",") when reading, and CSV becomes as boring as it was always supposed to be.

Sources

• RFC 4180: Common Format and MIME Type for Comma-Separated Values (CSV) Files
• IANA media type registration: text/csv