CSV is probably the most widely exchanged data format in the world, and it is nearly as simple as a file can be. Understanding what is, and is not, inside one explains most of the problems people have with them.
What CSV stands for
CSV means comma-separated values. A CSV file is a plain-text table: each line is a row, and the values in a row are separated by commas. The first line is usually a header naming the columns.
name,city,age
Ada,London,36
Linus,Helsinki,54
Open that in a spreadsheet and you get three rows and three columns. Open it in a text editor and you see exactly what is above. The extension is .csv, and the media type is text/csv.
A format with a loose standard
CSV existed for years before anyone wrote a specification. RFC 4180, published in 2005, documents the common practice, but it is informational rather than a strict standard, and real-world files differ. Programs disagree about the delimiter, the text encoding, how to represent line breaks inside values and whether there is a header. That is why importing a CSV sometimes goes wrong.
What the file holds
Only text. A CSV file stores no formatting, no formulas, no column widths, no multiple sheets and no data types. The number 007 and the text 007 are indistinguishable in the file; whatever opens it decides how to interpret each value. That is the root cause of the problems described in CSV leading zeros and dates in Excel.
Strengths
- Universal. Practically every spreadsheet, database and programming language can read and write CSV.
- Compact and human-readable.
- Streamable. You can process a file one line at a time without loading it all.
- Diff-friendly in version control.
Limitations
- No data types or schema. Everything is text.
- No standard way to describe the encoding. See fixing garbled characters in CSV files.
- Awkward for nested data, which suits JSON better.
- Ambiguity around quoting, delimiters and line endings. See CSV quoting and escaping rules.
Not always a comma
In many European locales, where the comma is the decimal separator, spreadsheet software exports "CSV" with semicolons between values. Tab-separated files, usually .tsv or .tab, use a tab character. A file may be called CSV and use any of these. See CSV vs TSV and tab-delimited files.
How to open a CSV file
- In a spreadsheet application, using the import or open command.
- In a text editor, to see the raw contents.
- In a browser: Docento's Text & Markdown Editor opens
.csvfiles locally, shows them in a fixed-width font and checks that every row has the same number of fields. See how to open a CSV file without Excel.
When to choose CSV
Choose CSV to exchange flat tabular data between systems, to export from a database, or to keep a small dataset in version control. Choose a spreadsheet format when you need formulas and formatting, and JSON when the data is hierarchical. For a comparison, see CSV vs XLSX.
Takeaway
A CSV file is a plain-text table with one row per line and separators between values. It is simple and universal, but carries no types or formatting, so always check the delimiter, encoding and header when you import one.
