How to Open a CSV File in Excel Without Breaking Your Data

CSV files are everywhere: bank statements, website orders, mailing lists, exports from almost any system. They're plain text with commas between the values, and Excel opens them happily. Too happily, in fact. Double-click a CSV and Excel guesses what every column is, and it often guesses wrong.

• Leading zeros vanish. Phone numbers and codes like 01234 become 1234.
• Long numbers get mangled. Card numbers, barcodes and order IDs turn into 1.23457E+15, and anything over 15 digits loses its last digits for good.
• Dates get confused. UK and US dates swap day and month, and codes like MAR1 or 1-2 turn into dates.
• Accented letters turn into gibberish. é becomes é, and £ becomes £.

And if you then save the file, those changes are permanent.

1. Open a blank workbook in Excel.
2. Go to Data > From Text/CSV (in the Get & Transform Data group).
3. Select your CSV file and click Import.
4. A preview window opens. Check:
• File Origin: choose 65001: Unicode (UTF-8) if any characters look wrong.
• Delimiter: usually Comma. Some files use semicolons or tabs.
• Data Type Detection: choose Do not detect data types to keep everything exactly as it is in the file. Or leave it and fix individual columns in the next step.
5. Click Transform Data to choose column types, or Load if the preview looks right.

Clicking Transform Data opens the Power Query Editor. For each column that needs protecting:

1. Click the small icon at the left of the column heading (it shows ABC, 123 or a calendar).
2. Choose Text for phone numbers, postcodes, IDs and codes.
3. For dates, choose Using Locale…, then Date and the country the dates came from, for example English (United States) for month-first dates.
4. Click Close & Load.

Your data appears as a table, with every zero and digit intact.

The import remembers where the file came from. If you get an updated CSV with the same name in the same place, just go to Data > Refresh All, and the new data loads with the same settings. Great for weekly exports.

If you prefer the classic wizard:

1. Go to File > Options > Data.
2. Under Show legacy data import wizards, tick From Text (Legacy).
3. Now use Data > Get Data > Legacy Wizards > From Text (Legacy).

In step 3 of the wizard you can set each column to Text or Date before anything is changed.

Newer versions of Microsoft 365 let you stop Excel changing data when it opens CSV files:

1. Go to File > Options > Data.
2. Under Automatic Data Conversion, untick the options you don't want, such as Remove leading zeros, Keep first 15 digits of long numbers, and Convert continuous letters and numbers to a date.
3. Click OK.

If you don't see this section, your version doesn't have it yet, so import the file instead.

If you need to send the file back as a CSV, choose File > Save As and pick CSV UTF-8 (Comma delimited). Only the current sheet is saved, and all formatting and formulas are lost.

All the data is in column A. The file uses a different separator, like semicolons. Use Data > Text to Columns and choose the right delimiter, or re-import and set the Delimiter.

Strange characters like é or £. The file is UTF-8 but was opened as a different encoding. Re-import with File Origin: 65001: Unicode (UTF-8).

I can't edit the imported table the way I want. Click inside it and choose Table Design > Convert to Range. You'll lose the refresh link.

Don't double-click CSV files you care about. Use Data > From Text/CSV, set codes and IDs to Text, choose UTF-8, and your data arrives exactly as it left.