Why is my CSV not opening correctly in Excel

Last updated August 2026

The file is probably fine

This is the most useful thing to know up front. In the large majority of cases the CSV is well formed and Excel is misreading it. A CSV carries no metadata: it does not declare its delimiter, its encoding, or the type of any column. Excel has to guess all three, it guesses using your machine settings rather than anything in the file, and it does it silently.

That is why the same file opens perfectly for a colleague and mangles for you, and why it looks correct in a text editor. Confirm the file is sound first, by opening it in any plain text editor or by running it through the CSV to JSON Converter, which parses the actual structure and shows you the fields it found. If that looks right, the fix belongs in how you open the file, not in the file.

Symptom, cause and fix at a glance

Cause 1: the delimiter is not what you think

The name says comma-separated, but Excel does not read it that way. On Windows it splits using the List separator from Windows regional settings. In the UK and US that is a comma; across much of Europe it is a semicolon, because the comma is the decimal mark there. Open a comma-separated file on a semicolon machine and there is nothing to split on, so all the data lands in one column.

There are three ways out, in increasing order of robustness:

• Import instead of open. Data, then From Text/CSV, and choose the delimiter explicitly. This changes nothing on disk.
• Declare it in the file. Make the first line sep=, and Excel will honour it. Note this is an Excel-specific convention and other parsers will read that line as data.
• Match the recipient. If a file is going to European colleagues, exporting semicolon-separated will save everyone the trouble.

Cause 2: encoding, and the byte order mark

If names and addresses come through as é or ’, the file is UTF-8 and Excel decoded it as a single-byte legacy codepage such as Windows-1252. Each multi-byte UTF-8 character gets shown as the two or three separate characters its bytes happen to mean in that codepage.

Excel will detect UTF-8 correctly if the file begins with a byte order mark, the three bytes EF BB BF. Most tools that write CSV do not add one by default, because for every other consumer the BOM is noise. So the two reliable fixes are to export as UTF-8 with BOM specifically for Excel, or to import through Data, From Text/CSV and set File Origin to 65001: Unicode (UTF-8).

Modern Excel is better at this than it was, but it is still a guess. Never rely on it for a file you are handing to someone else.

Cause 3: Excel rewriting your data

This is the damaging one, because it silently changes values rather than just displaying them oddly, and once you save the file the originals are unrecoverable. Excel assigns a type to each column as it loads:

• 007 becomes the number 7, so zero-padded reference codes lose their padding.
• 03-05 becomes a date, and which date depends on your locale.
• Gene names and product codes such as SEPT1 or MAR2 become dates. This is a well-known enough problem in genomics that some gene symbols were renamed because of it.
• Long numeric identifiers past 15 digits lose precision, because they are stored as floating point.
• A value such as 1/2 can become a date or a fraction.

The only dependable defence is to import and set the affected columns to Text before the data loads. Doing it afterwards does not undo the damage: reformatting a cell that already holds a date just shows you the date's serial number.

Cause 4: quoting and embedded newlines

If rows break in unexpected places, a field probably contains the delimiter, a quote, or a line break without being escaped properly. The CSV convention is that a field containing a comma, a double quote or a newline must be wrapped in double quotes, and any double quote inside it must be doubled.

id,name,notes
1,"Smith, John","He said ""hello"" twice"
2,"Jane Doe","Line one
line two"

Both of those rows are valid. Hand-built exports, especially ones assembled with string concatenation, frequently get this wrong and produce a file that is genuinely broken rather than merely misread. Running it through the CSV to JSON Converter tells you which case you are in: if the parser recovers clean records, the file is fine and Excel is the problem; if the records come out ragged, the export is at fault.

The reliable way to open any CSV

1. Do not double-click the file.
2. In Excel, go to Data, then From Text/CSV.
3. Set File Origin to 65001: Unicode (UTF-8).
4. Set the delimiter explicitly rather than accepting the detected one.
5. Choose Transform Data, and set every identifier, code or zero-padded column to Text.
6. Load.

It is slower than double-clicking once, and it is the only route that does not silently rewrite your data.

If you are the one producing the file

• Write UTF-8 with a BOM if Excel is a known consumer.
• Quote every text field rather than only the ones that need it.
• Use ISO dates (YYYY-MM-DD), which survive locale differences.
• Prefer a real spreadsheet format over CSV when types matter. CSV cannot carry them.
• Do not emit a trailing delimiter at the end of a row.

For related problems, the unexpected token JSON errors guide covers the equivalent parsing traps in JSON, and why is my base64 decode garbled covers the same class of encoding mismatch. More fixes are in the Tools Nimbus guides.