Why Does My CSV Open Wrong in Excel?

You export data from a system, double-click the CSV, and Excel presents you with one column of crammed-together text. Or the names have turned into José and Müller. Or every postcode has lost its leading zero and half the product codes have become dates.

None of this is corruption. A CSV is a plain text file containing no information about how it should be read, so Excel guesses — at the delimiter, at the character encoding, and at the type of every column. It makes those guesses using your Windows regional settings rather than anything in the file, which is why the same file behaves differently on two machines.

The fix for all of it is the same: stop double-clicking, and import instead.

Which symptom do you have?

Four of those six are fixed by the same action, which tells you something about where the problem really is.

The right way to open a CSV

Do not double-click it. Open Excel to a blank workbook, then:

1. Data → From Text/CSV (older versions: Data → Get External Data → From Text).
2. Select the file. A preview window opens.
3. Set File Origin to 65001: Unicode (UTF-8) unless you know it is something else.
4. Set Delimiter to whatever the file uses — comma, semicolon or tab. The preview updates live, so you can see when it is right.
5. Click Transform Data rather than Load, which opens the Power Query editor.
6. In the editor, click each column that holds an identifier, a postcode, a phone number or anything else that is not really a number, and set its type to Text.
7. Close & Load.

Step six is the one people skip, and it is the one that prevents the irreversible damage.

Why Transform Data rather than Load

Loading directly applies Excel’s type detection, and some of that detection is destructive. A column of values like 00123 loaded as a number becomes 123, and the leading zeros are not stored anywhere. Reformatting the column as text afterwards gives you 123 displayed as text, not 00123.

The same applies to date conversion. 3-4 imported as a date becomes a date serial number, and the original string is gone. There is no undo for this once the file is loaded and saved.

Setting the column type to Text before loading is the only reliable prevention.

The encoding problem in detail

Text has to be stored as bytes, and the mapping between bytes and characters is the encoding. UTF-8 is the modern standard and handles every language. Older single-byte encodings such as Windows-1252 handle about 250 characters and nothing else.

When Excel reads a UTF-8 file as Windows-1252, each multi-byte character is displayed as the two or three single-byte characters its bytes happen to represent. é is two bytes in UTF-8, and those two bytes read individually are Ã and ©. Hence José.

The BOM

A byte order mark is a three-byte sequence at the very start of a file announcing that it is UTF-8. It is invisible in any text editor and Excel uses it to detect the encoding automatically.

Many systems export UTF-8 without a BOM, because for most software a BOM is unnecessary and occasionally unwelcome. Excel is the notable exception: without it, Excel falls back to your regional encoding and mangles the file.

To add one, open the file in Notepad, choose File → Save As, and set the Encoding dropdown to UTF-8 with BOM. In Notepad++, use Encoding → Convert to UTF-8-BOM. The file then opens correctly on a double-click, which is worth doing if the same export arrives weekly.

If you control the export, “UTF-8 with BOM” is the setting to ask for when the consumer is Excel, and plain UTF-8 for everything else.

The delimiter problem

The C in CSV stands for comma, but a large part of the world uses a comma as the decimal separator and therefore uses a semicolon to separate fields.

Excel picks its expected delimiter from the Windows list separator setting, not from the file. A file produced in Germany opens as a single column in the UK, and a British file does the same in Germany, with no indication of why.

Three ways to deal with it:

• Set it during import, as above. Best for a one-off.
• Change the Windows list separator under Control Panel → Region → Additional settings. This affects every CSV on the machine and also changes the separator Excel uses in formulas, which surprises people.
• Add a sep= line. Putting sep=; on the very first line of the file tells Excel the delimiter explicitly, overriding regional settings. It is a Microsoft-specific convention and other tools will read that line as data, so it only suits files that are going to Excel and nowhere else.

Tab-separated files avoid the issue entirely, which is why many systems offer them as an alternative export.

Rows that split in the wrong places

If a field contains a line break — a multi-line address, a comments field — a correctly formed CSV wraps that field in quotes, and the line break inside the quotes is data rather than a row separator.

Excel’s import handles this correctly. Some simpler text-splitting methods do not, which is another reason to use the proper import path rather than opening and using Text to Columns.

If rows are splitting unpredictably, open the file in a text editor and look at the problem row. An unmatched quote character anywhere in the file throws off everything after it, and finding it manually is usually quicker than fighting the import.

Saving back out

Excel’s Save As → CSV UTF-8 (Comma delimited) is the option you want. The plain CSV (Comma delimited) option uses your regional encoding and will mangle anything outside it, which is how files get progressively worse each time they pass through a workflow.

Be aware that saving as CSV discards everything that is not cell values — the formatting, the formulas, every sheet except the active one. Excel warns you, and the warning is accurate.

Also be aware that any damage done on import is now baked in. A postcode that lost its zero on the way in is saved without it on the way out, and anyone downstream inherits the problem.

What to expect

Importing with the encoding and delimiter set explicitly, and identifier columns marked as Text, resolves essentially every case of this. It takes about forty seconds once you know the path, against the fifteen minutes of confusion that double-clicking produces.

The limitation worth knowing is that damage is one-way. Excel’s auto-conversion destroys information rather than hiding it, so if you have already opened and saved a file with leading zeros stripped or codes turned into dates, the only real fix is to go back to the original export and import it properly. There is no formula that reconstructs what was discarded.

For data that genuinely matters, keep the original CSV untouched and work on a copy. If you handle data files often, our guide to opening files when you do not have the program covers the broader category of format problems, and the JSON formatter is useful when the export arrives as JSON rather than CSV.