The first thing most people do when they get a CSV file from finance, marketing, or an API is double-click it — letting Windows open it in Excel automatically. That’s almost always wrong. You’ll lose leading zeros (like '00456' becoming '456'), misread dates ('01/02/2024' as Feb 1 instead of Jan 2), and silently corrupt UTF-8 characters with accents or Chinese text. Trust me, I learned this the hard way after rebuilding a client’s sales report three times.
Quick Answer
Yes, Excel can read CSV files — but not reliably if you just double-click them. Use Data > Get Data > From Text/CSV (Alt + A > T > T) for full control over delimiters, encoding, column types, and date formatting. This method reads the file without altering its structure, unlike opening it directly.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Double-click (default) | Just click the .csv file in File Explorer | Quick preview only — never for analysis | No encoding control; auto-converts numbers/dates; drops leading zeros; breaks Unicode |
| Data tab → From Text/CSV | Alt + A > T > T → select file → preview & load | Production work, clean imports, international data | Slight learning curve; requires manual column type assignment |
| Open With → Excel (via right-click) | Right-click → Open With → Excel | Same as double-click — no improvement | Still uses default locale settings; no preview or encoding choice |
| Power Query Editor (advanced) | From Text/CSV → Edit → transform before loading | Large files, repeated imports, cleansing pipelines | Overkill for one-off files; steeper initial setup |
| Copy-paste from Notepad | Open in Notepad → Select All → Paste into Excel | Tiny files (<50 rows); emergency fixes | No delimiter handling; columns won’t split unless tabs used; messy for commas |
Method 1 Deep Dive
Let’s walk through Data > From Text/CSV using a real sample: a supplier list exported from SAP as suppliers_q2_2024.csv.
Here’s what the raw CSV looks like in Notepad:
Supplier ID,Name,Invoice Date,Amount,Currency 00782,"Acme Corp",03/15/2024,$45,200.00,USD 00911,"Zhonghua Ltd",2024-04-02,¥328,500.00,CNY 00047,"Nordic AB",2024-03-28,€21,890.50,EUR
If you double-clicked this, Excel would turn 00782 into 782, interpret 03/15/2024 as March 15 (but might flip it depending on your system locale), and treat ¥328,500.00 as text — breaking any SUM formulas later.
Instead: press Alt + A > T > T. Navigate to the file and click Import. You’ll land in the preview window. Notice the green icons above each column? Those are data type suggestions. Click the icon over Supplier ID and choose Text. That preserves the leading zeros. Click the icon over Invoice Date and pick Date (MDY) — even though the third row uses YYYY-MM-DD format, Power Query auto-detects both.
Now check the Encoding dropdown at the bottom-left. If your file contains names like "José García" or "北九州株式会社", switch from Default to UTF-8. Then click Load. Your data lands cleanly in Sheet1 starting at cell A1 — with Supplier ID showing as 00782, dates properly formatted, and currency symbols intact.
Method 2 Deep Dive
What if you get a CSV with inconsistent quoting, embedded line breaks, or mixed delimiters? Try this trick: open the file in Notepad first, then use Data > From Text (Legacy).
This older import wizard (still available via Alt + D > E) gives you more granular control. It forces you to specify delimiter (comma, semicolon, tab), text qualifier (usually "), and whether the first row is headers — all upfront.
Take this messy snippet from a CRM export:
| Contact ID | Full Name | Notes |
| C-8841 | Sarah Chen | "Onboarded Apr 2024\nRenewed contract" |
| C-8842 | Miguel Torres | "Requested demo\nSent pricing sheet" |
Notice the line breaks inside quotes? The modern From Text/CSV sometimes chokes on those. But the legacy wizard handles them cleanly — as long as you check Text qualifier: " and uncheck My data has headers if your first row isn’t truly header material.
Here’s the counterintuitive tip: if Excel keeps misreading your CSV as tab-delimited (especially on Mac-exported files), go to File > Options > Advanced and uncheck Use system separators. Then manually set Decimal separator to . and Thousands separator to ,. Yes — turning off system defaults often makes CSV imports *more* reliable.
Cheat Sheet
| Action | Shortcut | Where to Find It | Pro Tip |
|---|---|---|---|
| Import CSV with full control | Alt + A > T > T | Data tab → Get Data → From Text/CSV | Always set Supplier ID / PO Number columns to Text before loading |
| Legacy text import (for tricky files) | Alt + D > E | Data tab → Get Data → Legacy Wizards → From Text | Check Text qualifier and confirm delimiter — don’t rely on auto-detect |
| Fix encoding after import | None — must re-import | Power Query Editor → File → Close & Load To… → Load To → New Worksheet | If Chinese/Japanese text shows as , close the query and restart with UTF-8 encoding selected |
| Preserve leading zeros permanently | Custom number format: 00000 | Home tab → Number Format dropdown → More Number Formats | Only works if data is already imported as text — won’t fix numeric conversion after the fact |
