Beautifully, frustratingly simple
A CSV file is exactly what it sounds like: rows of values with commas between them and a header row naming the columns. There's almost nothing to it, which is the source of both its universality and its quirks. It has no types (everything is text), no formatting, no formulas and no multiple sheets — just the data.
The commas themselves are the classic snag: when a value contains a comma (or a line break, or a quote), it has to be wrapped in quotes and escaped. RFC 4180 finally wrote the conventions down in 2005, but you'll still meet files that bend them, which is why TSV — tabs instead of commas — is sometimes preferred.
CSV as a hand-off format
CSV's job is moving tabular data between things that otherwise wouldn't talk: export from Excel, import into a database; pull from one app, feed into a script. It's a transport format, not a working one — you wouldn't keep your data in CSV, but it's how the data travels. For structured or nested data destined for code, JSON is usually the better target.
Strengths
- Read and written by virtually every data tool ever made.
- Plain text — open it in any editor, diff it, generate it from a script.
- Tiny and simple, with no proprietary baggage.
Limitations
- No types, formatting, formulas or multiple sheets.
- Commas, quotes and line breaks inside values need careful escaping.
- Flat only — can't represent nested or hierarchical data.
When to use CSV
Use CSV to move tabular data between spreadsheets, databases and scripts. For nested data or web APIs, prefer JSON; to preserve formatting and multiple sheets, keep it in XLSX.
CSV FAQ
- How do I open a CSV file?
- Any spreadsheet (Excel, Google Sheets, Numbers, LibreOffice Calc) opens CSV, as does any text editor. To work with it as data, import it into a spreadsheet or load it in code.
- What's the difference between CSV and Excel (XLSX)?
- CSV is plain text holding a single table with no formatting, types or formulas. XLSX is a full workbook — multiple sheets, styled cells, formulas and charts. Converting XLSX to CSV keeps the values and drops everything else.
- Why are my CSV columns wrong when values contain commas?
- A comma inside a value splits the column unless the value is wrapped in quotes. A properly written CSV quotes such fields; if yours doesn't, the source export is the problem.