How to open a CSV file without breaking IDs, leading zeros and dates

A CSV file is plain text, but a spreadsheet app doesn’t open it as text: it decides what each column holds. That can turn a 16-digit order number into 1.23E+15, a postal code like 00123 into 123, and a day-first date into the wrong day. The safe way is to import the file instead of double-clicking it, and to tell the app which columns are text before anything is converted. Here’s how in Excel and Google Sheets, and what to do if the damage was already saved.

Why double-clicking a CSV changes your data

RFC 4180, which documents the common CSV format, describes records made of fields separated by commas, with optional double quotes around a field. ^{[Source 1]} Nothing in that format marks a column as text, a number or a date, so the app reading the file has to decide. ^{[Source 1]} When Excel opens a .csv file, it uses the current default data format settings to interpret each column. ^{[Source 2]}

Excel automatically removes leading zeros and converts large numbers to scientific notation, like 1.23E+15, so that formulas and math operations can work on them. ^{[Source 3]} Microsoft’s examples of data this hits include phone numbers, credit card numbers, product codes, account numbers and postal codes. ^{[Source 3]}

Long IDs lose digits

Excel has a maximum precision of 15 significant digits, so in a number with 16 or more digits, every digit past the 15th becomes zero. ^{[Source 3, Source 4]} Microsoft’s example is 12345678901234567890, which becomes the number 12345678901234500000 and may be displayed as 1.23457E+19. ^{[Source 5]} The zeros are in the value itself, not just in how it’s displayed.

Leading zeros disappear

By default, a value such as 00123 is converted to the number 123. ^{[Source 5]} In a postal code or a product code the zeros are part of the value, so an import that matches records on that field may no longer find them.

Dates are read in the wrong order

Some locales use a default date format of mm/dd/yyyy, while others use dd/mm/yyyy. ^{[Source 8]} So 03/04/2026 is 4 March in one and 3 April in the other, and the file doesn’t say which it means. Codes can turn into dates too: by default, a value like JAN1 is converted to the date January 1. ^{[Source 5]}

Accents turn into the wrong characters

Text is stored as numeric values, and an encoding standard says which value stands for which character. ^{[Source 10]} Read a file with a different encoding and the same values show different characters: in Microsoft’s example, a Cyrillic Й appears as É on a computer that uses the Western European (Windows) encoding. ^{[Source 10]} A byte-order mark (BOM) at the start of the file helps: Excel opens a UTF-8 CSV normally if it was saved with one. ^{[Source 9]}

Look at the raw file first

Keep the original download untouched and open a copy in a plain text editor, such as Notepad on Windows or TextEdit on a Mac. A text editor doesn’t turn values into numbers or dates, so you see what the file contains. Check the separator (commas, semicolons or tabs), how IDs and codes are written, whether any date has a day above 12 (which tells you the order), and how a few accented names look.

If the IDs already read 1.23E+15 here, the damage happened before the file reached you: see if the file was already saved.

How to open a CSV in Excel without losing data

Import it with From Text/CSV

1. On the Data tab, in the Get & Transform Data group, select From Text/CSV. ^{[Source 2]}
2. Find and double-click the file, then select Import. ^{[Source 2]}
3. In the preview, select Transform Data instead of Load, so the data opens in Power Query and you can edit it before it reaches Excel. ^{[Source 2]}
4. Select the header of a column that must stay exactly as written, then go to Home > Transform > Data Type and select Text. Ctrl+click selects several columns. ^{[Source 3]}
5. In the Change Column Type dialog, select Replace current. ^{[Source 3]}
6. Select Close & Load to return the data to your worksheet. ^{[Source 3]}

Step 3 matters because, for CSV files, Power Query detects data types by inspecting the values (by default in the first 200 rows) and adds a Changed Type step that converts them. ^{[Source 7]}

Dates in a different order: by default, Power Query uses your operating system’s locale to interpret text, numbers, dates and times. ^{[Source 8]} If the file uses another order, right-click the date column’s header, select Change Type > Using Locale, and choose the data type and locale. ^{[Source 8]} To change it for the whole file, select the Edit Settings icon next to the Source step under Applied Steps, and pick a locale in the Comma-Separated Values dialog. ^{[Source 8]}

Accents look wrong: for a UTF-8 file without a BOM, Microsoft points to Data > Get Data > From file > Get Data from Text/CSV. ^{[Source 9]}

Use the Text Import Wizard

The Text Import Wizard is a legacy feature that is still supported. ^{[Source 6]} To turn it on, select File > Options > Data, then under Show legacy data import wizards, select From Text (Legacy). ^{[Source 6]} Then go to Data > Get & Transform Data > Get Data > Legacy Wizards > From Text (Legacy). ^{[Source 6]} Renaming the file from .csv to .txt before you open it also makes Excel run the wizard. ^{[Source 2]}

• Step 1: set File origin to the character set the file was made with, if it differs from your computer’s. ^{[Source 6]}
• Step 2: choose the delimiter and the text qualifier, the character that encloses values. ^{[Source 6]}
• Step 3: select each column and set its column data format: Text for digits that must stay as written, or Date with the file’s order, such as YMD. ^{[Source 6]}

Turn off automatic data conversions

Excel for Microsoft 365 and Excel 2024, on Windows and Mac, let you switch the conversions off. ^{[Source 5]} On Windows, go to File > Options, select Data and go to Automatic Data Conversion; on a Mac, select Excel > Preferences, then the Edit tab. ^{[Source 5]} You can stop Excel removing leading zeros, keeping only 15 digits of long numbers, turning digits around the letter “E” into scientific notation, and turning letter-and-number combinations into dates. ^{[Source 5]} These options apply when you open a .csv file, but they don’t directly affect data imported with Power Query. ^{[Source 5]}

How to open a CSV in Google Sheets

1. Open a spreadsheet in Google Sheets. ^{[Source 11]}
2. At the top, click File > Import and choose the .csv file. ^{[Source 11]}
3. Select an import option, such as Create new spreadsheet or Insert new sheets. ^{[Source 11]}
4. Optionally, under “Separator character”, choose Detect automatically, Tab, Comma or a Custom character. ^{[Source 11]}
5. Click Import. ^{[Source 11]}

Google’s help page for importing doesn’t describe a way to set a column’s type during the import. ^{[Source 11]} Compare a few IDs, codes and dates with the raw file afterwards. The spreadsheet’s locale sets its default currency, date and number formatting; you can change it under File > Settings. ^{[Source 12]} If your file is day-first, check the locale before you import.

If the file was already saved after the damage

If someone opened the export in a spreadsheet and saved it again, the file now holds whatever the app turned the values into, and formatting can’t bring the originals back:

• An ID saved as 1.23E+15 keeps only the digits you can see.
• Formatting a column as Text doesn’t change numbers already entered, and a custom number format doesn’t restore leading zeros removed before it was applied. ^{[Source 3]}
• Dates that fit both orders, like 03/04/2026, can’t be told apart from the file alone.

One partial repair: if every value should have the same number of digits, like five-digit postal codes, the TEXT function can pad the zeros back, for example =TEXT(A1,"00000"). ^{[Source 3]} If the codes vary in length, you can’t know how many zeros each one lost.

The reliable fix is to export the data again from the system it came from, keep that download as it is, and open a copy with one of the import methods above.

Where CSV Import Repair fits

CSV Import Repair checks a CSV, TSV or TXT file in your browser: the file is read on your device and isn’t uploaded. It doesn’t open Excel workbooks. For each kind of damage:

• Long IDs: it flags values in scientific notation, such as 1.23E+15, in columns with a name like ID, SKU, Code, Account, Phone or Postal code, and in columns that otherwise hold text. It tells you the digits can’t be recovered from the file and that you should re-export from the source. It doesn’t change them. Some combined names, such as customer_id or AccountNumber, aren’t recognised yet: there the value is reported only as not being a plain number.
• Leading zeros: it reads every value as text and never converts it to a number, and the default Exact values download saves each value as it is. It can’t tell that a zero is already missing.
• Dates: when a column’s dates could be read two ways, mix day-first and month-first, or are written in different formats, it asks which order the file uses. Then it can rewrite them as YYYY-MM-DD, day first or month first. Dates that don’t fit the order you chose are left unchanged, and two-digit years are never guessed.
• Encoding and BOM: it detects a byte-order mark and checks whether the file is valid UTF-8. If it isn’t, it makes a best guess, shows previews in other encodings, and lets you read the file again with the one that looks right. Characters that couldn’t be read are flagged. Repaired files are saved as UTF-8, with a BOM if you choose. It can’t repair characters that were already saved garbled into the file.

Nothing changes without your approval, every change is listed in the change report you download, and Undo reverses it.

Whichever app you use, the order is the same: keep the original, look at it as text, and tell the app what each column is before it decides for you.