How to Open Large CSV Files That Excel Can't Handle

Why Excel Can't Open Your CSV

There are two separate limits you might hit:

• The row limit. A worksheet has 1,048,576 rows. If the CSV has more, Excel shows a "File not loaded completely" warning and silently drops the rest. It's easy to miss, and then your totals are wrong.
• Memory. Even under the row limit, a CSV with many columns can make Excel slow or unresponsive, because every cell becomes an object with formatting and type information.

Google Sheets has its own cap: a spreadsheet can hold up to 10 million cells in total, so a wide file runs out of room even sooner.

First, Decide What You Actually Need

Option 1: Look at the Data as Text

A CSV is plain text, so any tool for large text files can display it instantly. On macOS and Linux:

head -n 20 sales.csv           # header and first rows
wc -l sales.csv                # count lines (rows + header)
grep -n "ORD-55812" sales.csv  # find a specific record

If you prefer a visual tool, you can browse a big CSV in a large file viewer. It shows the file as numbered lines, so the header is line 1 and each row follows. You can search for a value across the whole file, jump to any line, and export a range of lines, for example rows 1 to 500,000, as a smaller CSV that Excel will open.

The viewer doesn't parse columns or calculate anything; it's for inspecting and extracting. That's often all you need to check a format, confirm a record exists, or hand someone a sample.

Option 2: Load It With Power Query

Excel's Power Query can import more rows than a worksheet holds by loading them into the Data Model:

1. Go to Data → Get Data → From File → From Text/CSV and choose the file.
2. In the preview, click Transform Data if you want to remove columns or filter rows first. Doing this early keeps memory use down.
3. Choose Close & Load To..., select Only Create Connection, and tick Add this data to the Data Model.
4. Build a PivotTable from the Data Model to summarise all the rows.

You can't scroll through those rows in a worksheet, but you can count, sum and pivot them. Performance depends heavily on your computer's memory and whether you're on 64-bit Excel.

Option 3: Query It Like a Database

For serious analysis, a database engine is much faster than a spreadsheet. DuckDB can query a CSV file directly without importing it first:

-- in the DuckDB command-line shell
SELECT region, COUNT(*) AS orders, SUM(amount) AS revenue
FROM 'sales.csv'
GROUP BY region
ORDER BY revenue DESC;

You can also write the filtered result to a new CSV that Excel can handle:

COPY (SELECT * FROM 'sales.csv' WHERE order_date >= '2026-01-01')
TO 'sales_2026.csv' (HEADER);

SQLite and Python's pandas or polars libraries are good alternatives if you already use them.

Option 4: Split It Into Smaller Files

If the person who needs the data insists on Excel, split the CSV into chunks below the row limit, repeating the header in each. How to split large text files shows how to do that safely on every platform. Keep in mind that splitting makes cross-file totals more awkward.

Common Large-CSV Pitfalls

Leading Zeros and Long Numbers

When Excel opens a CSV directly, it guesses types. ZIP codes lose leading zeros, and numbers longer than 15 digits (such as card-like IDs or some order numbers) lose precision. Importing through Power Query and setting those columns to Text avoids it.

Commas and Line Breaks Inside Values

Quoted fields can contain commas and even line breaks. Simple line-based tools like head or split -l don't understand quoting, so a row containing a line break can be split in half. Check whether your data has multi-line fields before relying on line counts as row counts.

Encoding

Excel may misread UTF-8 files without a byte order mark, producing garbled accented characters. Power Query lets you choose the file origin (encoding) explicitly.

Different Delimiters

Despite the name, many "CSV" files use semicolons or tabs, especially in European locales. Check the first line before importing.

When CSV Isn't the Right Format

If you're producing the data yourself, consider whether CSV is the best choice. JSON vs CSV vs Excel compares the formats, and if your source is JSON, a JSON to Excel converter can create a proper XLSX file with typed columns.

Summary

Use a viewer to look, Power Query or a database to analyse, and splitting only when someone needs the rows in Excel. Whichever you choose, watch out for leading zeros, long numbers and quoted line breaks.