A CSV file is a text file. That is the whole thing. If you open one in Notepad you will see everything it contains, because there is nothing else in there — no formulas, no fonts, no colours, no second sheet, no hidden anything.
Almost every problem people have with CSV comes from forgetting that one fact, and from the program that opens it by default behaving as though it were a spreadsheet instead.
What a CSV File Actually Is
The meaning of CSV is not hidden anywhere clever. The abbreviation stands for Comma-Separated Values, and that full form is the entire description of the format: the values are separated by commas. The file extension is .csv, and the file type is plain text — the same kind of file as a .txt, just with an agreed shape inside it.
Here is a complete example of a CSV file. Not an excerpt, not a simplified version — an entire, valid file:
Four lines. The first is a header row naming the columns. Column headers are a convention rather than a requirement, and a CSV with no header line is still a valid CSV. Every line after it is one record, and every comma starts a new field. A spreadsheet program draws that as a grid, but the grid is something the program invents on the way in. It is not in the file.
That simplicity is why CSV has outlived nearly every format invented to replace it. Every database exports it, every analytics tool imports it, every language reads it, and a file written in 1995 still opens today. It is the closest thing data has to a universal handshake.
A CSV is used for anything that fits a flat table: database dumps, analytics exports, bank statements, product catalogues, mailing lists, and the contacts files that Google Contacts, Outlook and most phones import and export. Its advantages are the same thing as its limitations — no types, no formatting and no software of its own, so there is nothing in it to break, expire or need a licence.
The Rules Nobody Ever Standardised
CSV was in wide use for decades before anyone wrote the rules down. The closest thing to a specification is RFC 4180, published in 2005 — and even that is marked Informational. It does not define the format so much as describe what most people had already settled on.
The syntax is short enough to state in four rules, and they describe the whole layout of the file:
- Records are separated by line breaks; fields within a record are separated by commas.
- A field may be wrapped in double quotes. It must be wrapped if it contains a comma, a double quote, or a line break.
- A double quote inside a quoted field is written twice.
- Whitespace is part of the field. A space after a comma is a space in your data.
That third rule is the one people trip over. Here is an example where one address contains a comma and one company name contains quotes:
Row 1 has four fields, not six, because the commas inside the quotes belong to the data. Row 2 contains the company name The "Blue" Cafe — each internal quote is doubled so the parser knows it is a character rather than the end of the field.
This is exactly where hand-written CSV exports break. If a program joins fields with commas without quoting them, one customer with a comma in their address quietly shifts every column after it by one, for that row only. Nothing errors. The file still opens. The data is simply wrong from that column onwards. A CSV Validator catches exactly this class of fault: rows whose field count does not match the header.
How to Open a CSV File — and Make One
There are four sensible ways to open a CSV file, and they are good at different things:
| How | Good for | Watch out for |
|---|---|---|
| Text editor Notepad, TextEdit, VS Code | Seeing the truth — every comma, quote and blank field | No grid, so wide files are hard to read |
| Online viewer | A quick look at a file as a table without importing it anywhere | Very large files may be slow in a browser |
| Google Sheets | Sharing and light editing | Converts values on import, much like Excel |
| Excel | Analysis, charts, pivot tables | Changes your data on the way in — see below |
To create a CSV file you need nothing special. Open a text editor, type your rows, and save with a .csv extension. From a spreadsheet, use Save As and pick CSV — remembering that only the active sheet is saved, and that formulas become their results, because a CSV has nowhere to keep a formula.
If a file arrives looking like chaos, the CSV Viewer will lay it out as a table without touching it, and the CSV Formatter will tidy inconsistent quoting and spacing.
Why Excel Is the Riskiest Way to Open One
Double-clicking a CSV opens Excel, and Excel does not open it. It imports it — guessing a data type for every single cell, then rewriting the cell to match the guess. It never asks, and it never tells you what it changed.
Four guesses cause almost all the damage:
| In the file | What Excel shows | What was lost |
|---|---|---|
007 | 7 | Leading zeros — postcodes, account numbers, product codes |
4029382712345678 | 4.02938E+15 | Precision. Excel keeps 15 significant digits; a 16-digit number is changed permanently |
3-4 | 04-Mar | The original text. It was a size, a score or a range |
+353871234567 | 353871234567 | The leading + of an international phone number |
None of this is a bug report anyone can file. Excel is doing what it was designed to do: make a spreadsheet out of loose text. The problem is that a CSV is not loose text. It is data that already had a type, and the guess overwrites it.
The damage becomes permanent at the moment you press save. Until then the file on disk still holds 007; once Excel writes it back, the file holds 7 and the original is gone.
The same gap explains a complaint that sounds like a bug: a CSV not saving changes. Colours, column widths, formulas, filters and extra sheets are all things a CSV has nowhere to store, so Excel discards them on save and keeps only the values. Nothing failed — the file simply has no room for any of it.
The Year Excel Made Scientists Rename Genes
If that sounds like a minor annoyance, it is worth knowing how far it has gone.
Several human genes have short symbols that Excel reads as dates. SEPT1 becomes 1 September. MARCH1 becomes 1 March. DEC1 becomes 1 December. Geneticists shared results as spreadsheets, and the gene names silently turned into dates on the way in.
In 2016 a study in Genome Biology went through the supplementary spreadsheets attached to published papers and found gene-name errors of this kind in roughly one in five of them. Not one in five cells — one in five papers.
The eventual fix was not to Excel. In 2020 the committee responsible for human gene naming renamed the genes: SEPT1 became SEPTIN1, MARCH1 became MARCHF1, and so on down the list.
Why the Accents Turn Into Nonsense
You open a file and every accented character has become gibberish — café shows up as café, and names in Hindi or Japanese are a wall of question marks.
This is the format's real design gap. A CSV has no header, no metadata, nowhere at all to record which character encoding it uses. The bytes are just bytes, and the program opening the file has to guess. Guess UTF-8 when the file is Windows-1252, or the other way round, and every non-English character breaks.
Two practical rules avoid nearly all of it:
- Save as UTF-8. It covers every language you are likely to need, and it is what almost every other tool expects.
- For Excel specifically, choose “CSV UTF-8” in the Save As list. That variant writes a few extra bytes at the start of the file — a byte order mark — whose only job is to tell Excel which encoding it is looking at.
If you are handing a file to someone else, say which encoding you used. The file itself cannot.
When a Comma-Separated File Has No Commas
Open a CSV and find every row crammed into a single column, and the delimiter is usually the reason. This is what people mean when they say a file is not opening correctly in Excel or not importing correctly: nothing is broken, the program is simply splitting on the wrong character.
In much of Europe the comma is the decimal separator — 3,14 is pi. A comma cannot then also separate fields, so spreadsheet programs in those locales write and expect a semicolon instead. The file still ends in .csv. It is still called a comma-separated file. There is not a comma in it.
Tab-separated files are common too, sometimes saved as .tsv and sometimes not. So “CSV” in practice means delimited text, and the delimiter is whatever the file happens to use. Any decent viewer or converter lets you set it; if a file arrives as one long column, changing the delimiter is the first thing to try, not the last.
How Big Can a CSV File Get?
The format has no limit. A CSV can hold a billion rows, because it is a text file and text files simply keep going.
The limits belong to whatever you open it with. An Excel worksheet stops at 1,048,576 rows and 16,384 columns — that is Excel’s maximum row count, not the file’s, because a CSV has no row limit of its own. A larger CSV does not fail loudly either: Excel loads what fits and leaves the rest behind. Browser-based viewers hit memory limits earlier. Command-line tools and databases have no practical ceiling at all.
For files past a million rows, the usual answer is not to open the whole thing. Split it into pieces with a CSV Splitter, work on one piece at a time, and rejoin later with a CSV Merger — or push it into a database and query it there.
CSV, Excel or JSON?
Each of the three is good at something the others are bad at.
| CSV | Excel (.xlsx) | JSON | |
|---|---|---|---|
| Human-readable as text | Yes | No | Yes |
| Keeps data types | No | Yes | Yes |
| Formulas, formatting, sheets | No | Yes | No |
| Nested or repeating data | No | No | Yes |
| File size for the same rows | Smallest | Larger | Largest |
| Opens anywhere, forever | Yes | Mostly | Yes |
Use CSV to move a flat table between two systems that do not otherwise speak to each other. Use Excel when a person needs to analyse it and the formatting matters. Use JSON when the data is nested, or when an API is involved. Converting between them is routine — CSV to JSON, CSV to Excel and CSV to SQL all start from the same flat table.
So: a CSV file is a text file with a delimiter, a possible header row, and quoting rules for the awkward cases. It carries no types, no encoding and no formatting, which is simultaneously why it works everywhere and why the program that opens it feels free to invent all three. Look at the text first, import deliberately second, and CSV stops being mysterious.