Why CSV Files Break And What They Never Tell You

A CSV file never tells you how to read it. Not the delimiter, sometimes called the separator, not the quoting, not the character set, not the line endings. Four decisions were made when the file was written. None of them was recorded anywhere. Every program opening it has to guess all four.

That is the whole problem and the reason the format survives anyway. Nothing else is as easy to produce. Almost every mail export, contact list and report ends up in one.

The CSV Specification With No Authority

There is a document. RFC 4180 sets out what a CSV should look like. What kind of document it is matters more than what it says.

It was published to write down what software already did rather than to tell anybody what to do. Its own text describes the format as one that has been in use for years without any formal specification. It makes no attempt to change that.

Compare that with how mail is specified. RFC 5322 says a message line ends with two particular characters and every mail server obeys. RFC 4180 says a CSV field may be enclosed in quotes and a great deal of software does whatever it likes. One is a rule, the other is a description of a habit.

The Four Things a CSV File Never Records

Every CSV carries four unstated decisions. A reader has to work out each one. Getting any of them wrong changes what the data appears to say.

The same program writes different files on different machines. A spreadsheet exporting a CSV follows the regional settings of the computer it is running on. Two colleagues export the same sheet. One gets commas and the other semicolons. Neither has done anything unusual.

The Quoting Rule That Breaks Simple CSV Parsers

A quoted value may contain a line break. That is explicitly permitted. It is also where most hand written CSV handling falls over.

subject,from,body
"Invoice query","sam@example.net","Hello,

Could you check the March invoice?"
"Site access","jo@example.org","Confirmed for Tuesday."

Split that on line breaks and you get five records, three of them nonsense. Parse it properly and you get three rows, one of which contains a body with a blank line in the middle of it.

This is exactly what a mail export produces. Message bodies contain line breaks by their nature, so any export putting bodies in a column produces the awkward case as a matter of course rather than as an edge case. A CSV of messages is the hardest kind of CSV there is.

What Happens When Mail Becomes a CSV File

Exports to CSV are common in mail work. The format is a poor fit for what mail contains.

The last two rows are where people are surprised later. A CSV of messages is a report about mail rather than a copy of it. It cannot be turned back into mail afterwards.

Use it for what it is good at. A list of who wrote to whom and when, for reading in a spreadsheet, is a reasonable thing to want and CSV does it well. Keeping the mail itself needs a format built for messages, which is any of the ones built to hold them.

What Excel Does to a CSV File Without Asking

Most complaints about broken CSV files are complaints about a spreadsheet trying to help.

None of these damages the file. The CSV on disk still holds what you wrote. The spreadsheet changed the display. The damage only becomes permanent if somebody saves over the original from that view. Opening through the import dialogue rather than by double clicking lets you set each column as text and avoid all of it.

Making a CSV File Somebody Else Can Open

1. Use commas and quote generously. Wrap any value holding a comma, a quote or a line break. Quoting a value that did not need it costs nothing and breaks nothing.
2. Write UTF-8. Add the byte order mark where Excel is the reader. That single choice removes the accented character problem in the place it happens most.
3. Put a header row on it. Nothing requires one and every recipient wants one. It is the only thing in the file that explains itself.
4. Say what you did. Separator, encoding, quoting, in the email or the filename. The file cannot state its own terms, so somebody has to.
5. Send JSON when software is the reader. Types and structure are stated rather than guessed, so none of the four questions arises.

The filename is a fair place to put it. Something ending in utf8-semicolon tells the next person more than any amount of explanation they will have deleted by the time they open the file.

Where a CSV has arrived and nobody knows how it was written, reading it before importing it saves the guesswork. Our CSV viewer shows the columns as a parser actually splits them rather than as a spreadsheet redraws them. The converters handle the route between a mailbox and a table when a report is what you need.