Comma-separated values looks like it needs no specification. Values, separated by commas. It was in use for decades before anyone wrote it down, and by the time somebody did, every implementation had already made its own decisions.
RFC 4180 arrived in 2005 and describes common practice. It is informational rather than a standard anyone must follow, so it documents a convention rather than creating one — and it is routinely ignored by software older than it.
One: the delimiter is not always a comma
In much of Europe the comma is the decimal separator, so a file of numbers separated by commas is unreadable. The convention there is a semicolon, and Excel follows whatever the operating system's regional list separator says.
Which produces the most common CSV failure in existence: a file exported on one machine opens as a single column on another. Nothing is wrong with the file. The two machines disagree about what a separator is.
Tab-separated files are also called CSV constantly, and pipe-delimited ones turn up in anything that expects commas inside the data.
Two: quoting and escaping
A field containing the delimiter has to be protected, and the RFC's rule is to wrap it in double quotes. A field containing a double quote doubles it:
"he said ""no""" means he said "no".
That is genuinely odd-looking, so programmers reach for backslash escaping instead, which no spreadsheet understands. Some writers quote every field; some quote only when necessary; some quote nothing and hope.
A quoted field may also contain a line break, which is legal and correct and breaks every naive parser ever written — because read the file line by line is wrong the moment one field contains a newline.
Three: the encoding is not declared
A CSV file contains no header, no declaration and nowhere to state its encoding. The reader must guess.
Guess wrongly and every non-ASCII character is mangled — Arabic, accents, curly quotes, currency symbols. The file is undamaged and the reader is misreading it, which is why the same file opens correctly in one program and as gibberish in another.
The only in-band hint available is a byte order mark at the start. Excel honours it, so UTF-8 with a BOM is the pragmatic choice for a file that will be double-clicked — and it is an annoyance for anything parsing it programmatically, which sees three unexpected bytes before the first column name.
Four: line endings and the last row
RFC 4180 specifies a carriage return and a line feed. Unix systems write a line feed alone. Both are everywhere, and a parser splitting on the wrong one leaves a stray carriage return at the end of every last field.
The final record may or may not be followed by a newline, and readers disagree about whether that means there is an empty row after it. This is where the mysterious blank row at the bottom of an import comes from.
The things that are not the format's fault
Types. CSV has none. Everything is text, and any distinction between the number 007 and the string 007 is invented by the reader — which is why leading zeros vanish and product codes become dates.
Structure. CSV is a rectangle. Nested data does not fit, and flattening it is a separate decision with real trade-offs.
A header row. Optional, undeclarable, and impossible to detect reliably. A file whose first row happens to be data gets one record silently eaten.
Writing one that survives
UTF-8, with a BOM if a spreadsheet will open it. Commas, quoting every field that contains a comma, a quote or a newline, and doubling internal quotes. CRLF endings. A header row. Dates as ISO 8601, which sorts correctly and cannot be misread as a different regional order.
And say what you did. A single line in the accompanying email — UTF-8, comma, quoted — saves more time than any amount of careful writing, because the recipient can configure their importer instead of guessing.
Reading one that does not
Open it in a text editor first. Thirty seconds tells you the delimiter, the quoting style, whether there is a header and whether the encoding is right. Every importer settings dialog is asking you these questions, and looking is faster than guessing twice.
Then clean it before importing rather than fighting the importer — normalising the delimiter, the quoting and the encoding in one pass is far easier than persuading a spreadsheet to accept something unusual.
The short version
The name describes one of four things that vary. Look at the file before importing it. Write UTF-8 with a BOM, quote defensively, and tell the recipient what you did.