A 2023 workplace survey of 1,247 finance and ops professionals found that 58% of CSV-related errors in Excel went unnoticed for over 3 days — and 22% led to incorrect payroll or vendor payments before anyone caught them.
Quick Answer
CSV (Comma-Separated Values) is a plain-text file format where each line represents a row of data, and commas separate the columns. Excel opens CSV files automatically — but it guesses data types, often mangling phone numbers like 0422 198 765, dates like 01/02/2024, or product codes like 00123. The file itself contains no formatting, formulas, or multiple sheets — just raw, unstructured text.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Double-click CSV file | Open directly in File Explorer | Quick preview when data is simple and clean | No control over column formatting; Excel auto-converts everything |
| Data > From Text/CSV | Import via Power Query interface | Large files, mixed data types, repeatable workflows | Adds a query tab; not ideal if you need immediate edits in worksheet |
| Data > From Text (Legacy) | Use Text Import Wizard (Alt+A+T) | Precise column-by-column type control; works offline | Wizard disappears after first use; no refresh capability |
| Open as TXT, then Save As Excel | Change extension to .txt, open with Notepad, copy-paste into Excel | When commas appear inside quoted fields (e.g., “Smith, Jr.”) | Tedious; loses original line breaks; no encoding control |
Method 1 Deep Dive
Let’s say you get a supplier list from Alibaba’s export tool: suppliers_export_20240412.csv. It looks harmless:
Supplier ID,Name,Contact Email,Order Date,Amount 00892,"Acme Corp","contact@acmecorp.cn","03/18/2024",12450.75 00105,"Zhenjiang Tools Ltd","info@zjtools.com","04/02/2024",8920.00 00711,"Shenzhen Precision Group","sales@szpg.net","04/10/2024",32675.50
If you double-click that file, Excel will open it — but quietly convert 00892 to 892, interpret 03/18/2024 as March 18 (not 2024-03-18), and treat "Acme Corp" as plain text — fine, but fragile.
Here’s what to do instead: Open Excel blank. Go to the Data tab → click Get Data → From Text/CSV. Navigate to your file and click Import.
You’ll land in Power Query Editor. Look at the preview. See how Supplier ID shows as 892? Click the icon next to its header (it looks like 123) and choose Text. Same for Contact Email — even though it looks fine, setting it to Text prevents future parsing glitches.
Now check Order Date. If it’s showing as 3/18/2024 without time, click the ABC→123 icon → Date. Excel will auto-detect the format — but verify it’s using M/d/yyyy. If not, right-click the column → Change Type → Using Locale → select English (United States).
Click Close & Load. Your data lands in Sheet1 — now truly preserved. Cell A2 says 00892, not 892. And yes — this is why finance teams at companies like Alibaba Cloud run validation checks on every CSV import before posting journal entries.
Method 2 Deep Dive
Sometimes you’re stuck on an older Excel version (2016 or earlier), or your IT policy blocks Power Query. That’s where the legacy Text Import Wizard saves you — and it’s faster than most realize.
Start with a blank workbook. Go to Data → From Text (not “From Text/CSV”). You’ll see the wizard. Navigate to your CSV file and click Import.
Step 1: Choose Delimited → Next. Step 2: Check Comma only — uncheck Tab, Semicolon, Space. Then click Next.
Step 3 is where most people rush — and lose data. Look at the preview pane. See 00892 turning into 892? That’s Excel treating it as a number. Highlight Column A (Supplier ID) in the preview, then under Column data format, select Text. Do the same for Column C (Email) and Column D (Date) — yes, even the date. Why? Because Excel’s auto-date logic assumes US format by default, and your supplier might be in Singapore or Germany.
Here’s the counterintuitive tip: If your CSV has headers, leave My data has headers checked — but don’t trust Excel’s header detection. Manually rename columns in the final step: double-click Column1 and type Supplier ID. This avoids mismatches when re-importing later.
Click Finish → choose New worksheet. Done. No formulas. No queries. Just clean, predictable data starting at cell A1.
Keyboard shortcut: Alt + A + T opens the Text Import Wizard instantly — no mouse needed. Try it now with any CSV file on your desktop.
Cheat Sheet
| Action | What to Do | Shortcut / Tip | Cell Example |
|---|---|---|---|
| Prevent leading zero loss | Set column format to Text *before* pasting, or use apostrophe: '00892 |
Ctrl+1 → Number → Text | A2 = '00892 |
| Force date recognition | In Power Query, right-click column → Change Type → Using Locale → English (US) | Alt+D+T opens wizard | D2 = 2024-03-18 |
| Preserve quotes in names | Ensure CSV uses double quotes around fields containing commas — e.g., "Smith, Jr.",Sales |
Power Query handles this automatically | B5 = "Zhenjiang Tools Ltd" |
| Verify import accuracy | Compare row count in Notepad (Ctrl+End) vs Excel’s status bar; check first/last 3 rows manually | Ctrl+G → type A:A → Enter to jump to last row |
A107 = 00711 (not 711) |
| Save back as CSV safely | File → Save As → Browse → Choose location → Save as type: CSV (Comma delimited) → OK | Excel strips formulas, charts, and extra sheets | Saved to: C:\Exports\alibaba_suppliers_clean.csv |
