What Is a CSV File? The Simplest Format, and the One That Eats Your Data

A CSV file is about as simple as a data format gets. It is plain text, one line per row, with values separated by commas:

name,quantity,price
Widget,12,4.50

That is the whole idea, and it is why CSV outlived every proprietary format aimed at replacing it. Every program can read it, it is tiny, it opens in any text editor, and you can see exactly what is in it.

It is also the format most likely to damage your data without telling you, and the reasons are worth knowing before the next time you open one.

The Comma Is Not Always a Comma

This catches people constantly, and it is a language problem rather than a software bug.

In German, French, Spanish, Portuguese and many other locales, the comma is the decimal separator — 4,50 means four and a half. A comma cannot then also separate fields, so spreadsheet software in those locales writes and expects semicolons instead:

name;quantity;price
Widget;12;4,50

Both files are called CSV. Open the semicolon version in a comma-expecting program and every row collapses into a single column. Open the comma version in a semicolon-expecting one and the same thing happens in reverse.

There is no marker inside the file saying which it is. The only reliable fix is to import rather than double-click, so you can state the delimiter yourself.

Quoting, and Why It Exists

What happens when a value contains a comma? It gets wrapped in double quotes:

"Smith, John",12,4.50

And when a value contains a quote mark, the quote is doubled to escape it. A field can even contain a line break, as long as it is quoted — which is why you cannot reliably count rows by counting lines in a CSV.

Every proper CSV tool handles this. Hand-built files — anything assembled by joining strings with commas — usually do not, and the breakage appears only when someone’s name or address happens to contain a comma.

The Encoding Problem: Why Chinese Turns to Garbage

This is the most damaging gap in the format, and it has no good answer.

A CSV file contains no declaration of its character encoding. Unlike HTML or XML, there is nowhere to say “this is UTF-8”. The program opening it has to guess.

Excel on Windows traditionally guesses the system’s local codepage rather than UTF-8. So a CSV containing Chinese, Japanese or Korean text — or accented European characters — opens as unreadable symbols, even though the file is perfectly correct.

Two practical responses:

• Add a UTF-8 byte order mark at the start of the file. It is three invisible bytes that tell Excel the encoding, and it is the usual reason an exported CSV opens correctly in one tool and as garbage in another. Many export tools offer “UTF-8 with BOM” for exactly this.
• Import instead of opening. The import dialogue lets you choose the encoding explicitly, which removes the guessing entirely.

What Spreadsheets Destroy on Opening

This is the part that costs people real work, because it is silent and it happens before you have done anything.

A CSV has no types — every value is text. The spreadsheet guesses what each one means, and its guesses are confidently wrong in several recurring ways:

• Leading zeros vanish. A postcode 01234 becomes 1234. Phone numbers, account numbers, product codes and national ID numbers all suffer this.
• Long numbers become scientific notation. A sixteen-digit card or tracking number turns into something like 1.23457E+15, and the original digits are gone, not hidden.
• Anything date-shaped becomes a date. A value like 3-10 becomes 3 October. This is the famous problem that forced geneticists to rename genes whose symbols kept being converted into dates.
• Trailing and leading spaces get trimmed or preserved inconsistently, which breaks matching later.

The critical detail: if you then save the file, the damage is written back. The original values are not recoverable from the saved copy.

Two habits prevent all of it. Keep an untouched copy of any CSV you receive. And when the content includes codes, identifiers or anything with leading zeros, use the import route and mark those columns as text before the data lands.

What CSV Is Genuinely Good At

• One flat table moved between two systems that share nothing else.
• Size. A million rows of numbers is a few tens of megabytes, with no overhead.
• Durability. It is plain text; it will open in fifty years.
• Comparability. Two versions can be compared line by line, which spreadsheet formats make difficult.

And what it cannot do: multiple sheets, formulas, formatting, cell types, merged cells, charts, or anything nested. Those are the reasons the spreadsheet formats exist alongside it.

Quick Reference

• Plain text, one line per row, values separated by a delimiter
• The delimiter is a semicolon in comma-decimal locales — import, never double-click
• Values containing a delimiter are quoted; quotes inside are doubled
• No encoding declaration — use UTF-8 with BOM, or set the encoding at import
• Opening in a spreadsheet strips leading zeros and converts codes into dates
• Saving after that writes the damage back permanently
• Always keep an untouched copy of the original