Opening a CSV With More Than a Million Rows in Excel

Excel has a hard ceiling of 1,048,576 rows per worksheet, which is 2^20, and a CSV longer than that opens anyway. It shows a warning that the data set is too large for the grid, loads the first million or so rows and drops the rest. If you then press save, the sheet overwrites the file with the shortened version, and the rows beyond the limit are gone for good. People lose data this way because the dialog is easy to click through and the first screen looks entirely normal. The fix is to decide what you need from the file before you open it, because most of the time the answer is not "all the rows in a grid". This article is part of a series on the traps in opening CSV files in Excel and Sheets without damage.

What happens at the limit

Take a file with 1,100,000 data rows and a header. In Excel, that is 1,100,001 lines against a sheet that holds 1,048,576, with row 1 for the header. Excel loads the first 1,048,575 data rows, shows a message that the data set is too large for the Excel grid and warns that saving the workbook will lose the data that was not loaded. The rows missing are 1,100,000 minus 1,048,575, which is 51,425, and they are the last ones in the file, so the most recent period of a time-ordered export is what vanishes. The column limit is 16,384, and a wide survey export can hit it first.

You will not see the loss unless you look. The sheet ends on a normal-looking row, and a total computed over the visible rows is wrong by exactly the missing part. For a time series, a chart ends a few weeks early and nobody asks why.

Count before you open

The cheapest defense is a number. Run wc -l on the file before opening it:

wc -l big.csv

 1100001 big.csv

That is the line count: 1,100,000 data rows plus the header. If the count is within a few percent of 1,048,576, expect trouble. Two cautions about the count. A file whose quoted fields contain newlines has more lines than records, so the real row count is lower and a file that looks too big may fit (checking the row and column counts of a CSV). And a file with a trailing blank line gives a count one too high, which does not matter at this scale.

Option one: answer the question outside Excel

Ask what you wanted to do with the million rows. If it was a total, a count by group, a filter or a top ten, a query does it without any grid. DuckDB reads the CSV directly:

SELECT count(*) AS n, sum(amount) AS total FROM 'big.csv';
SELECT region, count(*) AS n, sum(amount) AS total FROM 'big.csv' GROUP BY region ORDER BY region;

On the test file, the first query returned 1100000 rows and a total of 549450000 in about 0.16 seconds, and the second gave 275,000 rows for each of four regions:

east   275000  137500000
north  275000  136950000
south  275000  137225000
west   275000  137775000

The result is small enough to paste into a workbook, which is the right division of work: the database aggregates, the spreadsheet presents. The files are read in place with no import step (querying a CSV file with DuckDB instead of loading it), and the same query on a 5 GB file takes seconds to a minute, not an afternoon. If you need to filter to a subset and look at it in Excel, COPY (SELECT * FROM 'big.csv' WHERE region = 'north') TO 'north.csv' (HEADER) writes a file that opens fine.

Option two: split into pieces under the limit

Sometimes the work really is row by row, and the file has to be cut. split does it by line count, and it has a gotcha:

split -l 1000000 big.csv part_
wc -l part_*
head -2 part_ab

 1000000 part_aa
  100001 part_ab
 1100001 total
1000000,north,0
1000001,south,1

The second piece starts with a data row, not the header. Only part_aa has column names. Add the header to each later piece (head -1 big.csv | cat - part_ab > part_ab.csv) before opening it, or Excel will treat the first data row as a header and every column name will be a number. A second way to split is by value, which is usually more useful than by line count, because each piece then means something: one file per month, per region or per customer, written by a query with a WHERE per piece (splitting a large CSV into smaller files).

Keep the pieces under about 900,000 rows if you will add formulas, since formula columns and filters slow a sheet far below the row limit.

Option three: load into the Data Model

When you do need to analyze all the rows in Excel, Power Query can bring them in without placing them on a sheet. Use Data > From Text/CSV, choose Transform Data, set the column types, and then Close & Load To, selecting Only Create Connection and ticking Add this data to the Data Model. The rows are held in the Data Model, a compressed in-memory store, and you build PivotTables and measures over it. A few million rows of a handful of columns work on an ordinary laptop, and memory is the limit, not the 1,048,576 rows.

The limits of this route are real. You cannot scroll the rows like a sheet, you cannot edit them, and the model is rebuilt from the file on refresh. It is for analysis and not for editing, and the workbook holds a compressed copy of all the data, so it can become large. Importing a CSV into Excel with Power Query shows the query steps that come before the load.

Option four: take a sample

If you only need to get a feel for the file, a sample is enough. The first 100,000 lines are quick to take: head -n 100001 big.csv > sample.csv. The first lines of a time-ordered file are the oldest ones and may differ from the rest, so a random sample is more representative, and shuf or a DuckDB USING SAMPLE 100000 ROWS query produces one (sampling random rows from a huge CSV). A sample is for looking, never for totals.

Check what you did

Whichever route you took, finish with a count that reconciles. The rows in the pieces add up to the rows in the original (here 1,000,000 plus 100,001 equals 1,100,001 lines), the aggregates match a second computation, and nothing was saved over the original file. Keep the original read-only, or work on a copy, so that a mistaken Ctrl+S cannot do harm. When the workbook is the deliverable, say in a note on the first sheet which file it came from and how many rows it held.

When one sheet is the wrong container

A million-row table is a database table. If you open this file every week, load it into DuckDB, SQLite or PostgreSQL once and query it there; a spreadsheet is the presentation layer. The same goes for any file that people edit: a CSV of this size should not be hand-edited in a grid at all, and changes belong in a script that can be reviewed and repeated.

Google Sheets will not rescue you. Its cap is 10 million cells across the workbook, which is 3.3 million rows of this three-column file, but it slows down long before that, and the browser tab is the bottleneck. The official figures for Excel are in the Excel specifications and limits page, and DuckDB's reader is documented in its CSV overview. For a first look at a file that is too big to open at all, see handling CSV files that are too big.