Why does my CSV look broken when I open it in Excel?

You export a customer list as a CSV, double-click it, and Excel opens something that looks damaged. José has become José. The customer IDs have lost their leading zeros. The sixteen digit order numbers now read 4.00012E+15. Some dates have their day and month swapped, and on some machines the entire file sits in column A. The instinct is to export it again, or hunt for a different export option. That does not help, because the file was fine. The damage happens on the way in, and it happens the same way every time.

Last week I watched a file get exported, opened, deleted and exported again, and then again, on the advice that it must have come out wrong, before anybody opened it in a text editor. It had not come out wrong. I was irritated enough by the whole thing to write this down properly, so fair warning, it is more thorough than it strictly needs to be.

Look at the bytes, not at Excel

A CSV is text with commas in it. There is no field anywhere in the file saying which character encoding it uses, which character separates the columns, or which columns are numbers and which are labels that happen to be made of digits. Every program that opens a CSV has to guess all of that, and Excel guesses from your computer's settings rather than from the file.

So the first move is to stop looking at what Excel shows you and look at what the file contains. I built a small test export for this: three customers with accented names, IDs with leading zeros, sixteen digit order numbers and a date column.

with open("export.csv", "rb") as f:
    head = f.read(64)
print("BOM:", head.startswith(b"\xef\xbb\xbf"))
print(head)

BOM: False
b'name,customer_id,order_number,ordered\nJos\xc3\xa9 Mart\xc3\xadnez,00417,4000'

That is a healthy file. The é in José is stored as the two bytes \xc3\xa9, which is correct UTF-8. The zeros are there. The order number is all there. Everything that looks broken in Excel is intact on disk.

Why the names come out garbled

Look at the first line of that output again. A byte order mark, or BOM, is three bytes at the very start of a file, EF BB BF. It carries no text. Its only job is to say "this file is UTF-8". When Excel on Windows opens a CSV by double-click and finds no BOM, it reads the file in the computer's older default encoding, which on an English or Western European machine is usually Windows-1252. In that encoding every byte is one character, so the two bytes of é turn into two separate characters.

for name in ["José Martínez", "Zoë Brennan"]:
    print(name.encode("utf-8").decode("cp1252"))

José Martínez
Zoë Brennan

That is the garbling Excel shows you, exactly. Nothing was corrupted. Two programs disagreed about what the same bytes meant.

This is a tangent but it is the whole problem in miniature. The first time I ran that snippet, my terminal printed both names perfectly, because it was quietly guessing too, in the opposite direction, and its guess undid the first one. I spent a few minutes convinced my code was wrong before I forced the output to UTF-8 and the garbling appeared. Two wrong guesses cancelling out on one screen, and on nobody else's, is how a lot of these bugs survive long enough to reach a customer.

Why everything lands in one column

The separator is guessed from a Windows regional setting called the list separator. In many locales that write decimals with a comma, which covers much of continental Europe, the list separator is a semicolon, so Excel splits on semicolons and a comma separated file arrives as one wide column. Send a semicolon file the other way and the same thing happens in reverse. You can find out what a file actually uses without opening Excel at all. Here it is run against the test export and a semicolon version of the same file.

import csv

def delimiter_of(path):
    with open(path, encoding="utf-8-sig", newline="") as f:
        return csv.Sniffer().sniff(f.read(4096), delimiters=",;\t|").delimiter

print(repr(delimiter_of("export.csv")), repr(delimiter_of("export-semicolon.csv")))

',' ';'

Find the columns Excel will change

The garbled names are at least obvious. The type conversions are worse, because the results look plausible. On the way in, Excel converts anything that resembles a number or a date. IDs lose their leading zeros. Numbers with twelve or more digits are displayed in scientific notation, and because Excel keeps only fifteen significant digits, a sixteen digit card or order number has its last digit replaced with a zero the moment it loads. A date like 03/04/2026 is read in your locale's order, so the same cell means the third of April to one person and the fourth of March to another.

I run this on anything before it goes near a spreadsheet, so I know which columns need protecting before they need rescuing.

import re

def risky_columns(path):
    with open(path, encoding="utf-8-sig", newline="") as f:
        rows = list(csv.DictReader(f, delimiter=delimiter_of(path)))
    flags = {}
    for col in rows[0]:
        values = [r[col] for r in rows if r[col]]
        if any(re.fullmatch(r"0\d+", v) for v in values):
            flags[col] = "leading zeros will be dropped"
        elif any(re.fullmatch(r"\d{16,}", v) for v in values):
            flags[col] = "over 15 digits, the rest become zeros"
        elif any(re.fullmatch(r"\d{12,15}", v) for v in values):
            flags[col] = "shown in scientific notation"
        elif any(re.fullmatch(r"\d{1,2}/\d{1,2}/\d{2,4}", v) for v in values):
            flags[col] = "will be read as a date, in your locale's order"
    return flags

for col, why in risky_columns("export.csv").items():
    print(f"{col:14} {why}")

customer_id    leading zeros will be dropped
order_number   over 15 digits, the rest become zeros
ordered        will be read as a date, in your locale's order

It is deliberately crude. It flags a column if any single value in it matches, which will sometimes flag a column that turns out to be fine. I would rather check a false alarm than hear about a real one from a customer.

Dates are the worst of the four

Garbled names are obvious and lost zeros are at least consistent. Dates fail in a way that looks like success, which is why they get their own section.

Excel stores a date as a number, a count of days, and formats that number to look like a date. When it reads 03/04/2026 on a machine set to month first, it stores the fourth of March. On a machine set to day first, it stores the third of April. Both are perfectly valid dates, nothing on screen looks wrong, and the value in the cell is now a different day from the one the file meant.

It gets stranger. A value like 13/04/2026 cannot be a month first date, because there is no thirteenth month, so on a month first machine Excel gives up and leaves it as text. The same column ends up holding real dates for every row where the day was twelve or under, and text for the rest. Sort it and the order is nonsense. Filter it by month and a chunk of the rows quietly vanish. I have watched this take a reporting sheet apart without producing a single error message, which is the part that bothers me most.

The fix, when you control the file, is to write dates year first. 2026-04-03 means one day in every locale, and Excel reads it as that day wherever the file is opened. When you receive a file in day first or month first order, find out which it is from whoever produced it, because nothing inside the file can tell you, and then convert it deliberately:

from datetime import datetime

def to_iso(value, order="%d/%m/%Y"):
    return datetime.strptime(value, order).date().isoformat()

print(to_iso("03/04/2026"), to_iso("03/04/2026", "%m/%d/%Y"))

2026-04-03 2026-03-04

Same string, two different days, and the only thing deciding between them is the format you pass in. The code cannot guess the order any better than Excel can. What it can do is make you state the order out loud, once, in a place somebody can read later, which is more than a double-click ever does.

This is not only an office annoyance, either. In 2020 a set of human gene names was changed, partly because spreadsheet software kept converting them into dates, and The Verge covered the renaming at the time. When the people who name human genes change the names to get around a spreadsheet default, I think it is fair to say the default is winning.

Import it instead of opening it

1. Open Excel first, with an empty workbook. Do not double-click the file. Double-clicking is what sets all the guessing off.
2. Go to Data, then From Text/CSV, and pick the file. This opens a preview instead of loading straight into cells.
3. Set File Origin to 65001: Unicode (UTF-8). The names in the preview should correct themselves as soon as you do. If they do not, the file is not UTF-8, and it is worth finding out what it is before going any further.
4. Check the Delimiter the preview picked. If the columns look wrong, change it here rather than later.
5. Set Data Type Detection to Do not detect data types, then Load. Everything arrives as text, exactly as it was in the file. Convert the columns that really are numbers afterwards, one at a time, on purpose.

The order matters most at step five. It has to happen before anything loads, because the conversion is not a display setting you can undo afterwards. Once a zero has been dropped on the way in, it is gone from that workbook.

Recent Microsoft 365 builds also have an Automatic data conversion section under File, Options, Data, where the leading zero and long number conversions can be switched off. I have switched them off on my own machine. It is worth doing, and it helps nobody else, because the person you send the file to still has their own settings.

When you are the one making the file

If you produce CSVs that people will open in Excel, write them with a BOM. In Python that is one argument, utf-8-sig.

with open("export.csv", encoding="utf-8", newline="") as src, \
     open("for-excel.csv", "w", encoding="utf-8-sig", newline="") as dst:
    dst.write(src.read())

with open("for-excel.csv", "rb") as f:
    print(f.read(8))

b'\xef\xbb\xbfname,'

Those three bytes at the front are the BOM, and with them Excel reads the names correctly on a double-click. It fixes the encoding and nothing else. The zeros and the long numbers will still be converted, because the BOM says nothing at all about types.

If the people receiving the file will open it in Excel and the IDs matter, send an .xlsx instead. An .xlsx stores each cell's type alongside its value, so a column stored as text stays text and there is nothing left to guess. The metadata extractor on this site exports an .xlsx rather than a CSV, and this is the case for that choice: page titles and URLs are text, and a file that records them as text leaves Excel nothing to convert.

Before a CSV leaves my machine

I produce more of these files than I receive, so this is the part I actually use every week. It is short on purpose, because a checklist I will not read is worse than none.

• Dates are written year first, even when the person asking for the file wrote them day first in their email.
• The file is UTF-8 with a BOM if a person will open it, and without one if a script will read it. If both will, I send two files. That sounds fussy, and it has saved me more than one follow-up call.
• Any column of IDs, postcodes, phone numbers or account numbers gets mentioned in the message by name, with one line saying to import it as text rather than open it.
• If the IDs really matter, it goes as an .xlsx instead, and the whole question disappears.
• I open it once in a plain text editor before sending. Not in Excel. If it looks right as text, the file is right, and anything Excel does to it afterwards is a guess I have already warned them about.

That last habit is the one I would keep if I could keep only one. A text editor shows you the file. Every spreadsheet shows you its interpretation of the file, and most of this post has been about the gap between those two things.

What this will not handle

• The BOM can break other programs. Code that reads the file as plain UTF-8 sees the BOM as part of the first column name, so a header called name becomes name and every lookup on it fails. In Python, reading with utf-8-sig strips it. Other tools may not, so if the file feeds a script as well as a person, check the script.
• Excel on a Mac. Everything above is about Excel on Windows. The Mac versions have their own defaults and I have not checked them, so I will not pretend to know how they behave.
• Files that are not UTF-8 in the first place. Some older systems export in Windows-1252 or something else entirely. Adding a BOM to a file that is not UTF-8 tells Excel something false, and the garbling gets worse.
• Google Sheets. Its import dialog asks for the separator and has a checkbox for converting text to numbers, dates and formulas, which you can untick. It copes with UTF-8 better than a double-click in Excel does, but it has conversions of its own, and the same rule applies: import it, do not open it.