Opening a CSV File That Is Too Large for Excel or Sheets

You double-click a CSV. Excel thinks for a while, shows a million rows and a warning, and the last rows are missing. Or it does not open at all. The data is not damaged; the spreadsheet has a size limit, and the file exceeds it. The right response is not to find a bigger spreadsheet but to shrink the question: you rarely need twenty million rows on a screen. This article lists the ways to get the part you need into a file that Excel can open, with commands that work on files of any size.

The limits

Excel's specifications and limits page gives 1,048,576 rows and 16,384 columns per worksheet and 32,767 characters per cell. Google Sheets has its own cap, on cells in total (check the current figure in its help pages). A CSV with more records than a sheet can hold will be cut off, and Excel's warning about it is easy to dismiss. Two traps come with it: a file with a header and 1,048,576 data rows needs 1,048,577 rows and loses the last one, and a library that writes the workbook for you can lose rows without telling you. XlsxWriter returned -1 for a row past the limit and raised nothing, as the workbook article found.

Step 1: look without opening

head -n 20 orders.csv                 # the first rows
head -n 20 orders.csv | column -s, -t # aligned, if no field has a comma
wc -c orders.csv                      # the size

Or with DuckDB, which reads only what the query needs:

SELECT * FROM read_csv('orders.csv') LIMIT 20;
DESCRIBE SELECT * FROM read_csv('orders.csv');
SELECT count(*) FROM read_csv('orders.csv');

count(*) is the number to compare with 1,048,576. On the 20,000,000-row test file DuckDB counted the records in about a second, and wc -l is wrong by the number of quoted line breaks (counting lines).

Step 2: take the part you need

Most of the time you want rows that match something, or totals. Write that as a query and save the result:

-- the rows for one country and month
COPY (
  SELECT * FROM read_csv('orders.csv')
  WHERE country = 'BR' AND created_at >= '2025-03-01' AND created_at < '2025-04-01'
) TO 'orders-br-march.csv' (FORMAT csv, HEADER);

-- or a summary, which is what a pivot table would have made anyway
COPY (
  SELECT country, status, count(*) AS orders, round(sum(amount), 2) AS revenue
  FROM read_csv('orders.csv') GROUP BY 1, 2 ORDER BY 1, 2
) TO 'orders-summary.csv' (FORMAT csv, HEADER);

The summary has 35 rows for seven countries and five statuses and opens instantly. A grouped query over the 1.3 GB, 20,000,000-row file took 1.23 s in DuckDB. This is the answer in most cases: the spreadsheet was never the right place to aggregate twenty million rows, and a person reading the result wants the totals.

A random sample is the right extract when you need to look at examples; see sampling for ways that keep rows whole. If the row count after filtering is still above the limit, tighten the filter or split.

Step 3: split into files that fit

If someone really needs all the rows, cut them into pieces under the limit, 1,000,000 per file is a round number that leaves room for the header:

split -l 1000000 -d --additional-suffix=.csv orders.csv part_

That cut the test file into 21 parts in 3.64 s. Only the first part has the header, so for parts that each open on their own, use a tool that repeats it. qsv split --size 1000000 outdir orders.csv took 6.79 s, awk 22.83 s and Miller 35.48 s, and the splitting article compares them and shows what happens when a cut falls inside a field with a line break. For a file you split by line count, remember that a part with 1,000,000 lines may hold fewer records if fields contain breaks, which is safe, but a cut through the middle of a record is not.

Step 4: tools that open big files

There are programs made for this, some of which I have not tested: a database browser that imports the CSV (DuckDB's shell, SQLite's .import, DBeaver), a pager that handles huge text files (less -S, vim with care), and CSV viewers such as csvlook or visidata for the terminal. The terminal preview article covers the simple ones. For anything beyond a look, a database with a SQL console is a better spreadsheet for big data than a spreadsheet.

Step 5: if the receiver must use Excel

Ask what they will do with it. If the answer is a pivot table, build the summary yourself. If it is a lookup, send a keyed extract. If it is a review of every row, split, and say how many parts there are. Whatever you send, encode it so it opens correctly: UTF-8 with a byte order mark is the usual compromise for Excel, and the encoding article explains why. Keep identifiers as text where you can, since Excel will drop leading zeros from a zip code when it converts the column to numbers (Excel and leading zeros).

A checklist before you send it

Whoever receives the extract will not have your context, so put it in the file name and a note. Name the file for what it holds (orders-br-march.csv, not export2.csv), say how many rows it has, and say what was left out. If the extract is a sample or a filter, write the condition down; a spreadsheet looks the same whether it holds all the data or a tenth of it, and people will quote totals from it. If you split a file, number the parts and give the part count, and consider a small manifest listing each part's row count so the receiver can notice a missing one.

Also decide how the receiver should handle the columns that spreadsheets damage. Long numeric identifiers lose digits past 15, zip codes lose leading zeros, and values such as 1-2 or MAR1 become dates. If you control the extract, add a leading apostrophe or format the column as text in a workbook instead of a CSV (the workbook article shows how), or tell the receiver to import with text columns rather than double-clicking. A five-line note saves an afternoon of confusion.

How to tell it worked

Open the result and look at the last row, not the first: if it matches the last record of your extract, nothing was cut. Compare the row count in the sheet with count(*) from the query. Check a number you know, such as a total. And keep the query or the split command with the file, so the extract can be rebuilt.

What I could not check without Excel

How long a given machine takes to open a file near the limit, what the warning dialog says in your version, and whether Sheets accepts the same file are all things I did not test. The commands that make a smaller file are the reliable part. If a colleague tells you the file "opened fine", ask for the row count they see: if it is exactly 1,048,576, it did not. The large-file hub puts these steps in order.

Further reading: Excel specifications and limits; DuckDB CSV import.