Why CSV Files Open Wrong in Excel — and How to Stop It
Leading zeros vanish, dates flip month and day, accents turn to nonsense and everything lands in one column. Each failure has a cause and a fix that takes seconds.
Updated August 28, 20268 min read
A CSV file is a text file. It contains no formatting, no types and no instructions — just characters. Everything you see when it opens in a spreadsheet is a guess made by the program about what those characters were supposed to mean, and most of the ways a CSV appears to be corrupted are actually the guess going wrong. The file is usually fine. The interpretation is not.
Everything lands in column A
The most common failure, and the easiest to explain. CSV stands for comma-separated values, but Excel does not use the comma. It uses whatever your operating system has configured as the list separator, which follows your regional settings — and across much of Europe and South America that is a semicolon, because the comma is already the decimal separator.
So a comma-separated file opened on a machine configured for semicolons contains no separators as far as Excel is concerned, and each line becomes one long cell. Nothing is broken; the file simply used a different convention. Opening the same file on a colleague's machine in another country works perfectly, which is why this problem is so often reported as intermittent.
- Import rather than open. Data ▸ From Text/CSV lets you choose the delimiter explicitly instead of guessing. This is the fix that always works.
- Or add a separator hint. A first line reading
sep=,tells Excel which character to use. It is a Microsoft extension, so other programs display it as a stray row. - Or convert the file first. Converting CSV to Excel produces a real spreadsheet with the columns already split, and no import dialogue at all.
- Check what you actually have. The CSV viewer shows the parsed structure and the delimiter it detected, which takes ten seconds and often ends the investigation.
Leading zeros disappear
A postcode of 01234 becomes 1234. A phone number of +44 20 7946 0958 turns into something unrecognisable. A product code of 0012-A survives while 001234 does not. The cause is that Excel examines each value, decides it looks like a number, and converts it — and numbers do not have leading zeros.
This is not recoverable by reformatting the column afterwards. The conversion happened at import; setting the cell format to text later displays the number as text, but the zeros are already gone. The fix is to tell Excel the column is text before it reads it: in the import dialogue, or — in Excel for Microsoft 365 and Excel 2024 — by turning off Remove leading zeros and convert to number under File ▸ Options ▸ Data ▸ Automatic Data Conversion, which also applies to a file opened with a double-click.
There is a related and nastier version of the same problem. Long numeric identifiers — sixteen-digit card references, some barcodes — exceed the fifteen significant digits a spreadsheet stores precisely, so the final digits become zeros. The value looks plausible and is silently wrong, which is considerably worse than an obvious error. In Excel for Microsoft 365 and Excel 2024, turning off Keep the first 15 digits of long numbers in the same Automatic Data Conversion settings keeps such values as text, every digit intact.
Dates change meaning
The date column is where CSV import does the most damage, because the results are wrong rather than merely ugly and they look entirely normal.
| In the file | What Excel may decide | Why |
|---|---|---|
03/04/2026 | 3 April or 4 March | Depends entirely on regional settings |
2026-04-03 | Usually correct | ISO 8601 is unambiguous — use this |
MAR-3 | 3 March of the current year | A year you never supplied |
1-2 | 1 February | It was a range, or a section number |
SEPT1 | 1 September | A gene name, famously |
3.4 | 3 April, in some locales | A decimal number, silently converted |
The first row is the dangerous one, because both interpretations are valid dates and nothing looks wrong. A file exported in one country and opened in another can have every ambiguous date in it shifted, with the dates above the twelfth of the month left correct — producing a dataset that is right two thirds of the time, which is far harder to notice than one that is wrong throughout.
The cure is ISO format everywhere: 2026-04-03, year first. It cannot be misread in any locale, it sorts correctly as plain text, and every spreadsheet and database accepts it. If you generate CSV files for other people, this single choice removes more support requests than anything else you can do.
Get typed columns and intact identifiers without the import dialogue — converted on your device, never uploaded.
Convert CSV to ExcelAccented characters turn into nonsense
Names come through as José instead of José, or the whole file is peppered with question marks and boxes. This is an encoding mismatch: the file was written as UTF-8, which is the modern standard and handles every language, and Excel opened it assuming the legacy Windows codepage of your region.
There are two workable fixes. Import through Data ▸ From Text/CSV, where you can select UTF-8 explicitly. Or write the file with a byte order mark at the start — three invisible bytes that identify it as UTF-8 — which makes Excel do the right thing on a plain double-click. The second option is why so many export tools offer a confusingly named 'CSV UTF-8 (with BOM)' choice.
Rows split in the wrong places
A CSV that contains commas, line breaks or quotation marks inside its values has to escape them, by wrapping the value in double quotes and doubling any quote inside it. When that is done incorrectly — usually by an export written by hand rather than by a proper library — the parse goes wrong at that point and stays wrong for the rest of the file.
- A comma inside an address splits one field into two, and shifts every column after it on that row.
- A line break inside a comment field turns one record into two, one of which has too few columns.
- An unescaped quotation mark — a measurement in inches, a nickname — makes the parser treat the rest of the line as quoted text.
- Mixed line endings from files edited on different systems can produce phantom blank rows between every real one.
- A stray apostrophe in a name is harmless in a proper CSV and breaks anything built with string concatenation.
The escape fix tool repairs broken quoting rather than merely reporting it, which is the difference between a fixed file and a bug report. It is worth running on any CSV that came out of a system you do not control, before it reaches a spreadsheet at all.
A workflow that avoids all of this
Look at the file before opening it
The CSV viewer shows the delimiter, the encoding and the parsed columns without a spreadsheet interpreting anything. Thirty seconds here tells you which of the problems above you are about to have.
Convert rather than import
Converting CSV to Excel produces a workbook with columns already split and identifiers kept as text, so there is no dialogue to configure and no regional setting to fight.
Or import deliberately if you must use Excel directly
Data ▸ From Text/CSV rather than a double-click. Set the delimiter, set the encoding to UTF-8, and mark every identifier column as Text before loading. Turning off Excel's automatic data conversions (File ▸ Options ▸ Data, in Microsoft 365 and Excel 2024) makes a double-click keep leading zeros and long numbers too, but does nothing for the delimiter, the encoding or the order of day and month.
Check the columns that always break
Postcodes, phone numbers, dates and any code with a leading zero. Compare the first and last few rows against the source file rather than trusting the middle.
Export as UTF-8 with ISO dates
When it is your turn to produce the file, export to CSV with UTF-8 encoding and dates written year-first. You will spare somebody else this entire article.
One point about where this happens. CSV exports are frequently the most sensitive files an organisation handles — customer lists, payroll, orders, medical records — and uploading one to an online converter discloses it, whatever the policy says afterwards. Every tool here parses and rewrites the file on your own device, so there is no upload and no server-side copy to worry about.
Frequently asked questions
Why does my CSV open all in one column in Excel?
Because Excel uses your regional list separator rather than always using a comma, and in many locales that separator is a semicolon. A comma-separated file then appears to contain no separators at all. Import through Data ▸ From Text/CSV and choose the delimiter explicitly, or convert the file to a real spreadsheet first.
How do I stop Excel removing leading zeros from a CSV?
Mark the column as Text in the import dialogue before the data loads. Reformatting afterwards does not help, because the conversion to a number already discarded the zeros. In Excel for Microsoft 365 and Excel 2024 you can instead turn off Remove leading zeros and convert to number under File ▸ Options ▸ Data ▸ Automatic Data Conversion, and a double-clicked CSV then keeps them. Postcodes, phone numbers and account codes should always be imported as text.
Why do dates change when I open a CSV?
Ambiguous formats like 03/04/2026 are interpreted according to regional settings, so the same file reads as 3 April in one country and 4 March in another. Writing dates in ISO format — 2026-04-03 — removes the ambiguity entirely and sorts correctly as plain text.
How do I fix accented characters in a CSV?
The file is UTF-8 and Excel assumed a legacy codepage. Reopen it through Data ▸ From Text/CSV with UTF-8 selected, or save the file with a UTF-8 byte order mark so a double-click works. If the characters appear as question marks rather than odd symbol pairs, the conversion already happened and you need the original file back.