Converting Between List Formats Without Losing Data: The Quoting Rules Nobody Reads

The one-liner, and the exact moment it breaks

Newline list to comma list is join. Comma list to newline list is split. Both are correct exactly as long as no item contains the delimiter, and the moment one does the conversion stops being reversible without saying so. Our test list of five items — Smith, John / Doe, Jane / O’Neill, "Bud" / a two-line address / plain — joined on commas and split back returned eight items. Nothing threw, nothing warned, and three of the eight were fragments of names. That silence is the whole problem: a list converter that loses data produces a plausible list, not an error.

The same experiment in each of our languages gives the same shape: three surname-comma-forename entries become six after a naive round trip, in English, French, Spanish, Portuguese, German and Italian alike. Written through an RFC 4180 writer and read back with an RFC 4180 parser, every one of them comes back as three items identical to the input. The rule is not language-specific; only the data that trips it is.

What RFC 4180 actually says

RFC 4180 is short, from October 2005, and — worth knowing — Informational rather than a standard. Its seven rules are: records are separated by CRLF; the last record need not end with one; an optional header line may come first; fields are separated by commas and spaces count as part of a field; quoting is optional, but an unquoted field may not contain a double quote; fields containing line breaks, double quotes or commas should be enclosed in double quotes; and a double quote inside a quoted field is escaped by preceding it with another double quote. That last rule is the one people invent an alternative for, usually a backslash, which no CSV reader expects.

The ABNF grammar in section 2 is stricter than anything in practice. TEXTDATA is defined as %x20-21 / %x23-2B / %x2D-7E, which excludes the comma at %x2C and the double quote at %x22 — and also every byte above 127. We checked: "plain" is TEXTDATA-legal; "café", "naïve", "Straße" and "ação" are not. In practice the charset parameter of the text/csv media type carries the encoding and everyone writes UTF-8 anyway, but it is a reminder that the RFC codified an existing mess rather than designing a format. The document says so itself, recommending that implementations be conservative in what they produce and liberal in what they accept.

A CSV field may contain a line break

This is the rule that breaks the most importers, because it contradicts the mental model of one record per line. We wrote a two-record file whose second field is a two-line address: the bytes are id-1,"Line one CRLF Line two",ok CRLF id-2,flat,ok. Split on the newline it yields three fragments; parsed properly it yields two records, the first of which contains the line break intact. Any code that reads a CSV file with readLines is wrong on this input, and the input is not exotic — addresses, product descriptions and pasted notes all contain line breaks.

Line endings deserve their own paragraph. The RFC says CRLF between records, and splitting "a,b CRLF c,d CRLF" on the line feed alone returns ["a,b\r", "c,d\r", ""] — two fields with an invisible carriage return glued to them and one empty tail. That stray \r is the reason a value compares unequal to itself between two systems, and the reason a trailing empty string turns into a phantom last row. A parser that consumes CRLF, LF and a lone CR as record separators handles all three families of file and returns the same two records for each.

Why half of Europe writes CSV with semicolons

Ask the platform what a number looks like in each of our six locales and the collision is obvious. Formatting 1234567.5 gives 1,234,567.5 in en-US, 1 234 567,5 in fr-FR with a narrow no-break space at U+202F as the group separator, 1.234.567,5 in es-ES, de-DE and it-IT, and 1 234 567,5 in pt-PT with U+00A0. Five of the six use the comma as the decimal mark. A comma-delimited price list in those locales therefore has a comma inside a field on every single row — which is precisely why their spreadsheet software writes and expects the semicolon instead.

The run makes it concrete. The row Chair / 1,299.00 / 2 joined on commas parses as four fields — Chair, 1, 299.00, 2 — because the thousands separator is a comma in English. The row Stuhl / 1.299,00 / 2 joined on commas parses as four for the mirror-image reason: the decimal mark is a comma in German. Quote the price field and both are three fields again; use a semicolon and both are three fields without any quoting at all. Neither approach is more correct; the semicolon is the one a European spreadsheet will open without an import dialogue, and quoted commas are the one an API will accept.

Empty items, trailing separators, and what no round trip can recover

Splitting the empty string on a comma returns one empty item, not zero. "a,b," returns three items, the last one empty. ",a" returns two, the first one empty. "a,,b" returns three, the middle one empty. None of these is a bug; they are all the consequence of one definition — a separator separates, so n separators mean n+1 items. What people usually want is the filtered version, and filtering is a decision that silently deletes a genuinely empty field.

The unrecoverable case is at the writing end. Joining the empty list and joining a list holding one empty string both produce the empty string, so the two are identical on the wire and no parser can tell them apart. Our own minimal parser then compounded it: reading the empty string it returned zero records, which is right for one of the two inputs and wrong for the other. Switching to a writer that quotes every field fixes it exactly: one empty item becomes the two characters "" and parses back to a single empty item, while zero items stay the empty string and parse back to nothing. If empty items can occur in your data, always-quote is not a style preference.

JSON, tabs, and choosing a delimiter on purpose

A JSON array sidesteps the whole argument by quoting everything and escaping the rest: our five-item list survived the round trip unchanged, with the two-line address stored as a single string containing \n. That is the format to pick when a program is on both ends. Its cost is that every consumer must be a JSON parser, and that JSON has types — a list of postcodes will come back as numbers if someone writes them without quotes, and 01234 will come back as 1234 or as a syntax error.

Tab-separated values are the format with no specification, which is why they work so often and fail so quietly. Our adversarial list contained a tab inside an item, so a tab delimiter would have split it. Before choosing any delimiter, look: on that list the comma, the semicolon and the tab all occurred inside items while the vertical bar, U+001F and the null byte did not. A delimiter that provably does not occur turns the conversion back into the one-liner it looked like at the start — and if none is safe, quote. One last practical note that has nothing to do with parsing: a field starting with =, +, - or @ is treated as a formula by spreadsheet applications, so a list of user-supplied strings should have such fields neutralised before anyone opens the file.

Frequently asked questions

Can a CSV field really contain a line break?

Yes, and RFC 4180 says so explicitly: a field containing line breaks should be enclosed in double quotes, and the ABNF allows CR and LF inside an escaped field. We wrote a two-record file whose second field held a two-line address; it occupies three physical lines, so counting lines gives 3 and parsing gives 2. Any importer built on reading lines is wrong on that file.

Is a semicolon-delimited file still CSV?

Not according to RFC 4180, whose ABNF fixes the delimiter at %x2C, the comma. In practice it is what spreadsheet software writes in every locale that uses the comma as the decimal mark — five of our six. Keep the rest of the rules: quote fields that contain the delimiter, double the quotes, separate records with CRLF. Label the file honestly and state the delimiter when you hand it over.

Is an empty string one item or zero?

Splitting says one: splitting "" on a comma returns a list with one empty item. Joining cannot tell you, because [] and [""] both produce the empty string. So the answer is a convention you have to choose and record, not something the data contains. The only way to keep the distinction across a round trip is to quote every field, which turns one empty item into two quote characters and zero items into nothing at all.

How do I escape a double quote inside a field?

By doubling it, inside a quoted field. The item say "hi" is written "say ""hi""" — an opening quote, the text with each inner quote written twice, a closing quote. A backslash does nothing; CSV has no backslash escape. Our eleven-item adversarial list, which included that string and an already-quoted one, round-tripped identically under this rule.

When should I use a JSON array instead?

Whenever both ends are programs. JSON quotes every string and escapes control characters, so items containing commas, quotes and line breaks need no special handling: our five-item list round-tripped unchanged, the two-line item stored with \n inside a single string. Prefer CSV when a spreadsheet or a person is at the other end, and remember JSON has types, so identifiers with leading zeros must stay strings.