How to Convert a Large CSV to Excel (Over 1 Million Rows)

So you have a CSV file that is too large for Excel, and double-clicking it either freezes your computer, opens a truncated mess, or throws a cryptic error. You are not alone. Data analysts, accountants, marketers, and researchers run into this every single day: an export from a CRM, a web-scraping dump, server logs, or a government open-data file lands on their desk as a 500 MB, 2 GB, or even 10 GB CSV, and Excel simply cannot open it. The reason is a hard, non-negotiable ceiling baked into every modern version of Excel: 1,048,576 rows per worksheet. No setting, registry hack, or add-in raises that number.

This guide walks you through every practical large CSV to Excel method, from splitting the file into chunks to Power Query’s Data Model to chunked Python conversion. You will learn why the row limit exists and what exactly happens when a CSV crosses it, how to pick the right method for your situation in under a minute, and then four battle-tested methods in full detail: splitting the CSV into multiple Excel files that each fit under the limit, importing with Power Query into the Data Model so millions of rows live inside one workbook, chunked conversion with Python for the biggest files, and the database alternative for when Excel is honestly the wrong tool. Along the way you will get copy-paste code, troubleshooting for the most common errors, performance tips that cut conversion time in half, and honest guidance on when to stop fighting Excel and switch tools.

Whether your file is a 200 MB sales export with 3 million rows or a multi-gigabyte log file, by the end of this article you will have a working pipeline — and if your file is a normal size, our free CSV to Excel converter handles it in seconds right in your browser, with nothing uploaded to any server.

Why Excel Can’t Open Huge CSVs: The 1,048,576 Row Limit

Every Excel worksheet, in every version from Excel 2007 through Microsoft 365, has exactly 1,048,576 rows and 16,384 columns (the last column is XFD). This is not a bug and it is not configurable. It comes from the XLSX file format itself, which addresses rows with a fixed structure. Microsoft has kept the limit stable for nearly two decades because changing it would break compatibility with billions of existing spreadsheets, add-ins, and integrations across the US, UK, India, Canada, Australia, and everywhere else Excel is used.

When you open a CSV that is too large for Excel, one of three things happens. First, and most dangerously, Excel may silently truncate your data: it loads the first 1,048,576 rows and drops the rest without a clear warning, so you end up analyzing an incomplete dataset and never know it. Second, Excel may refuse to open the file at all, showing an error like “this file is too large” or simply hanging until you force-quit. Third, the file may open but become unusably slow, with every scroll and filter taking seconds because Excel is trying to hold hundreds of megabytes of text-parsed data in memory.

A quick way to check whether your file is over the limit before you even try: count the rows from the command line or with a quick script instead of opening the file. On Windows PowerShell, (Get-Content large.csv | Measure-Object -Line).Lines gives you a row count (slow for huge files — a faster .NET approach is shown later). On macOS or Linux, wc -l large.csv answers in seconds. If the count is above roughly one million data rows (remember to subtract the header row), you need one of the methods below rather than a plain double-click open.

It also helps to know the difference between row count and file size. A 100 MB CSV with 20 narrow columns might hold 2 million rows and break the limit, while a 500 MB CSV with 200 wide text columns might hold only 300,000 rows and open fine — slowly, but fine. Always check rows, not megabytes, when deciding your strategy.

Large CSV to Excel: Pick Your Method in 60 Seconds

When a CSV is too large for Excel, not every oversized file needs the same treatment. Use this decision guide before you invest time:

• 1–3 million rows, and you want plain worksheets you can scroll and filter: split the CSV into multiple Excel files, each under the row limit. Method 1 is your answer.
• 1–10 million rows, and you mainly need pivot tables, charts, and summaries: import with Power Query into the Data Model. Method 2 keeps everything in one workbook.
• 10+ million rows, multi-gigabyte files, or you need this automated daily: Python chunking. Method 3 is the professional pipeline.
• You need to join, filter, and aggregate the data rather than eyeball it: load it into DuckDB or SQLite and export only results to Excel. Method 4.
• Under ~1 million rows but the file still feels heavy: you probably do not have a row-limit problem at all — see the quick-files section or just use our free converter.

If you are in a hurry, Method 1 with the Python script below is the fastest path from “impossible file” to “usable Excel files” for most people. If you are an Excel power user who lives in pivot tables, Method 2 will feel like magic.

Method 1: Split the CSV into Multiple Excel Files

Splitting is the most straightforward answer to a CSV that is too large for Excel: chop the file into chunks of, say, 900,000 rows each (leaving headroom under the 1,048,576 cap), and convert every chunk to its own XLSX file. You end up with sales_part1.xlsx, sales_part2.xlsx, and so on — each one a normal, fast, fully functional Excel file. This works for any file size, needs no Excel installation for the conversion itself, and the parts can later be recombined with Power Query if you want a unified view.

Option A: Python (recommended)

Install pandas and openpyxl once (pip install pandas openpyxl), then run this script. It reads the CSV in chunks without ever loading the whole file into memory, so it handles multi-gigabyte files on an ordinary laptop:

import pandas as pd

CHUNK_ROWS = 900_000   # safely under Excel's 1,048,576 row limit
chunk_no = 1

for chunk in pd.read_csv("large.csv", chunksize=CHUNK_ROWS):
    out = f"large_part{chunk_no}.xlsx"
    chunk.to_excel(out, index=False, engine="openpyxl")
    print(f"Wrote {out} ({len(chunk):,} rows)")
    chunk_no += 1

print("Done.")

Each chunk keeps the header row, so every output file is self-contained. On a typical laptop this processes roughly 1–3 million rows per minute; a 5-million-row file finishes in a few minutes. If you would rather split by a column value — for example one file per region or per month — see our guide on splitting a CSV into multiple Excel files, which covers pandas groupby splitting in detail.

Option B: PowerShell (no Python needed)

On Windows, this script splits by line count using a fast .NET reader instead of the slow Import-Csv cmdlet (which would try to parse everything into objects first):

$reader = [System.IO.File]::OpenText("C:\data\large.csv")
$header = $reader.ReadLine()
$part = 1; $count = 0
$writer = [System.IO.StreamWriter]::new("C:\data\part$part.csv")

while (($line = $reader.ReadLine()) -ne $null) {
    $writer.WriteLine($line); $count++
    if ($count -ge 900000) {
        $writer.Close()
        $part++; $count = 0
        $writer = [System.IO.StreamWriter]::new("C:\data\part$part.csv")
        $writer.WriteLine($header)
    }
}
$writer.Close(); $reader.Close()

This produces smaller CSVs you can then open in Excel normally or convert with our free converter. For a fully automated PowerShell pipeline that goes straight from CSV to XLSX, see our PowerShell conversion guide.

Option C: csvkit (macOS/Linux command line)

If you live in the terminal, csvkit splits cleanly while respecting quoted fields (a naive split -l can break rows that contain embedded newlines inside quotes):

pip install csvkit
# split into chunks of 900,000 data rows, keeping headers
csvstack large.csv | head -1 > header.csv   # not needed — csvsplit keeps headers
# simplest reliable route: use xsv (much faster)
xsv split -s 900000 large.csv chunks_

Method 2: Power Query + Data Model (Millions of Rows, One Workbook)

If your goal is analysis — pivot tables, charts, DAX measures — rather than scrolling through raw rows, Power Query with the Data Model is the elegant solution. Instead of loading CSV rows into the worksheet grid (where the 1,048,576 limit bites), you load them into the workbook’s in-memory analytical engine. A single XLSX can then hold several million rows, and you slice it with pivot tables that stay fast because the Data Model is columnar and compressed.

1. In Excel, go to Data > Get Data > From File > From Text/CSV and select your large CSV.
2. In the preview dialog, click Transform Data (not Load) to open the Power Query Editor.
3. Clean as needed: promote headers, set data types, remove columns you do not need. Every column you drop shrinks the model.
4. Click Close & Load > Close & Load To… — this dialog is the crucial step most people miss.
5. Choose “Only Create Connection” and tick “Add this data to the Data Model”, then click OK.
6. Build a pivot table from the Data Model (Insert > PivotTable > From Data Model) and analyze millions of rows.

Practical limits: the Data Model is bounded by your RAM and a soft guidance of about 2 GB per model. In practice, 5–10 million rows of typical business data (dates, categories, amounts) fit comfortably on a 16 GB machine because columnar compression is extremely effective on repetitive data. Text-heavy data with millions of unique strings compresses worse — drop or hash columns you do not analyze.

Two gotchas to know. First, refreshing the query re-reads the CSV, so keep the source file in a stable path. Second, colleagues opening your workbook need a modern Excel (Microsoft 365 or Excel 2016+) to use the model — the file will open in older versions but the data will be inaccessible. For the full Power Query workflow including M code and refreshable queries, read our dedicated guide to importing CSV with Power Query.

Method 3: Python Chunking for the Biggest Files

When files reach tens of millions of rows or multiple gigabytes — think server logs, IoT telemetry, or full e-commerce order histories — Python is the professional answer. Method 1 already showed chunked conversion; here is the fuller pipeline data engineers actually use, including the tricks that make it fast and safe.

Specify dtypes up front

Pandas infers column types by scanning data, which is slow and memory-hungry on huge files. If you know your schema, declare it:

import pandas as pd

dtypes = {"order_id": "int64", "customer_id": "int64",
          "region": "category", "amount": "float64"}

total = 0
for i, chunk in enumerate(pd.read_csv("orders.csv", chunksize=900_000,
                                       dtype=dtypes,
                                       parse_dates=["order_date"]), start=1):
    chunk.to_excel(f"orders_part{i}.xlsx", index=False, engine="openpyxl")
    total += len(chunk)
    print(f"part {i}: {total:,} rows done")

Using category for low-cardinality text columns (region, status, country) can cut memory use dramatically — often 5–10x for repetitive data, which is exactly what exports from US, UK, Indian, Canadian, and Australian e-commerce platforms tend to contain.

Filter before you convert

Often you do not need all 20 million rows in Excel — you need last quarter’s data, or one country’s slice. Filter in the chunk loop and only write what survives:

for i, chunk in enumerate(pd.read_csv("events.csv", chunksize=500_000,
                                       parse_dates=["ts"]), start=1):
    keep = chunk[chunk["ts"] >= "2026-01-01"]
    if not keep.empty:
        keep.to_excel(f"events_2026_part{i}.xlsx", index=False)

This is also how you beat the row limit without splitting: a 3-million-row CSV filtered to 400,000 relevant rows converts to a single, fast XLSX.

Handle encoding and delimiters explicitly

Large exports are notorious for encoding surprises — a file that is mostly UTF-8 with a few Windows-1252 smart quotes will crash a naive read halfway through. Defensive defaults:

pd.read_csv("large.csv", chunksize=900_000,
            encoding="utf-8", encoding_errors="replace",
            sep=None, engine="python")  # sep=None sniffs the delimiter

Note the engine="python" requirement when sniffing delimiters; it is slower than the C engine, so once you know the delimiter (check with a text editor or head), hard-code it and switch back to the default C engine for speed. For the complete Python reference — multi-sheet workbooks, openpyxl formatting, and every encoding fix — see converting CSV to Excel with Python.

Method 4: Use a Database Instead (When Excel Is the Wrong Tool)

Sometimes the honest answer to “how do I convert this large CSV to Excel” is “you shouldn’t — at least not all of it.” If your workflow is filter, aggregate, join, and summarize — and the raw rows are just the means to that end — a database does in seconds what Excel struggles with for minutes, and you export only the results to XLSX.

DuckDB: the zero-setup option

DuckDB is a single-file analytical database that queries CSVs directly, no server or import step needed. Install it (pip install duckdb) and run:

import duckdb

con = duckdb.connect()
# aggregate 10M rows without loading them all into RAM
result = con.execute("""
    SELECT region, date_trunc('month', order_date) AS month,
           COUNT(*) AS orders, SUM(amount) AS revenue
    FROM read_csv('orders_10M.csv', header=true)
    GROUP BY region, month
    ORDER BY month, region
""").df()

result.to_excel("monthly_revenue_by_region.xlsx", index=False)
print(f"Exported {len(result):,} summary rows")

The 10-million-row source never becomes an Excel file at all — only the few thousand summary rows do. This pattern (database for crunching, Excel for presentation) is how most professional analysts actually handle oversized data.

SQLite: the built-in alternative

Python ships with SQLite, so there is nothing to install. It is row-oriented rather than columnar, so it is slower than DuckDB for analytics, but it is perfect for filtering and joining:

import sqlite3, pandas as pd

con = sqlite3.connect("data.db")
for chunk in pd.read_csv("large.csv", chunksize=200_000):
    chunk.to_sql("events", con, if_exists="append", index=False)

q = "SELECT country, COUNT(*) c FROM events WHERE amount > 100 GROUP BY country"
pd.read_sql(q, con).to_excel("big_spenders_by_country.xlsx", index=False)

What About Smaller Large Files?

Not every big-feeling CSV is actually over the row limit. A 50–150 MB file with 400,000 rows will open in Excel — it will just be slow, and you may hit formatting quirks like leading zeros being stripped or dates being misread. For those files, skip the heavy machinery: convert directly with our free CSV to Excel converter, which runs entirely in your browser (your data never leaves your computer), then apply the fixes in our guides on preserving leading zeros and CSV date formats. The methods above are for files that genuinely exceed 1,048,576 rows or several hundred megabytes.

Troubleshooting Common Errors

Most errors happen when you try to open a big CSV file the normal way — double-clicking it and hoping for the best. Work through these fixes in order:

“Excel ran out of memory” or freezes on open

You are opening the raw CSV by double-clicking. Stop — check the row count first with wc -l or the PowerShell snippet from the introduction. If it is over the limit, use Method 1 or 2 instead of fighting the open dialog. If it is under the limit but still freezes, the culprit is usually extremely wide rows (hundreds of columns) or very long text cells; importing via Power Query with unneeded columns removed fixes both.

Rows are silently missing after conversion

This is the truncation trap: Excel loaded 1,048,576 rows and discarded the rest. Always verify with a row count after converting — compare wc -l on the source against the sum of rows across your output files. Build this check into every script you run.

Python crashes with MemoryError

You are probably calling pd.read_csv() without chunksize, loading the entire file at once. Add chunksize, declare dtype (especially category for repetitive text), and select only needed columns with usecols. Together these three changes cut memory use by an order of magnitude.

Weird characters (é, ’) in the output

Encoding mismatch. The file is probably Windows-1252 or Latin-1 rather than UTF-8 — common in exports from older US and European systems, while Indian government portals often emit UTF-8 with a BOM. Try encoding="utf-8-sig" first (handles the BOM), then encoding="cp1252", then encoding="latin1" as the last resort that never fails (it maps every byte, though not always correctly).

Dates become numbers like 45658

Excel stores dates as serial numbers — 45658 is a valid date displayed without date formatting. Select the column, press Ctrl+1, and apply a Date format. Better yet, parse dates during conversion (parse_dates in pandas, or set the type in Power Query) so the XLSX arrives correctly formatted.

Numbers show as 1.23E+11 (scientific notation)

Long numeric IDs — phone numbers, 16-digit account numbers — exceed Excel’s 15-digit precision and get mangled. The fix must happen at import: declare those columns as text, never as numbers. Our guide to CSV scientific notation in Excel covers every variant of this problem.

Performance Tips: Convert Faster

• Use an SSD and keep source and output on the same fast drive. Conversion is I/O-bound more often than people expect; a spinning hard drive can easily double the time.
• Prefer the xlsxwriter engine for pure speed. pd.ExcelWriter("out.xlsx", engine="xlsxwriter") writes significantly faster than openpyxl when you do not need openpyxl-specific features. You cannot mix both in one file, so choose per task.
• Drop columns you do not need with usecols. A 200-column export where you analyze 12 columns converts many times faster when you read only those 12.
• Avoid per-cell formatting on millions of rows. Formatting a million cells individually is catastrophically slow; format whole columns or ranges instead, or apply formatting in Excel after conversion.
• Close Excel before running big conversions. The Excel COM automation and even a background Excel instance compete for memory and file locks on Windows.
• For recurring jobs, schedule the script. A nightly Python or PowerShell job that splits and converts the day’s export means you never do this manually again.

Large files come in endless varieties — odd encodings, strange delimiters, corrupted rows — so if you hit a stubborn one, join the discussion in our community forum and share the error message; someone has almost certainly solved the same file before.