Guides

How to Open a CSV File That's Too Big for Excel

By the Opener team6 min read

An Excel worksheet holds at most 1,048,576 rows and 16,384 columns, so a 3-million-row export simply does not fit — Excel loads the first part, warns you, and leaves the rest behind. To see the whole file, open it in a tool without that limit, such as the CSV viewer, load it into Excel’s Data Model instead of a sheet, split it, or analyse it with Python or a database.

Why Excel cuts the file off

A CSV file has no size limit of its own. It is plain text: one line per row, values separated by commas or semicolons (if that is new to you, see what a CSV file is). Excel, however, puts everything you open into a worksheet, and a worksheet has a fixed grid: 1,048,576 rows by 16,384 columns (column XFD).

When the file is bigger than that grid, Excel shows a warning that the file was not loaded completely and keeps only the rows that fit. The header plus 1,048,575 data rows make it in; row 1,048,577 onward is gone from what you see on screen.

Google Sheets is no escape here. It has a limit of 10 million cells per spreadsheet, counted across all sheets. A file with 3 million rows and 10 columns is already 30 million cells, so it will not import either.

The real risk: saving loses data

The warning is easy to click away, and what is left on screen looks like a perfectly normal table. The danger comes later:

If Excel has already shown the warning, close the file without saving and keep the original export untouched. Then check how big it really is before deciding what to do.

Option 1: open the whole file in your browser

If you mainly need to look at the data — check a few records, find a customer, see what a column contains — a viewer without a grid limit is the quickest route.

The Opener CSV viewer reads the file in your browser and never uploads it, which matters when the export contains customer or financial data. Parsing runs in a background thread and only the rows on screen are drawn, so a 200,000-row file opens in about a second and files with millions of rows work on a normal laptop. The practical limit is your device’s memory, not a fixed row count.

Once it is open you can:

That last point is a handy trick: filter 3 million rows down to the 40,000 that mention one region, export them, and the smaller file opens in Excel without any warning. The viewer does not run formulas, pivots or charts; for that, read on.

Option 2: load it into Excel’s Data Model

Recent desktop versions of Excel can import a CSV through Power Query (the “Get Data” / “From Text/CSV” route on the Data tab) instead of opening it directly. The difference is where the data ends up.

Instead of loading the result into a worksheet, choose to load it as a connection only and add it to the Data Model. The Data Model stores the data in a compressed form outside the grid, so it is not bound by the 1,048,576-row limit. You can then build a PivotTable on top of it and summarise all 3 million rows — totals per month, counts per category, and so on.

The trade-off: you cannot scroll through the rows like a normal sheet, and the exact menu labels vary between Excel versions. It is the best option when you need to stay in Excel and the end result is a summary rather than a row-by-row view.

Option 3: count, preview and split from the command line

On macOS, Linux or Windows with WSL, a few standard commands answer “how big is it?” and “what’s in it?” in seconds, without loading the file into anything:

wc -l export.csv              # number of lines
head -n 20 export.csv         # first 20 lines
head -n 100000 export.csv > sample.csv   # a smaller sample to try things on

Note that wc -l counts lines, not rows. If some values contain line breaks inside quotes (addresses and comments often do), the line count will be higher than the row count.

To split the file into pieces Excel can open, each with its own header row:

tail -n +2 export.csv | split -l 1000000 - part_
for f in part_*; do
  { head -n 1 export.csv; cat "$f"; } > "$f.csv" && rm "$f"
done

Splitting is fine for quick checks, but be careful with the same quoted-line-break issue: split cuts by lines and can break a multi-line value in half. And working across four separate workbooks is clumsy if you need totals over all of them.

Option 4: analyse it with pandas, SQLite or DuckDB

If you are going to calculate, join or aggregate, a proper data tool is the honest answer. All of these handle tens of millions of rows on an ordinary laptop.

Python with pandas reads the whole file into memory:

import pandas as pd

df = pd.read_csv("export.csv")
print(len(df))
print(df.groupby("region")["amount"].sum())

If the file is larger than your RAM, read it in chunks:

total = 0
for chunk in pd.read_csv("export.csv", chunksize=500_000):
    total += chunk["amount"].sum()

DuckDB can query a CSV file directly with SQL, without importing it first:

SELECT region, COUNT(*) AS orders, SUM(amount) AS revenue
FROM 'export.csv'
GROUP BY region
ORDER BY revenue DESC;

SQLite is installed on many systems and works well when you want to keep the data in one queryable file:

sqlite3 sales.db
sqlite> .mode csv
sqlite> .import export.csv sales
sqlite> SELECT COUNT(*) FROM sales;

A good workflow is to do the heavy lifting here and then write a small result — a few thousand summary rows — back to CSV for Excel or a report.

Frequently asked questions

Can I raise Excel’s row limit?

No. The 1,048,576-row limit is fixed for every worksheet. You can only work around it: load the data into the Data Model, split the file, or use another tool.

How do I know if my file was truncated?

Excel warns when it opens the file, and a sheet whose data ends exactly at row 1,048,576 is a strong sign. To be sure, check the size of the original file with wc -l or open it in the CSV viewer, which shows the total row count.

Will Google Sheets or LibreOffice open it instead?

Usually not. Google Sheets is capped at 10 million cells per spreadsheet, and LibreOffice Calc also has a fixed maximum number of rows per sheet. Both are fine for smaller files, but a multi-million-row export needs a viewer, a database or a script.

Is it safe to open a large customer export in a browser tool?

It depends on the tool. Many online converters upload your file to a server. Opener reads the file locally in your browser and sends nothing anywhere, so the data stays on your device.