CSV is a plain-text format that stores tabular data using commas to separate values — not an Excel file at all. But if you double-click a .csv file and expect Excel to handle it perfectly, you’ll likely get mangled dates, vanished leading zeros, or numbers turned into scientific notation (trust me, I learned this the hard way).
The Problem
You receive a vendor list from procurement:
suppliers.csv. You double-click it. Excel opens — looks fine at first glance. Then you spot it: "00123" became "123", "2024-05-01" turned into "May 1, 2024", and "$4,599.99" is now just "4599.99" with no dollar sign or comma.
That’s not Excel misbehaving. That’s Excel doing exactly what it’s designed to do — interpret plain text as *values*, not *strings*. And since CSV has zero formatting, formulas, colors, or cell types baked in, Excel guesses. Badly.
Here’s what your raw CSV actually contains (as seen in Notepad):
Supplier ID,Name,Contract Start,Annual Value,Region
00123,Acme Corp,2024-05-01,$4,599.99,North
00789,Beta Solutions,2024-06-15,$12,850.00,South
00042,Delta Labs,2024-03-22,$7,200.50,West
00555,Echo Dynamics,2024-07-10,$3,110.00,East
But Excel’s auto-import turns it into this mess:
| A1 | B1 | C1 | D1 | E1 |
|---|
| 123 | Acme Corp | 45401 | 4599.99 | North |
| 789 | Beta Solutions | 45476 | 12850 | South |
| 42 | Delta Labs | 45362 | 7200.5 | West |
| 555 | Echo Dynamics | 45505 | 3110 | East |
Notice how A2:A5 lost their leading zeros? C2:C5 are now serial numbers instead of dates? D2:D5 dropped currency symbols and thousands separators? That’s the problem in action — and it happens every time you open CSV by double-clicking.
The Solution
Don’t double-click. Use Excel’s Text Import Wizard instead. This gives you control over how each column is interpreted — before Excel makes irreversible decisions.
Here’s how to fix it in 4 steps:
1. In Excel, go to the
Data tab → click
Get Data →
From Text/CSV (or press
Alt +
A +
T to open legacy Text Import Wizard — yes, the old one still works and is faster for simple cases).
2. Browse to your
suppliers.csv, select it, and click
Import. You’ll land in Power Query Editor. Don’t panic — you don’t need to write M code. Just click
Transform Data if needed, but for now, skip to step 3.
3. In the preview window, click the
Transform tab →
Using Example is tempting, but avoid it. Instead, click each column header and set its data type manually:
- Click the icon next to
Supplier ID → choose
Text (to keep "00123")
- Click
Contract Start → choose
Date (and verify the format is YYYY-MM-DD)
- Click
Annual Value → choose
Decimal Number, then right-click →
Format Column → Currency
4. Click
Close & Load. Your data lands cleanly in Sheet1 — with leading zeros intact, proper dates, and formatted currency.
This is what you get now:
| A1 | B1 | C1 | D1 | E1 |
|---|
| 00123 | Acme Corp | 2024-05-01 | $4,599.99 | North |
| 00789 | Beta Solutions | 2024-06-15 | $12,850.00 | South |
| 00042 | Delta Labs | 2024-03-22 | $7,200.50 | West |
| 00555 | Echo Dynamics | 2024-07-10 | $3,110.00 | East |
One more tip: Save your cleaned version as
.xlsx — not
.csv — if you plan to add formulas, charts, or conditional formatting later.
Going Further
What if your CSV uses semicolons instead of commas? Or has embedded line breaks inside quoted fields? Or mixes UTF-8 and ANSI encoding?
First, know that CSV isn’t one standard — it’s a family of formats. The most common variant is RFC 4180, but many systems (especially European ones) default to semicolon delimiters and comma-as-decimal (e.g., "123,45" means 123.45).
To handle non-comma delimiters:
- In the Text Import Wizard (
Alt +
A +
T), choose
Delimited → click
Next → uncheck
Tab, check
Other, and type
;
- In Power Query, after loading, go to
Home →
Advanced Editor, and change
Delimiter=Comma to
Delimiter=Semicolon
For encoding issues (e.g., “Müller” showing as “Müller”), always use
From Text/CSV — not legacy wizard — because it auto-detects UTF-8. If it fails, click the
File Origin dropdown and try
65001: Unicode (UTF-8).
And here’s the counterintuitive tip: If you *must* double-click a CSV and can’t use the wizard, rename it to
.txt first. Then open it via
Data →
From Text/CSV. Excel treats
.txt files more carefully than
.csv — weird, but true.
Also worth noting: CSV files can’t store multiple worksheets. So if you export a 3-tab Excel workbook to CSV, you’ll only get Sheet1 — unless you explicitly save each sheet separately.
How Is CSV Different From Excel?
It’s not just “CSV vs Excel.” It’s plain text versus a structured binary container.
Think of CSV like a grocery list written on a sticky note: simple, universal, lightweight. Excel (.xlsx) is more like a filing cabinet with labeled folders, color-coded tabs, calculator tape stuck to the side, and a coffee stain on page 3.
| Feature | CSV | Excel (.xlsx) |
|---------|-----|----------------|
| **File size** | Tiny (e.g., 12 KB for 10k rows) | Larger (e.g., 240 KB same data + formatting) |
| **Formulas** | None | Full support (SUM, VLOOKUP, LAMBDA, etc.) |
| **Cell formatting** | None — just raw values | Fonts, borders, number formats, icons, data bars |
| **Multiple sheets** | No | Yes — up to 1,048,576 rows × 16,384 columns per sheet |
| **Security** | None — anyone can open in Notepad | Password protection, encryption, macro signing |
| **Compatibility** | Opens in Notepad, Google Sheets, Python pandas, SQL Server Import Wizard | Requires Excel, LibreOffice Calc, or compatible viewer |
The real difference shows up when you share data. If you email a CSV to a developer, they’ll parse it in 3 lines of Python. If you send them an Excel file with merged cells and custom fonts, they’ll reply with a polite but exhausted emoji.
Also: Excel can *save* as CSV — but it silently drops everything non-tabular: formulas become values, charts vanish, images disappear, and hidden rows/columns reappear. Always check the warning dialog — and read it.
When NOT to Use This
Don’t use CSV import if:
- Your data has formulas you want to preserve (CSV strips them — use .xlsx or .xlsb instead)
- You’re working with >1 million rows and need pivot table speed (CSV imports slowly; consider Power Pivot or database connection)
- You need to retain cell comments or data validation rules (they’re gone forever in CSV)
- You’re collaborating with people who *only* use Excel Online — it lacks full Text Import Wizard support (use desktop Excel or pre-process in Power Query Desktop)
Also avoid CSV entirely for financial audit trails. Why? Because CSV has no built-in version history, no change tracking, and no way to prove who edited which cell when. If compliance matters, stick with native Excel and enable
Track Changes (Review → Track Changes → Highlight Changes).
One edge case nobody talks about: CSV headers with spaces or special characters (e.g., "Customer #", "Q1 Revenue (USD)") break some legacy systems. If your downstream tool chokes on those, wrap headers in quotes in the raw CSV — but test first. Excel handles them fine; your ERP might not.
Keyboard Shortcuts
These shortcuts save minutes every day — especially when juggling CSV imports:
| Action | Shortcut | Notes |
|---|
| Open Text Import Wizard (legacy) | Alt + A + T | Fastest for simple comma/semicolon files |
| Open Power Query CSV Import | Alt + D + F + T | Triggers Data → From Text/CSV |
| Convert selected column to Text format | Ctrl + 1 → Number tab → Text → OK | Prevents auto-conversion on paste |
| Paste Special → Text Only | Ctrl + Alt + V, then T, then Enter | Kills formatting & formulas on paste |
| Toggle between formula view / value view | Ctrl + ` (backtick) | See actual formulas in cells — handy for debugging CSV exports |
