Open a CSV in Excel without garbled text or lost zeros

What a CSV is, and why it is so fragile

A CSV is a plain-text file where each line is a row and the columns are separated by a character, usually a comma or a semicolon. It carries no information about the text encoding, about which delimiter it uses, or about whether “007” is a number or a code. Those decisions are made by the program that opens it, which is why the same file looks fine in one place and broken in another.

Problem 1: accents come out as “é”, “ñ”…

This is an encoding problem. The file is saved as UTF-8 (the standard of the web, where “é” takes two bytes) and Excel, when you double-click it, often reads it with an older one-byte-per-character encoding, so each accented letter turns into two odd symbols. The data is not damaged.

The fix is not to double-click the file but to import it and tell Excel what encoding it uses:

1. Open Excel with a blank workbook and go to the Data tab.
2. Click From Text/CSV, choose the file and import.
3. In the preview, if the text looks wrong, change File Origin to 65001: Unicode (UTF-8).
4. Click Load.

This is the answer that keeps turning up in Microsoft's own community threads on the problem. If you are the one creating the CSV, many applications offer “CSV UTF-8” as a save option, which Excel recognises more reliably.

Problem 2: everything lands in a single column (or splits in the wrong places)

The delimiter is to blame. Excel uses the list separator defined in your Windows regional settings. In countries where the comma is the decimal separator (3,14), that list separator is usually the semicolon. So a comma-separated CSV opens in a single column on such a machine, and a semicolon-separated one opens fine. The reverse happens on a machine set to US or UK regional settings.

There are two fixes:

• At import time (Data → From Text/CSV), pick the right delimiter from the dropdown; the preview updates instantly.
• Change the Windows list separator (Region → Additional settings → Numbers tab → “List separator”). It affects every program, so it only makes sense if you always work with the same kind of file.

If you are sending the file to other people, think about their settings: a semicolon CSV works well in a continental-European Excel and badly in an English one. To avoid surprises, some people simply send an .xlsx instead.

Problem 3: leading zeros disappear

“00123” becomes “123” because Excel sees digits and assumes a number, and numbers do not have leading zeros. It happens with postcodes, phone prefixes, product references and IDs. The fix is to set that column to Text during import: in From Text/CSV, click Transform Data, select the column and change its data type to Text before loading. If you already opened the file and the zeros are gone, open it again through the import: the original text file still has them.

Problem 4: long numbers get rounded or shown in scientific notation

Excel stores numbers with a precision of 15 digits, according to its own specifications. A number with 16 or more digits (a long ID, a card number, an 18-digit barcode, a system identifier) loses everything after the fifteenth digit, which is replaced by zeros, and Excel may also display it in scientific notation (1.23E+15). Long numbers that are identifiers rather than quantities should always be treated as text, just like leading zeros.

Excel's limits

Excel tops out at 1,048,576 rows and 16,384 columns per worksheet, and 32,767 characters per cell. If you open a CSV with more rows, Excel only loads the first ones and the rest never appears on the sheet, so be careful with large exports: do not run an analysis of “all the data” without first checking how many rows the file has. For those cases a viewer or a tool without that cap is a better fit.

Other common traps

• Dates that change format. Excel reads “03/04/2026” according to your regional settings (3 April or 4 March?) and may turn a date-looking string into a real date. Import that column as text if you do not want it reinterpreted. To avoid ambiguity, use the ISO format (2026-04-03).
• Commas inside a field. A name like “Smith, Ann” needs quotation marks so it does not split into two columns. A program that generates the CSV usually adds them; if you type it by hand, add them yourself.
• Line breaks inside a cell can split rows when the CSV is opened in a program that does not support them.
• Saving back to CSV loses formatting, formulas and multiple sheets. Always keep an .xlsx copy.

How to check a CSV before opening or sending it

To see what the file really contains, without any program interpreting anything, use the CSV viewer: it detects the delimiter and shows the table and any problem lines. If the file has stray spaces, blank lines or duplicate rows, the CSV cleaner tidies it up before you import. And if your data is in JSON, you can turn it into a table with JSON to CSV. All three run in your browser: a CSV with customers, payroll or orders does not have to be uploaded to a server.

If the CSV comes from a JSON export or an API, the guide to why your JSON is invalid covers the usual errors when preparing it. And if the file has dates or week numbers, how ISO week numbers work explains why Excel can give a different number from other systems.

Sources and further reading

• Microsoft: Import or export text (.txt or .csv) files, delimiters and list separator
• Microsoft: Excel specifications and limits (15-digit precision, rows and columns)
• Microsoft Q&A: UTF-8 encoded CSV file opening garbled (fix using origin 65001)

Figures checked on 3 October 2026.