What is a CSV file?

CSV is a plain-text format you can open in any editor. It is used for moving data between programs.

The extension is .csv, and the full name is Comma-Separated Values. Both matter less than what the file can hold, which is what the rest of this page is about.

It dates from 1972. The specification is RFC 4180.

A format that has been readable for that long is a format worth trusting with something you want back in ten years.

It is published in full, so anyone can implement it from the document rather than by inspection, which is why it turns up in so many programs and why files written twenty years ago still open. A published specification is not the same thing as a royalty-free one: where a format wraps a codec, the patent licensing is a separate question the standard does not settle.

CSV stores its content exactly. Saving it again changes nothing, so it can be opened, edited and re-saved as often as you like without accumulating damage — which is what makes it a working format rather than a delivery one.

CSV has no comment syntax. Anything explanatory has to live outside the file, which is worth knowing before choosing it for something a human will edit by hand.

Microsoft Excel, LibreOffice Calc and pandas read it, and so do most programs of the same kind.

If a file will not open, the format is rarely the problem — it is more often that the program predates it. Converting to something older is the reliable way past that, and it is what the rest of this site is for.

No browser reads it.

That is the single most common reason to convert it: not that the format is bad, but that the place you want to show the file cannot read it.

CSV is meant to be opened and changed. Keep the file in this format for as long as the work is going on, and export from it whenever a finished copy is needed.

The recurring complaints: column types are guessed on import rather than stated.

None of these is a reason to avoid the format. They are the things worth knowing before you are surprised by one, which is a different claim and a more useful one.

A CSV is rows of values separated by commas, and that description holds until the first value contains a comma. Then it needs quoting, and quoted values need a way to contain a quotation mark, and values need a way to contain a line break — and each of those rules is where implementations begin to disagree.

RFC 4180 was published in 2005 to write the conventions down, and it is explicit that it documents existing practice rather than defining a standard everyone must follow. Twenty years later, plenty of software still does not follow it. That is the honest starting point: CSV is a family of nearly compatible formats wearing one extension.

A value containing a comma, a quotation mark or a newline must be wrapped in double quotes, and a quotation mark inside such a value is written twice. Get that right and a value can contain anything, including a whole paragraph with line breaks in it.

Get it wrong and the damage is silent. An unescaped quote makes a parser swallow the rest of the file into one field. A comma inside an unquoted address shifts every column after it by one, for that row only — so a file of ten thousand rows can be correct except for the eleven that mentioned a company with a comma in its name, and nothing reports an error.

In countries where the decimal separator is a comma — most of continental Europe — spreadsheets export with semicolons instead, because a file full of "1,50" would be unparseable otherwise. The extension is still .csv.

Tabs are the third common choice and are the reason TSV exists, since a tab almost never appears inside a value. If a CSV opens as a single column, the separator is the first thing to check: the file is fine and the reader guessed wrong. Most import dialogs let you say which character to use, and most export dialogs let you choose.

A CSV has no types. The characters 0 0 1 2 3 sit in the file, and whether they become the number 123 or the text "00123" is decided entirely by whatever reads them.

That is the root of the whole family of complaints about spreadsheets eating data: leading zeros disappearing from postcodes, product codes turning into dates, long identifiers losing their last digits to floating point. None of it is in the file. It happens on opening, and the defence is to import rather than open and declare the awkward columns as text.

A CSV carries no declaration of its character encoding. UTF-8 is the sane default and is what almost everything writes now; Excel on Windows has historically assumed a regional code page instead, which is why an export full of accented characters opens as gibberish.

The usual workaround is to write a byte order mark — three invisible bytes that tell Excel the file is UTF-8. It works, and it creates a different problem: many parsers then see those bytes as part of the first column heading, so a match against "id" silently fails. There is no arrangement that satisfies both, which is a fair summary of the format as a whole.

Streaming. A file larger than memory can be read row by row by a tool that never holds more than one line, which is why data pipelines, database imports and command-line utilities all speak it and why a five-gigabyte export is workable.

And transparency. It is text, so nothing can hide in it — no macros, no external links, no formulas reaching out to other files. That is precisely why security-conscious systems ask for CSV rather than a workbook, and it is a real property rather than a consolation.

Five habits prevent nearly every problem. Use UTF-8 without a byte order mark. Quote every field that could contain the separator, a quote or a newline — or simply quote everything, which is legal and cheap. Use a header row with short, unambiguous names. Write dates in ISO form, year first, which sorts correctly and cannot be misread as American or European. And say which separator you used if it is not a comma.

None of that is enforced by anything, which is why it is worth doing deliberately. A CSV that follows those five rules opens correctly in every tool that will ever receive it.