The CSV Double-Click Disaster
You download orders from Shopify. Double-click the CSV to open in Excel.
Instant data corruption:
- Order ID `0001234` becomes `1234`
- Phone number `+1234567890` becomes `1.23457E+09`
- Product SKU `005-RED-M` becomes `5-RED-M`
- Date `01/03/2025` becomes `03/01/2025` (if you're in US locale)
- Tracking number `123456789012345678` becomes `1.23457E+17`
You don't notice until you've sent wrong shipments, contacted wrong customers, or couldn't reconcile orders.
Why this happens:
Excel is "helpful" - it tries to guess data types. Numbers get converted, dates get reformatted, leading zeros disappear. CSV is just text - Excel assumes it knows better.
The solution:
Never double-click CSVs. Always import using Text Import Wizard where YOU control formatting.
What Breaks When You Double-Click
Leading Zeros (SKUs, Order IDs, Zip Codes)
What you have in CSV:
```
00123
00456
00789
```
What Excel shows after double-click:
```
123
456
789
```
Impact: Lost leading zeros break lookups, imports, and reconciliation. You can't match order #00123 to shipment #123.
Large Numbers (Order IDs, Tracking Numbers, Phone Numbers)
What you have in CSV:
```
123456789012345678
```
What Excel shows:
```
1.23457E+17
```
Impact: Scientific notation is irreversible - you can't get the full number back. This ruins tracking numbers, credit card references, etc.
Dates
What you have in CSV (International format):
```
01/03/2025 (March 1, 2025)
```
What Excel shows (US format):
```
3/1/2025 (January 3, 2025)
```
Impact: Dates shift by months. Reports show wrong months, aging analysis is broken, reconciliation fails.
Text That Looks Like Formulas
What you have in CSV:
```
=2+2
-5
+1
```
What Excel does:
```
4 (calculates formula)
-5 (right-aligned as number)
1 (treats as positive number)
```
Impact: Excel executes formulas in CSV data. Malicious CSVs can exploit this (CSV injection attacks).
The Right Way to Import CSV to Excel
-
3
Open Excel (Don't Open the CSV)
Start with blank Excel workbook. Do NOT double-click the CSV file. We're going to import it using the wizard which gives us control.
-
3
Select Your CSV File
Browse to your CSV file and click Import (or Get Data). Excel shows a preview window. Do NOT click 'Load' yet - we need to transform the data.
-
3
Click 'Transform Data' (Power Query Editor)
In the preview window, bottom-right: 'Transform Data' button. This opens Power Query Editor where we can control data types before loading.
-
3
Set File Origin to UTF-8
In Power Query: Home → Data Type dropdown → ensure File Origin is '65001: Unicode (UTF-8)' for international characters. If data looks garbled, this is why.
-
3
Change Data Types for Each Column
Click column header. Transform → Data Type → Text (for SKUs, IDs with leading zeros) OR Date (for dates) OR Whole Number (for counts). Text preserves everything exactly as-is.
-
3
Click 'Close & Load'
Once all columns are correctly typed, click 'Close & Load' in top-left. Data loads to new sheet with your formatting preserved. Leading zeros intact, dates correct, numbers as text if needed.
Quick Method: Text Import Wizard (Legacy)
If you're using older Excel (pre-2016) or prefer the classic wizard:
Step-by-Step:
1. Data → From Text
- Select your CSV file
- Text Import Wizard opens (3 steps)
2. Step 1: Delimited
- Choose "Delimited" (data separated by commas)
- File Origin: "65001: Unicode (UTF-8)"
- Click Next
3. Step 2: Delimiter = Comma
- Check "Comma"
- Uncheck Tab
- Text Qualifier: " (double quote)
- Preview shows columns separated
- Click Next
4. Step 3: Column Data Formats
- THIS IS THE CRITICAL STEP
- Click each column in preview
- For columns with leading zeros, IDs, phone numbers: Select "Text"
- For date columns: Select "Date: MDY" or "YMD" depending on your source
- For regular numbers: Select "General"
- Click Finish
5. Choose Where to Load
- Existing worksheet: $A$1
- Or new worksheet
- Click OK
The Column Type Cheat Sheet
When setting column types during import, use this guide:
Always Use TEXT For:
- Product SKUs (any format: ABC-123, 00123, ITEM-XL-RED)
- Order IDs (especially with leading zeros)
- Zip/Postal Codes (00501, 90210)
- Phone numbers (+1234567890, (123) 456-7890)
- Credit card references (last 4 digits: 1234)
- Tracking numbers (long strings of digits)
- Customer IDs starting with 0
- Account numbers
- Any ID field where leading zeros matter
- Any field Excel might interpret as formula (=, +, -)
Use DATE For:
- Order dates
- Ship dates
- Customer birthdays
- Any date field
Important: Choose correct date format (MDY vs DMY vs YMD) based on your CSV source.
Use NUMBER (General or Whole Number) For:
- Quantities (5, 10, 100)
- Prices WITHOUT currency symbols ($19.99 should be just 19.99)
- Percentages as decimals (0.15 for 15%)
- Inventory counts
- Age, counts, scores
Use CURRENCY (Optional) For:
- Revenue, costs, refunds
But be careful - if CSV has currency symbols ($, €), import as TEXT first, then use formula to clean:
```excel
=VALUE(SUBSTITUTE(A2,"$",""))
```
Saving CSV Properly (Back to CSV)
After cleaning data in Excel, you might need to save back to CSV:
The Problem:
Excel's "Save As CSV" re-applies the same auto-formatting. Your carefully preserved leading zeros will disappear.
The Solution:
Option 1: Keep as Excel (.xlsx)
Best option - Excel format preserves all formatting, formulas, data types.
Option 2: Save as CSV UTF-8
File → Save As → CSV UTF-8 (Comma delimited) (.csv)
Important: Excel will warn "Some features may be lost". That's normal. But your TEXT-formatted cells should preserve leading zeros.
Option 3: Prefix Leading Zeros with Single Quote
If you MUST save to standard CSV and preserve leading zeros:
1. Before saving, prefix cells with single quote: `'00123`
2. The quote forces text interpretation
3. When re-imported, the quote is invisible but zeros remain
Testing Your CSV Export:
After saving as CSV:
1. Close the file
2. Double-click the CSV to open
3. Check if leading zeros survived
4. If not, use option 3 (single quote prefix)
Google Sheets CSV Import
Google Sheets has similar issues but slightly different solutions:
Automatic Import (Drag CSV to Sheets):
- Often preserves data better than Excel double-click
- But still auto-formats dates and numbers
- Not reliable for critical data
Proper Import Method:
1. File → Import
- Upload your CSV file
2. Import Settings:
- Import location: New sheet
- Separator: Comma
- Convert text to numbers, dates: UNCHECK THIS
3. Click Import
- Data loads as text
- No auto-formatting applied
4. Manual Formatting:
- Now YOU choose which columns to format as dates, numbers
- Right-click column → Format → Number/Date
- Keeps control in your hands
Text-to-Columns (For Already-Opened Files):
If you already opened CSV and data is broken:
1. Copy problematic column
2. Paste as plain text
3. Data → Split text to columns
4. Choose delimiter
5. Format as Text before splitting
Get your e-commerce data delivered as formatted Excel files, not broken CSVs. No import wizard, no data corruption, just clean data ready to analyze.
Get Clean Excel ExportsNo CSV headaches • Already formatted
Common Mistakes to Avoid
Learn from these common pitfalls to ensure success
Double-clicking CSV files to open them
Solution: Never do this. Always open Excel first, then import via Data → From Text/CSV.
Clicking 'Load' in Power Query preview without checking data types
Solution: Always click 'Transform Data' to review and set correct data types for each column.
Choosing wrong date format during import
Solution: Check a sample date in your CSV. If it says 01/03/2025, is that March 1 (DMY) or January 3 (MDY)? Choose matching format.
Saving back to CSV after cleaning, breaking the data again
Solution: Save as Excel (.xlsx) to preserve formatting. If you must use CSV, test the output by re-opening.
Not using UTF-8 encoding for international data
Solution: If you see garbled characters (é, ñ, etc.), change File Origin to UTF-8 in import settings.
How to Verify Your Numbers
Ensure accuracy with these verification steps
You can import a CSV with leading zeros and they're still there after import
Dates import in the correct format (January 3 doesn't become March 1)
Long numbers stay as full numbers, not scientific notation
International characters display correctly (José, München, São Paulo)
You know how to save back to CSV without breaking data again
Frequently Asked Questions
I already opened the CSV and data is broken. Can I fix it?
Why does Excel keep treating my SKUs as numbers even when I set them to Text?
What's the difference between General, Number, and Text formats?
How do I import CSV with semicolon delimiters instead of commas?
Can I automate CSV imports to always use correct formatting?
Ready to Transform Your Reporting?
Start with a free report and see exactly what you'll get every time.