What Is a CSV File? Open, Edit and Convert It
CSV stands for comma-separated values, and the format is about as plain as computing gets: a text file where each line is a row and commas split that row into columns. That is very nearly the entire specification. It is also why every spreadsheet, database, bank export screen and analytics dashboard can produce one, and why CSV keeps turning up as the only format two otherwise incompatible programs both understand.
What the format looks like
Three customers, with a header row naming the columns:
name,city,notes
Ada Lovelace,London,First programmer
"Torvalds, Linus",Helsinki,"Said ""talk is cheap"""
Grace Hopper,New York,The second row shows the two quoting rules that trip everyone up. A field containing a comma or a line break has to be wrapped in double quotes, and a literal double quote inside a quoted field is written as two double quotes. The trailing comma on the last row just means Hopper’s notes field is empty.
These conventions are codified in RFC 4180, a one-page standard that most software follows and some software doesn’t. The practical consequence: splitting a CSV on commas yourself breaks the moment a quoted field appears, which is why real parsers exist.
How to open a CSV file
- In Excel, double-clicking works but see the pitfalls below. The safer route is Data → From Text/CSV, which lets you say how each column should be interpreted.
- In Google Sheets, use File → Import or drag the file onto a sheet. Generally gentler with your data than Excel’s double-click, and free on any platform.
- Apple Numbers opens CSV directly on macOS and iOS with a double-click or a tap, and exports back out via File → Export To → CSV.
- Any text editor will do, and it is worth remembering CSV is just text. Notepad, TextEdit or VS Code show you exactly what is in the file, which is the fastest way to diagnose a “corrupted” import that turns out to be fine.
The classic Excel pitfalls
More CSV files are damaged by opening them than by creating them. Excel’s eager auto-conversion is usually the culprit:
- Leading zeros vanish. ZIP code
02134becomes2134, and phone numbers and product codes go the same way. Save the file and the damage is permanent, so import those columns as Text. - Dates appear out of nowhere. Anything date-shaped gets converted. The gene named
SEPT2became September 2nd in so many published spreadsheets that geneticists eventually renamed the gene. - Long numbers get mangled. A 16-digit ID collapses into scientific notation (
9.02E+15) with its final digits rounded away to zero. - The semicolon surprise. Across much of Europe the comma is the decimal separator, so regional Excel reads and writes CSV using semicolons instead. Receive one of those with US region settings and the entire file lands in column A. The CSV Delimiter Converter switches between commas, semicolons, tabs and pipes without breaking quoted fields, which is usually the whole fix.
Converting CSV to other formats
Because CSV is the universal export, it is constantly the input to a developer workflow. Two conversions come up daily. Feeding an API or a config with tabular data means converting CSV to JSON, where each row becomes an object keyed by the header names and numbers and booleans are detected automatically. And answering “check these 200 order IDs in the database” means turning a CSV column into a quoted SQL list, which is the job of the CSV to SQL IN converter, escaping and de-duplicating along the way. Both run in the browser, which is worth something when the export came out of a CRM full of customer records.
When CSV beats Excel formats, and when it doesn’t
| CSV wins when… | XLSX wins when… |
|---|---|
| Data moves between different programs or languages | Humans work inside the file itself |
| Files are huge, because CSV streams line by line | You need formulas, formatting or charts |
| Version control matters: diffs are readable text | One workbook needs multiple sheets |
| The file should still open in 30 years | Column types must be preserved exactly |
The limits are the flip side of the simplicity. CSV has no formulas, no formatting, no multiple sheets and no declared column types, so everything is text until the reader decides otherwise. That last point is exactly where the Excel pitfalls above come from. Use CSV as the interchange format it is, do the styling and calculation somewhere else, and keep an eye on any column whose values merely look like numbers.