A CSV is just text. No types, no formatting, nothing telling Excel what any of it means. So when you open one, Excel has to guess at every column, and it guesses eagerly. Most of the time you don't notice. The times you do notice are expensive, and one of them lost 16,000 COVID test results.
What Excel changes without asking
| In the CSV | After double-clicking | What that costs you |
|---|---|---|
| 00745 | 745 | Postcodes and account numbers lose leading zeros |
| 8801234567890123456 | 8.80123456789012E+18 | IDs past 15 digits are rounded, permanently |
| MAR1 | 1-Mar | Gene names, SKUs and part codes become dates |
| 3/4/2026 | April 3 or March 4 | Locale decides, and the file doesn't say |
| +44 20 7946 0000 | #NAME? or a formula | A leading + is read as an operator |
| café | café | UTF-8 read as Windows-1252 |
| 1.234,56 | 1.23456 or text | European decimals misparsed |
The right way to import in Excel
Import it, don't open it
Start from a blank workbook and go to Data → Get Data → From Text/CSV. That opens the Power Query preview instead of applying Excel's guesses on the spot.
Set the encoding
File Origin to 65001: Unicode (UTF-8). If accented characters look wrong in the preview, this is the setting that fixes it, not a find-and-replace afterwards.
Confirm the delimiter
Comma, semicolon, tab or pipe. Files exported in European locales often use semicolons, because the comma is doing duty as the decimal separator.
Set the column types
Click Transform Data and set every ID, postcode, phone number and product code to Text. This one step prevents everything in the table above.
Set date columns by locale
Use Using Locale and pick the locale the file was exported from, not the one you're sitting in. It's the only way to resolve day and month reliably.
Load, then check
Compare the row count against the source, spot-check a value you know, and confirm =ISNUMBER() behaves on your numeric columns.
The right way in Google Sheets
Fewer options here, but one that matters. Use File → Import and set Convert text to numbers, dates, and formulas to No. Everything arrives as text, intact, and you convert the columns you actually want converted afterwards.
Set the spreadsheet locale under File → Settings to match the file's origin before you import, too. Locale drives date parsing and decimal separators, and changing it later doesn't re-parse what's already in the sheet.
Working out what went wrong
Before repairing anything, identify which failure you've got. They have different fixes, and one of them has no fix at all.
- Numbers in a text column:
=ISNUMBER(A2)is TRUE where you expected an ID string, and the leading zeros are gone. - Rounded IDs: scientific notation in the cell, or the last few digits are suspiciously all zeros. Not recoverable.
- Codes turned into dates: the cell right-aligns and
=ISNUMBER()is TRUE on something that should be text. - Encoding damage:
é,’, orsitting at the start of the first header. - Everything in column A: wrong delimiter. Re-import with the right one.
Repairs, when you have no original
=TEXT(A2, "00000")=TEXT(A2, "mmmyy")=SUBSTITUTE(SUBSTITUTE(A2, "é", "é"), "’", "'")Stopping it at the source
If you own the export, a few changes upstream remove this problem for everyone downstream, permanently.
- 1Export dates as ISO 8601 (
2026-06-10). Unambiguous in every locale, and it sorts correctly even as text. - 2Export UTF-8 with a BOM if the file will be opened in Excel on Windows. The BOM is what tells Excel the encoding.
- 3Quote every field containing a comma, a quote or a newline, and escape embedded quotes by doubling them.
- 4Offer
.xlsxas an option where you can. It carries types, so none of this guessing happens. - 5Never let a field start with
+or=. Beyond the display mess, that's the CSV formula injection vector.
Two minutes of checking
However you got the data in, confirm it before building anything on top.
- Row count matches the source system, remembering the header row.
=COUNTA()against=COUNT()on numeric columns. A gap means text-formatted numbers.- Column totals match a control total from the source, if you have one.
- Sort each date column both ways and look at the extremes for 1900 or 2099 outliers.
- Search the sheet for
Ãas a one-shot encoding check.
Once it's in cleanly, the nine-step cleaning checklist picks up where this leaves off.
Convert a CSV without the guesswork
Upload the file and SheetAI flags the columns Excel would have mangled, IDs, codes and ambiguous dates, before anything gets converted.
Import a CSV in SheetAI
