Why Excel removes leading zeros and changes CSV data

A CSV file is plain text. Each line is a row, each comma starts a new column, and that is the whole format. It carries no information about whether a column holds numbers, dates or text. When Excel opens one by double-click, it has to guess the type of every cell, and it guesses in the same way every time. Seven of those guesses change your data. In Excel 2016 to 2021 all seven happen silently, and if you press Save, they are written back over the original. Microsoft 365 can warn about four of them (leading zeros, long numbers, "E" notation and letter-and-number codes read as dates) and lets you switch those off under File, Options, Data. A value such as 1/2 still becomes a date in every version.

The seven changes

1. Leading zeros disappear

02134 is a postcode in Massachusetts. Excel reads it as the number 2,134 and shows 2134. The same happens to phone numbers, product codes such as 000871, and account numbers. There is no undo once the file is saved.

2. Long numbers lose their last digits

Excel stores 15 significant digits. A 16-digit card or reference number such as 1234567890123456 is displayed as 1.23457E+15 and stored as 1234567890123450. The last digit has become a zero. Any identifier longer than 15 digits is damaged this way, and outside Microsoft 365's optional warning nothing on screen says so.

3. Anything that looks like a date becomes one

1/2 becomes 1 February or 2 January depending on the computer's region. 3-4 becomes a date. Product codes such as MAR1 and DEC5 become dates. The best-known case is in biology, where gene symbols such as SEPT1 and MARCH1 were turned into dates so often that a 2016 study in Genome Biology found the error in about one in five published papers with gene lists, and in 2020 the HGNC renamed those genes (to SEPTIN1 and MARCHF1) to stop it.

4. Accented and non-Latin characters turn into symbols

A CSV saved as UTF-8, which is what nearly every program produces today, opens in Excel as é where é should be, unless the file starts with a byte-order mark. Excel assumes the old Windows code page for a file that lacks that mark. Names, addresses and product descriptions in any language other than English are affected.

5. The whole row lands in column A

In regions where the decimal separator is a comma (Germany, France, Spain, Italy, the Netherlands and others), Excel expects columns to be separated by semicolons. A comma-separated file opened there shows every row as one long cell in column A. The reverse happens to a semicolon file opened in the United States or United Kingdom.

6. Rows beyond 1,048,576 are dropped

Excel's grid has 1,048,576 rows. A file with more rows opens with a message that not everything was loaded, and the extra rows are simply absent. Saving at that point writes a file with the rows gone.

7. Saving rewrites everything in Excel's own style

Even if a file opened correctly, Save As CSV writes dates in the regional format, removes quotes that were there for a reason, changes line endings, and writes numbers with the precision Excel holds rather than the text that was in the file. The file that comes out is not the file that went in.

How to open a CSV without any of this

Do not double-click the file. Import it, and tell Excel the types.

1. Open a blank workbook.
2. On the Data tab choose From Text/CSV (Excel 2016 and later, and Microsoft 365).
3. Pick the file. In the preview, set File Origin to 65001: Unicode (UTF-8) if characters look wrong.
4. Choose Transform Data. In the editor that opens, right-click each column that holds codes, identifiers or anything with leading zeros, and set its type to Text. Set the delimiter if the columns did not split.
5. Choose Close & Load.

Excel now keeps the original text exactly, because you told it not to guess. Keep the original CSV file untouched, and export a copy when you need one.

Two further habits help:

• When you save a CSV from Excel, choose the format named CSV UTF-8 (Comma delimited). It writes the byte-order mark, so the file opens correctly next time.
• When a file is too large to open, or you only need to look and search, open it in a viewer that does not load it into a grid. The large CSV viewer on this site reads the file in place, shows tab and semicolon files correctly, and has no row limit.