Why your CSV opened wrong in Excel, and how to fix it

Double-clicking a CSV asks Excel to guess. It splits each line on your computer's list separator, reads numbers and dates in your region's format, turns anything that looks like a number into one, and reads the bytes in a code page it assumes. When the file was made somewhere else, one or more of those guesses is wrong, and each wrong guess has its own symptom.

Which symptom, which cause

The last four are damage to the data, not just display. Close the workbook without saving: saving writes the damaged values back over the CSV.

Look at the bytes

When a file misbehaves, look at its first bytes before you guess. On a Mac or Linux, xxd prints them. This is a UTF-8 export:

xxd customers.csv | head -5

00000000: 6375 7374 6f6d 6572 5f69 642c 6e61 6d65  customer_id,name
00000010: 2c70 6f73 7463 6f64 652c 7068 6f6e 652c  ,postcode,phone,
00000020: 6f72 6465 725f 7265 662c 6a6f 696e 6564  order_ref,joined
00000030: 2c62 616c 616e 6365 0d0a 3030 3431 322c  ,balance..00412,
00000040: 5a6f c3ab 204d c3bc 6c6c 6572 2c30 3231  Zo.. M..ller,021

Three things to read off it:

• The delimiter: 2c is a comma. A semicolon is 3b, a tab 09.
• The encoding: ë is c3 ab, two bytes, so this is UTF-8. In the same file saved as Windows-1252, ë is the single byte eb (5a6f eb20 4dfc for Zoë Mü).
• The byte order mark: a UTF-8 file with a BOM starts efbb bf. This one starts 6375, cu, so it has none.

file -I gives the encoding as a guess, which is good enough to tell the two apart:

file -I customers.csv customers-1252.csv

customers.csv:      text/csv; charset=utf-8
customers-1252.csv: text/csv; charset=iso-8859-1

Import it in Excel

These steps follow Microsoft's pages on importing text files, the Power Query Text/CSV connector and data types in Power Query. We could not run Excel for this guide, so the labels are Microsoft's, from those pages.

Excel for Microsoft 365 and Excel 2024 also have Automatic Data Conversion settings (File › Options › Data on Windows, Excel › Preferences › Edit on a Mac). Turning off the options for removing leading zeros and for truncating long numbers to 15 digits makes a double-click keep those as text. Microsoft says these settings apply when you open a .csv, and not to data imported with Power Query: Set automatic data conversions. They do not change the delimiter, the encoding or the date order.

Each symptom, in more detail

Everything in one column

On Windows, a double-clicked CSV is split on the list separator in your regional settings. With US or UK settings that is a comma. In much of continental Europe, where the comma is the decimal mark, it is a semicolon, and exports from software there use semicolons too. A file and a computer that disagree give one column. Set Delimiter in the import, or see the LibreOffice and pandas options in convert CSV to Excel without breaking the data.

A semicolon file usually has decimal commas as well (1234,50). In the import, set that column's type with Using Locale and a decimal-comma locale, so it is read as 1234.5.

Leading zeros gone, and long numbers in E+ notation

Both happen because the column became a number. 00412 as a number is 412. A number holds 15 significant digits, so a 16-digit card number or a 19-digit order number is rounded, and Excel shows it in scientific notation. In our test file, three different 19-digit order numbers became the same value. Microsoft's page on keeping leading zeros and large numbers says the same: anything with 16 or more digits must be stored as text.

Formatting the cell afterwards doesn't bring the digits back. Re-import with the column as Text.

Dates flipped

04/03/2026 is 4 March in the UK and 3 April in the US, and nothing in the file says which. A double-click reads it in your region's order. A date like 31/01/2026 cannot be read month-first at all, so a column where some dates look right and others don't is the sign. Change Type › Using Locale with the file's locale reads every row the same way. Microsoft suggests checking the result by adding Day, Month and Year columns from Add Column.

Dates written as 2026-03-04 (ISO 8601) are read the same way everywhere. If you control the export, use them.

ë instead of ë

The file is UTF-8 and was read as Windows-1252. UTF-8 stores ë as two bytes, c3 ab, and Windows-1252 shows each byte as a character, so the first row of our file reads:

00412,Zoë Müller,02134,0612345678,7310000012345678901,2026-03-04,1234.50

Nothing is lost: import with File Origin set to UTF-8, or add a BOM to the file (below). The opposite case, a Windows-1252 file read as UTF-8, shows � where the accents were. That one is lossy in any program that reads it as UTF-8: re-read the original file as Windows-1252, or convert it first with iconv -f WINDOWS-1252 -t UTF-8 old.csv > new.csv.

Make a CSV that opens right

If you produce the file, three things help, and one thing no CSV can do.

Add a byte order mark. Microsoft's page on opening UTF-8 CSV files says a UTF-8 CSV saved with a BOM opens normally. In pandas it is the utf-8-sig encoding:

df.to_csv("for-excel.csv", index=False, encoding="utf-8-sig")

00000000: efbb bf63 7573 746f 6d65 725f 6964 2c6e  ...customer_id,n

To add one to a file you already have, on a Mac or Linux:

printf '\xef\xbb\xbf' | cat - customers.csv > customers-excel.csv

Use the reader's delimiter. For readers with continental European settings, write semicolons and decimal commas: df.to_csv("for-excel-eu.csv", index=False, encoding="utf-8-sig", sep=";", decimal=",") writes 1234,5 in the balance column.

Write dates as ISO 8601 (2026-03-04), which no region reads backwards.

A sep= first line, such as sep=;, tells Excel which delimiter to use. Microsoft doesn't document it, and we could not test it in Excel. What we did test is what it does to everything else: LibreOffice 25.2 imported sep= as the first row of data, and pandas 2.2.3 used it as the header, so every real column name ended up in the first data row. Our converter reads it and splits on the delimiter it names. Use it only for a file that goes to Excel and nowhere else.

What no CSV can do is say that a column is text. A file with leading zeros or long IDs keeps them only if the reader imports it with types, or has turned off Excel's automatic conversions. If the recipient will double-click, send an .xlsx with those values stored as text instead: convert CSV to Excel without breaking the data does that in one pandas script, one LibreOffice command, or the browser converter below.

In the browser

The CSV to XLSX Converter reads the file without a region's guesses and writes an .xlsx, which opens the same way on every computer:

• Delimiter: comma, semicolon, tab or pipe, whichever gives the rows the same number of columns. A delimiter inside quotes doesn't count, and a sep= first line wins.
• Encoding: a byte order mark if there is one, otherwise UTF-8, and Windows-1252 when the bytes aren't valid UTF-8. So Zoë comes out as Zoë from either kind of file.
• Numbers: anything with a leading zero, a leading + or more than 15 digits stays text. Other numbers become numbers, and a semicolon file with 1234,50 in it is read with decimal commas.
• Dates: 2026-03-04 becomes a date, the same in every time zone. 04/03/2026 stays text until you choose its order.

Once the data is in, check it the same way you found the problem: an ID cell should show its leading zeros, the formula bar should show every digit of a long number, and a date cell should change when you switch it to a long date format.