Why opening a CSV in Excel goes wrong
A CSV file is plain text with no information about types or encoding. When you double-click one, Excel has to guess both, and the guesses quietly change your data:
- Leading zeros disappear. Customer codes, PIN codes, ZIP codes and phone numbers such as 0110001 become 110001.
- Long numbers are rounded. Excel stores 15 significant digits, so a 16-digit card or account number becomes something like 1.23457E+15, and the last digit is replaced with a zero even after you change the format.
- Characters break. A UTF-8 file without a byte order mark is often read with the wrong encoding, so “José” becomes “José”, “₹” becomes “₹” and Hindi or Gujarati text turns into symbols.
- Text turns into dates. Values such as “1-2” or “MAR1” are converted to dates.
- Everything lands in one column. If the file uses semicolons but your computer’s list separator is a comma (or the other way round), Excel does not split the columns.
Once the workbook is saved, these changes are permanent.
How to import a CSV correctly in Excel
From Text/CSV
- Open a blank workbook and choose Data › From Text/CSV.
- Select the file. In the preview, set File Origin to 65001: Unicode (UTF-8) if the characters look wrong, and check the Delimiter.
- Set Data Type Detection to Do not detect data types so codes keep their zeros, or click Transform Data and change the code columns to Text.
- Click Load.
This imports the data as a table linked to the file. Right-click the table and choose Table › Convert to Range if you want a plain sheet.
The legacy Text Import Wizard
If you prefer the older wizard, enable it under File › Options › Data › Show legacy data import wizards, then use Data › Get Data › Legacy Wizards › From Text (Legacy). In step 3, select each code column and set Column data format to Text.
Turn off automatic conversions
Recent versions of Excel for Microsoft 365 include File › Options › Data › Automatic data conversion, where you can stop Excel removing leading zeros, shortening long numbers and converting text to dates. It only affects files you open after changing it.
Where this converter helps
Exports from banking, payroll and e-commerce systems. These often contain account numbers, order IDs and PIN codes that must keep every digit.
Customer lists in several languages. Names in Hindi, Gujarati, Spanish or French come through intact, whether the file was saved as UTF-8 or as Windows-1252 by an older program.
CSVs from European systems. Files that use semicolons as separators and commas as decimal marks are split into the right columns.
Sharing with people who do not use CSV. An .xlsx file opens the same way on every computer, without import dialogs.
How the conversion works
- Encoding is detected from the bytes: UTF-8 with or without a byte order mark, or Windows-1252.
- The separator is detected from the first lines: comma, semicolon, tab or pipe. Quoted values containing separators or line breaks are handled.
- Values are read as text first, exactly as written. Then, if “Store numbers as numbers” is on, plain numbers such as 1250 or -3.5 become real numbers.
- Anything that would lose information stays text: values with leading zeros, numbers with commas or currency symbols such as 1,00,000 or ₹ 2500, and anything longer than 15 digits.
- Dates stay as written. To turn text dates into real dates afterwards, select the column in Excel and use Data › Text to Columns, choosing the date order (for example DMY) in the last step.
The workbook has one sheet named after your file. Formatting, colours and formulas do not exist in CSV, so there is nothing to lose there.
Troubleshooting
Everything is still in one column. The file may use a separator other than the four detected ones, or contain only one column. Open it in a text editor such as Notepad to see what separates the values.
Some characters still look wrong. The converter reads UTF-8 and Windows-1252, which covers almost every CSV. Files saved in other encodings, such as UTF-16 or Shift-JIS, should be re-saved as UTF-8 in a text editor first.
Numbers with commas are still text. Values such as 1,00,000 or ₹ 2500 are kept exactly as written so nothing is lost. In Excel, a formula such as =VALUE(SUBSTITUTE(A2,",","")) turns grouped numbers like these into real numbers; remove currency symbols first with Find and Replace (Ctrl+H).
The preview shows an extra empty column. A trailing separator at the end of every line creates one. Run Remove blank rows afterwards; it also removes columns with no header and no values.
Going the other way
To turn a workbook back into a CSV that opens correctly everywhere, use the Excel to CSV converter. For old .xls workbooks, the XLS to XLSX converter upgrades them to the modern format.