How to open a CSV in Excel without breaking your data
Why Excel drops leading zeros, rounds long numbers, invents dates and garbles accents when it opens a CSV, and the import settings that prevent each one.
Don't double-click the file. Open a blank workbook, choose Data > From Text/CSV, set File Origin to 65001: Unicode (UTF-8), pick the delimiter, and click Transform Data. Then set ID-like columns (ZIP codes, account numbers, product codes) to Text and set date columns with the locale they were written in. If you use Excel for Microsoft 365 or Excel 2024, also turn off the automatic data conversions you don't want under File > Options > Data.
A CSV is plain text. Nothing in it is broken until a program reads it and guesses what each value means. Double-clicking hands every guess to Excel, and Excel guesses using your computer's regional settings.
What goes wrong when you double-click a CSV in Excel?
Take this two-line file, saved as UTF-8 without a byte order mark:
zip,city,card,date,code,part,place
00501,Holtsville,4111111111111111,03/04/2026,MARCH1,1E5,Zürich
Opened by double-click in Excel on a US-English Windows installation with default settings, the row comes out like this:
| Column | In the file | What Excel shows | Why |
|---|---|---|---|
| zip | 00501 |
501 | Converted to a number; leading zeros dropped |
| card | 4111111111111111 |
4.11111E+15 | 16 digits; Excel keeps 15 significant digits |
| date | 03/04/2026 |
3/4/2026 (March 4) | Read in your locale's date order |
| code | MARCH1 |
1-Mar | Letter-and-digit text read as a date |
| part | 1E5 |
1.00E+05 | Read as scientific notation, value 100,000 |
| place | Zürich |
Zürich | UTF-8 bytes read as Windows-1252 |
On a UK-English system the same date becomes 3 April. If the file used semicolons as delimiters, or your system uses a semicolon as its list separator, the whole row would sit in column A instead.
Every one of these changes is lost for good if you save the file again.
Leading zeros
Excel's default parsing turns any value made only of digits into a number, and numbers have no leading zeros. ZIP codes, phone numbers, ISO numeric country codes such as 004 and employee IDs are the usual victims. Formatting the column afterwards cannot restore the zeros because the original text is gone; see how to keep leading zeros in a CSV for recovery tricks when the values have a fixed length.
Long numbers and scientific notation
Microsoft's documentation states that Excel has a maximum precision of 15 significant digits. In a number with 16 or more digits, everything after the 15th digit is replaced with zero, so 4111111111111111 is stored as 4111111111111110. Separately, the General number format displays numbers of 12 or more digits in scientific notation, which is why a 13-digit barcode can look like 5.01235E+12 even when no digits were lost.
The display is cosmetic. The rounding is not. Credit card numbers, long account numbers, tracking numbers and database IDs must be imported as Text.
Dates and date-like text
Excel reads 03/04/2026 in the order your regional settings use (month first in the US, day first in most other places). An ambiguous date that happens to be valid in both orders is converted silently and wrongly. Text such as MARCH1, SEPT2 or 1-2 can also become dates. The problem was common enough that in 2020 the HUGO Gene Nomenclature Committee renamed human genes such as SEPT1 and MARCH1 (now SEPTIN1 and MARCHF1), partly because spreadsheets kept turning the symbols into dates.
Encoding
Characters outside basic ASCII are stored as two or more bytes in UTF-8. The letter ü is the bytes C3 BC. If Excel reads the file with the Windows-1252 code page instead, those two bytes become the two characters ü. When a file starts with a UTF-8 byte order mark (the three bytes EF BB BF), Excel recognizes it as UTF-8. Without a byte order mark, Excel may fall back to your system's legacy code page.
Delimiters
A double-clicked CSV is split on the list separator from your Windows regional settings. In countries that write decimals with a comma, such as Germany, France and Turkey, that separator is a semicolon, so a comma-delimited file lands in one column. The full explanation is in why Excel opens a CSV in one column.
How do you switch off Excel's automatic data conversions?
Excel for Microsoft 365 and Excel 2024, on Windows and Mac, have an Automatic Data Conversion section. On Windows it is under File > Options > Data; on a Mac, Excel > Preferences > Edit. The options are:
- Remove leading zeros and convert to number
- Keep the first 15 digits of long numbers and display in scientific notation if required
- Convert digits surrounding the letter "E" into a number in scientific notation
- Convert "date-like" combinations of continuous letters and numbers into a date
Clear an option and Excel keeps those values as text when it opens a file or when you type them. A further checkbox, on by default, warns you when you open a .csv or .txt file that would trigger a conversion that is still switched on, and lets you open it once without converting.
Two limits apply. Microsoft notes that values with a space or other separator, such as JAN 1 or JAN-1, may still be treated as dates. And these settings do not govern Power Query imports, which have their own type settings. Older versions of Excel don't have these options, so use Fix 2 or Fix 3 there.
How do you import a CSV with Data > From Text/CSV?
This is the modern import path, built on Power Query.
- Open a blank workbook and choose Data > From Text/CSV, then select the file.
- Check File Origin. Microsoft's Power Query documentation says the character set isn't inferred and UTF-8 is only detected when the file starts with a UTF-8 byte order mark. If accented letters look wrong in the preview, choose
65001: Unicode (UTF-8). - Set Delimiter to the character the file actually uses: comma, semicolon, tab or a custom one.
- Set Data Type Detection. The choices are based on the first 200 rows, based on the entire dataset, or do not detect data types. The last option loads every column as Text, which is the safest starting point for an unfamiliar file.
- Click Transform Data to open the Power Query editor.
In the editor, set types column by column:
- For IDs and codes, select the column and choose Transform > Data Type > Text. If Power Query asks whether to replace the current conversion step or add a new one, choose Replace current. Adding a new step would convert text that has already lost its zeros.
- For dates written in another convention, right-click the column header and choose Change Type > Using Locale, then pick Date and the locale of the file, for example English (United Kingdom) for day-first dates.
- For numbers with a decimal comma, use the same Using Locale option with a locale that writes decimals that way, such as German (Germany).
- Leave long numeric IDs as Text. Converting them to a number type and loading them to the grid brings back the 15-digit limit.
Click Close & Load. The query stays attached to the workbook, so when a new version of the file arrives you can refresh instead of repeating the steps.
How do you use the legacy Text Import Wizard for a CSV?
The older wizard shows every option on three screens. Enable it under File > Options > Data > Show legacy data import wizards > From Text (Legacy), then run it from Data > Get Data > Legacy Wizards > From Text (Legacy).
- Step 1: choose Delimited, set Start import at row, and set File origin to
65001 : Unicode (UTF-8)for UTF-8 files. - Step 2: tick the delimiter and set Text qualifier to
"so that a quoted value such as"Dallas, Texas"stays in one cell. - Step 3: click each column in the preview and choose General, Text, Date (with an order such as DMY or MDY) or Do not import column (skip). The Advanced button sets the decimal and thousands separators used in the file.
Microsoft's documentation also notes that renaming a .csv file to .txt makes Excel start this wizard when you open it.
What does a sep= line do when opening a CSV?
If the first line of a CSV is sep=; (or sep=,), Excel for Windows uses that character as the delimiter and does not show the line. Microsoft doesn't document this behavior and it is not part of the CSV specification, RFC 4180. Every other program reads it as a data row, and it can interact badly with a byte order mark, so test it in the Excel versions your recipients use before relying on it.
The documented alternatives are to change the list separator in Windows Region settings (it affects every application) or, for Excel only, to clear Use system separators under File > Options > Advanced and set the decimal separator to a comma, which makes Excel use a semicolon as the list separator.
How do you import a CSV in LibreOffice Calc?
Calc always shows its Text Import dialog when you open a CSV.
- Character set: choose Unicode (UTF-8).
- Language: sets how numbers and dates are read, so a German setting reads
1,50as one and a half. - Separator options: tick Comma, Semicolon, Tab or Other, and set the String delimiter to
". - Format quoted field as text: imports any value wrapped in quotes as text.
- Detect special numbers: when it is off, only plain decimal numbers are converted and dates, times and similar values stay as text.
- Column type: click a column in the preview and choose Standard, Text, Date (DMY), Date (MDY), Date (YMD), US English or Hide.
Calc stores numbers in the same double-precision format as Excel, so long IDs need the Text column type there too.
How do you import a CSV into Google Sheets without converting values?
Use File > Import > Upload. In the import dialog, choose an Import location, set Separator type, and clear Convert text to numbers, dates, and formulas to bring every value in as text. Sheets reads dates and decimal separators according to the spreadsheet's locale under File > Settings, so set that before importing a file from another country.
What routine should you follow for every CSV you receive?
- Open the file in a text editor first. Note the delimiter, the date format, any leading zeros, any IDs longer than 15 digits and any accented characters.
- Import with Data > From Text/CSV (or the equivalent in Calc or Sheets). Never double-click a file you care about.
- Set File Origin to UTF-8 and Data Type Detection to Do not detect data types, then convert only the columns that should be numbers or dates.
- Spot-check the first and last rows against the text editor.
- Save your working copy as XLSX, which keeps the types you set. The XLSX vs CSV page explains what each format preserves.
If you produce CSVs for other people, write UTF-8 with a byte order mark (Excel's Save As > CSV UTF-8 (Comma delimited) does this), quote text fields, write dates as YYYY-MM-DD, and say in the file name or a readme which delimiter you used. Datasets such as world countries geography download as both CSV and XLSX; pick XLSX for Excel and CSV for a script or database.