Double-clicking a CSV is the fastest way to look at it, and often the fastest way to damage it. Excel does not just display the file; it guesses what every value is and converts it. Most of the time the guesses are fine. When they are not, ZIP codes lose digits, account numbers change and product codes become dates.
The changes happen on screen first. If you then save the file as CSV, they are written back and the original values are gone. Here is what goes wrong and how to import CSV files so it does not.
What Excel changes when you double-click a CSV
Take this small export:
zip,order_ref,part,gene,code,name
02139,9876543210123456,1-2,MARCH1,12E3,José NúñezOpened with default settings, Excel commonly turns it into something like this:
| Column | In the file | After a default open |
|---|---|---|
| zip | 02139 | 2139 |
| order_ref | 9876543210123456 | Shown as 9.87654E+15, stored as 9876543210123450 |
| part | 1-2 | A date, such as 2 January or 1 February depending on regional settings |
| gene | MARCH1 | A date in March |
| code | 12E3 | 12000 |
| name | José Núñez | Sometimes José Núñez, if the encoding is misread |
Exact behavior varies by Excel version, platform and regional settings, but each of these is common.
Leading zeros, long numbers and dates
Leading zeros
Anything made only of digits is treated as a number, and numbers do not have leading zeros. ZIP codes, employee IDs, phone numbers and bank sort codes all suffer. Formatting the column afterwards can only fake them for fixed-length codes; the original text has already been converted.
Long numbers
Excel stores numbers with 15 significant digits of precision. Any digits beyond the fifteenth are replaced with zeros, and long numbers in a General-format cell are displayed in scientific notation. Order references, tracking numbers and long account IDs are at risk. Once the digits are zeroed, they cannot be recovered from the spreadsheet.
Automatic dates
Values that look even slightly like dates get converted: 1-2, 3/4, Jun-25. Text that combines a month name and a number is caught too. Human gene symbols such as SEPT2 and MARCH1 were converted so often that the official symbols were later changed to SEPTIN2 and MARCHF1.
Recent versions of Excel for Microsoft 365 on Windows have settings under File → Options → Data to switch off some of these automatic conversions, including removing leading zeros and converting text to dates. Availability depends on your version and platform, so check before relying on it.
Accents, encoding and the UTF-8 BOM
Most modern systems export CSV as UTF-8. Excel, particularly on Windows, has historically opened CSV files using the computer’s legacy code page unless the file tells it otherwise. The result is garbled text such as José instead of José.
The signal Excel looks for is a byte order mark (BOM): three invisible bytes, EF BB BF, at the very start of the file. A UTF-8 file with a BOM generally opens correctly when double-clicked. The trade-off is that some scripts and older tools read the BOM as part of the first header, so a column called id silently becomes a slightly different name and lookups on it fail.
When saving from Excel, choose CSV UTF-8 (Comma delimited) rather than plain CSV (Comma delimited). On Windows the plain option uses the legacy code page, and characters it cannot represent may be lost.
Semicolons, commas and regional settings
In many countries the comma is the decimal separator, so 3,14 means three point one four. To avoid the clash, Excel in those regions typically uses a semicolon as the list separator, saving and expecting files like this:
product;price;stock
Widget;3,14;120
Gadget;12,50;8Open a comma-separated file on a computer set up that way and everything lands in column A. Open a semicolon file on a computer that expects commas and the same thing happens in reverse. Excel takes the separator from the operating system’s regional settings, so two colleagues can see the same file differently.
Some people add a first line such as sep=; to force Excel’s choice. Excel honors it, but most other tools treat that line as data, so it causes problems elsewhere. Importing with an explicit delimiter, or converting the file, is cleaner.
Step by step: import a CSV safely in Excel
These steps follow Excel for Microsoft 365 on Windows. Menu names may differ slightly in older versions and on Mac.
- Start from an empty workbook instead of double-clicking the file.
- Go to Data → From Text/CSV and choose the file.
- In the preview, set File Origin to 65001: Unicode (UTF-8) if accented characters look wrong, and confirm the Delimiter.
- Set Data Type Detection to Do not detect data types. Every column then loads as text, exactly as written in the file.
- If you want real numbers or dates in some columns, choose Transform Data instead and set each column’s type in the Power Query Editor: Text for IDs, ZIP codes and codes; Decimal or Whole Number only for true quantities.
- Choose Load. Save your work as
.xlsxto keep the column types. - If you must save back to CSV, use CSV UTF-8 and spot-check the IDs, ZIP codes and long numbers in the saved file with a text editor.
If you prefer the older Text Import Wizard, enable it under File → Options → Data → Show legacy data import wizards. Its third step lets you mark individual columns as Text.
Importing into Google Sheets
Google Sheets handles UTF-8 well, but it converts values by default too. To keep them as written:
- Open a new or existing spreadsheet and choose File → Import → Upload.
- Pick an import location, such as a new sheet.
- Set the separator type, or leave it on automatic detection if you have checked the file.
- Untick Convert text to numbers, dates, and formulas.
- Import, then format only the columns that should be numbers or dates.
Unticking that box also keeps cells that start with = as text, which matters for files from outside sources. See CSV injection explained for why.
Check the file before you open it
A spreadsheet is a poor place to discover that a file is broken, because it has already changed things by the time you look. Check the file as text first.
The free CSV validator runs in your browser, so the file is never uploaded. It checks for unterminated quotes, rows with the wrong number of fields, blank or duplicate headers, stray whitespace, duplicate rows, formula-like cells and encoding problems. It also profiles each column with a blank count and inferred type, so you can see which columns of digits a spreadsheet is likely to treat as numbers. If the delimiter is the problem, the Change CSV Delimiter tool converts between comma, semicolon, tab and pipe.
You can also skip the spreadsheet for cleaning altogether. The CleanYourDataset workspace trims whitespace, removes empty and duplicate rows and fills blanks without reinterpreting your values as numbers or dates, then exports UTF-8 CSV with a BOM so accented characters generally survive a double-click in Excel. The guide explains the export format, and you can create a free account to try it.