It’s 3:12 PM. You just double-clicked sales_q3_2024.csv — 1.2 million rows, exported from your ERP. Excel opens… but only shows 1,048,576 rows. No warning. No error. Just silence — and a missing 152,424 records.
The Setup
You’re auditing Q3 sales for Acme Corp’s APAC region. The raw CSV comes from Snowflake, generated daily. It includes order ID, customer name, product SKU, date shipped, and revenue. Your team expects full traceability — no row drops.
| Order ID | Customer Name | Product SKU | Date Shipped | Revenue |
|---|---|---|---|---|
| ORD-78291 | Sarah Chen | PROD-A22X | 2024-07-03 | $12,450 |
| ORD-78292 | Tokyo Logistics Ltd | PROD-B44Z | 2024-07-03 | $8,920 |
| ORD-78293 | Maya Rodriguez | PROD-C11Y | 2024-07-04 | $15,600 |
| ORD-78294 | Sydney MedEquip | PROD-A22X | 2024-07-04 | $22,100 |
| ORD-78295 | Hoang Tran | PROD-D77W | 2024-07-05 | $6,340 |
| ORD-78296 | Singapore FinServ | PROD-B44Z | 2024-07-05 | $18,750 |
| ORD-78297 | Linh Nguyen | PROD-C11Y | 2024-07-06 | $9,820 |
| ORD-78298 | Melbourne Data Labs | PROD-A22X | 2024-07-06 | $14,300 |
| ORD-78299 | Kobe Tech Partners | PROD-D77W | 2024-07-07 | $5,210 |
| ORD-78300 | Auckland Cloud Inc | PROD-B44Z | 2024-07-07 | $20,490 |
The Challenge
You need the full 1,201,000-row dataset in Excel — not a truncated version. But here’s what most people don’t know: Excel’s 1,048,576 row limit applies to worksheets, not CSV files themselves. When you double-click a CSV, Excel uses its default text import engine — and that engine silently caps at 1,048,576 rows before even hitting the worksheet limit. Worse, if your CSV has commas inside quoted fields (like "Chen, Sarah"), Excel may mis-parse entire rows without warning.
The beauty of this approach is that it bypasses the auto-import engine entirely — no guessing whether Excel will treat column 3 as text or number, no phantom line breaks from embedded newlines.
Walking Through It
Step 1: Don’t double-click. Instead, open Excel blank. Go to Data → Get Data → From Text/CSV. Navigate to your file and click Import. This launches Power Query — Excel’s real CSV handler.
Step 2: In Power Query Editor, notice the preview shows all 1,201,000 rows. Check column types: click the ABC icon next to Revenue and select Decimal Number. For Date Shipped, click the calendar icon and choose Date. This prevents $12,450 from becoming 12450 or 12/45/00.
Step 3: Fix embedded commas. If Customer Name contains values like "Lee, James", Power Query auto-detects quote-delimited fields — but only if the first 200 rows contain them. Scroll down to row 1,198,201 (yes, really). See "O'Sullivan, Declan"? That’s your test. If it’s parsed as two columns, go back to Advanced Editor and add QuoteStyle = QuoteStyle.Csv to the Source step.
Before: Double-clicking sales_q3_2024.csv → A1:A1048576 filled, B1:B1048576 partially misaligned, 152,424 rows gone.
| A1 | B1 | C1 | D1 | E1 |
|---|---|---|---|---|
| ORD-78291 | Sarah Chen | PROD-A22X | 2024-07-03 | 12450 |
| ORD-78292 | Tokyo Logistics Ltd | PROD-B44Z | 2024-07-03 | 8920 |
| #VALUE! | (blank) | (blank) | (blank) | (blank) |
After: Power Query load → Full 1,201,000 rows in Sheet1, all columns correctly typed, no truncation.
| A1 | B1 | C1 | D1 | E1 |
|---|---|---|---|---|
| ORD-78291 | Sarah Chen | PROD-A22X | 2024-07-03 | $12,450 |
| ORD-78292 | Tokyo Logistics Ltd | PROD-B44Z | 2024-07-03 | $8,920 |
| ORD-1198201 | O'Sullivan, Declan | PROD-C11Y | 2024-09-22 | $11,375 |
The Result
Sheet1 now holds every record. You can filter by Date Shipped (column D), sum revenue with =SUM(E2:E1201001), and even add a pivot table that respects all 1.2M rows — because Power Query loads into the Data Model, not just the grid.
| Row | Order ID | Customer Name | Revenue |
|---|---|---|---|
| 1 | ORD-78291 | Sarah Chen | $12,450 |
| 2 | ORD-78292 | Tokyo Logistics Ltd | $8,920 |
| 1,048,576 | ORD-1126872 | Jakarta SaaS Group | $14,210 |
| 1,201,000 | ORD-1201000 | Wellington Analytics | $7,890 |
What Could Go Wrong
Here are three mistakes we see weekly — each with a clear symptom, cause, and exact fix:
| Symptom | Cause | Fix |
|---|---|---|
| All dates show as ##### in column D | Excel auto-formatted as General, then overflowed width | Select D1:D1201000 → Ctrl+1 → Category: Date → OK. Or double-click column edge. |
| Revenue sums to $0.00 despite visible numbers | Numbers imported as text (notice left-aligned cells) | In Power Query, click Revenue column → Transform tab → Data Type → Decimal Number. Then reload. |
| First 500 rows load fine; rest are blank or garbled | CSV uses UTF-8 with BOM, but Excel’s legacy importer reads as ANSI | In Power Query, click File Origin → Change to UTF-8 before loading. |
One counterintuitive tip: If your CSV exceeds 2M rows, skip Excel entirely. Use Alt+A+T (Data → From Text/CSV) anyway — then click Transform Data, then Advanced Editor, and replace Source = Csv.Contents(...) with Source = Csv.Contents(Web.Contents("file:///C:/path/to/file.csv"),[Encoding=1200]). Encoding 1200 = UTF-16 — handles larger files more stably.
Your next step: Open Excel right now. Try Alt+A+T on any large CSV — even one with 500k rows. Watch how Power Query previews *all* rows before loading. That’s your signal: you’re no longer at Excel’s mercy.
