Why your CSV opens wrong in Excel

You export a CSV, open it in Excel, and the data has quietly changed. Phone numbers have lost their leading zero. Product codes have become 1.23E+11. Dates that read 03/05 now say 5 March, or 3 May, depending on which side of the Atlantic the file was made.

Nothing is corrupt. The file on disk is exactly what was written. Excel is guessing what your text means, and it is guessing wrong. This article explains what it is doing and how to stop it.

A CSV has no types, and that is the root of the problem

Open a CSV in a text editor and it is just characters:

id,name,phone,joined
1001,Priya Raman,07700900123,2024-03-05

Nothing in that file says phone is text and joined is a date. There is no place to say it, because CSV has no way to store types. It is a grid of characters and nothing more.

A spreadsheet works the other way. It is built on types. Every cell is a number, a date, a boolean or text, because that is what makes sorting and arithmetic work. So when Excel opens a CSV, it has to decide for every single cell which of those it is looking at. It decides by matching patterns, and it never asks you.

The four things it gets wrong

Leading zeros disappear

07700900123 looks like a number, so Excel turns it into one. Numbers do not have leading zeros, so the zero is dropped. You get 7700900123. The same happens to postcodes, account numbers and any ID that starts with a zero.

Long numbers become scientific notation

A fifteen-digit code becomes 1.23457E+14. Worse, Excel stores numbers with about fifteen significant digits, so a sixteen-digit code is not just shown oddly: the last digits are actually gone. Formatting the column afterwards will not bring them back.

Dates rearrange themselves

03/05/2024 is 3 May in Britain and 5 March in America, and a CSV cannot say which one was meant. Excel uses your computer's regional settings, so the same file really does mean different things on two colleagues' machines.

Sometimes it makes up dates out of nothing. Gene researchers had this problem so badly (Excel turned gene names like SEPT2 into 2 September) that in 2020 the naming committee renamed the genes rather than keep fighting the spreadsheet.

The columns do not separate

Everything lands in column A. This is a regional setting too. In countries where the comma is the decimal separator, which includes much of Europe, Excel expects a semicolon between fields instead of a comma. A perfectly valid comma-separated file arrives as a single column.

The fix: import, do not open

Double-clicking a CSV gives Excel no chance to ask you anything. Importing it does. This is the most useful tip in this article.

1. Open Excel with a blank workbook. Do not open the file itself.
2. Go to Data → From Text/CSV.
3. Pick your file. A preview appears.
4. Choose Transform Data rather than Load. In the editor that opens, set the type of each problem column to Text.
5. Load.

Columns marked Text are taken exactly as written. Leading zeros survive, long codes stay whole, and nothing is turned into a date. Microsoft describes the same import steps in its guide to importing text and CSV files.

If the columns did not split, the same dialog has a delimiter setting. Switch between comma and semicolon there.

If you are the one producing the file

You can make the file harder to misread before it ever reaches anyone.

• Write dates as YYYY-MM-DD. The ISO format means the same thing everywhere on earth and sorts correctly as plain text. 2024-03-05 cannot be misread.
• Quote anything that is not really a number. Phone numbers, IDs and postcodes are labels, not quantities. You will never do arithmetic on a phone number.
• Send a spreadsheet instead where you can. An .xlsx file stores the type of every cell, so there is nothing to guess. If the recipient is going to open it in Excel anyway, give them a file that keeps its meaning. CSV to Excel does the conversion here without uploading anything.

Check before you send

A CSV that looks right on your machine can be wrong on someone else's, because half of these problems depend on regional settings and not on the file. Two quick habits catch most of them:

• Open the file in a plain text editor such as Notepad or TextEdit, and look at the raw characters. That is what the file really contains, with nothing interpreting it.
• Test with a known file. Our sample CSV has dates, long numbers and booleans in it on purpose, so you can see exactly what a tool or a colleague's Excel does to each one before you trust it with real data.

Why CSV survives at all

With all these problems, you might wonder why anyone still uses it. The reason is that every program ever written can read it, it needs no library, and a program can produce it in three lines. There is no version, no vendor and no licence.

It is a perfectly good format for moving data between programs. It is a poor format for a person to open by double-clicking, and every one of these problems lives in that gap.