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

Excel has a fixed grid size, and a big export can be larger than that grid. This post covers what goes wrong and the options you have.

The problem: Excel's hard limit

An Excel worksheet stops at:

• 1,048,576 rows (2^20)
• 16,384 columns (A to XFD)

Those limits apply to every version since Excel 2007, including Microsoft 365. When you open a CSV with more rows than that, Excel shows a "File not loaded completely" warning and silently drops everything past row 1,048,576. If you then save the file, the missing rows are gone from the saved copy too. That is the real danger: the file looks fine, but it is incomplete.

The same applies to Google Sheets in a different way: it has a limit of 10 million cells per spreadsheet, so a 20-column export tops out at 500,000 rows.

Option 1: a viewer that does not have a grid limit

The Fix42 CSV Viewer pages through the file instead of loading it into a spreadsheet grid:

1. Open the viewer and drop your CSV on it.
2. Files under 500MB are parsed in your browser. Files from 500MB up to 10GB are uploaded to our server, indexed, and deleted automatically after 24 hours.
3. Page through the rows (up to 5,000 per page), sort a column, hide columns you do not need, and search.

One thing to know: for files parsed in the browser, search, sort and export work on the page you are looking at. For files processed on the server, "Search All" searches the whole file and you can export every row or only the matching rows.

How fast a browser-side file opens depends on your machine's memory, because the whole file is held in the tab while you work with it.

Option 2: filter before Excel loads it

Often you do not need all 3 million rows, you need the 40,000 rows for one region or one month. Excel's Power Query can filter a CSV before it reaches the grid:

1. Data > Get Data > From File > From Text/CSV, pick the file.
2. Click Transform Data instead of Load.
3. Filter the column you care about (region, date, status) using the dropdown on its header.
4. Close & Load. Only the filtered rows land in the sheet.

Power Query reads the whole file while filtering, so it works on files far bigger than the grid. You keep formulas and pivot tables, and you are working with every row that matters instead of an arbitrary first million.

Option 3: Excel's own Data Model

If you need totals across all the rows, load the CSV into the Data Model: same steps as above, then Load To... > Only Create Connection with "Add this data to the Data Model" ticked. The Data Model is not bound by the 1,048,576 row grid, and you can build pivot tables on top of it. You cannot scroll through the rows like a normal sheet, but for totals and summaries it works well.

Things that trip people up

Garbled characters. The browser viewer reads files as UTF-8 and strips a UTF-8 byte order mark if one is present. A file exported in an older encoding such as Windows-1252 will show accented letters as junk. Re-save it as "CSV UTF-8" from the program that made it.

Wrong columns. The viewer guesses the delimiter (comma, semicolon, tab or pipe) from the header row. European Excel exports often use semicolons, which it handles. If your header row contains lots of commas inside quoted names, the guess can be wrong.

Line breaks inside cells. Addresses and notes fields often contain line breaks inside quotes. The viewer keeps those as one row, but some text editors and command line tools will split them, so row counts can differ between tools. For the same reason, wc -l on a file is a line count, not a row count.

Mixed line endings. Windows writes \r\n at the end of each line, Linux and macOS write \n, and very old Mac software wrote a bare \r. Files stitched together from several sources can contain more than one kind. The browser viewer accepts all three, and skips blank lines.

Long numbers and leading zeros. This is the Excel problem people notice last. Excel stores numbers to 15 significant digits, so a 16-digit account or card number such as 1234567890123456 is stored as 1234567890123450 and displayed as 1.23457E+15. ZIP codes and product codes lose their leading zeros (01234 becomes 1234). Saving the file afterwards writes the damaged values back. To avoid it, import through Data > From Text/CSV, choose Transform Data, and set those columns to Text before loading. The Fix42 viewer does not convert values when it parses in the browser: every cell is shown as the text in the file. For large files processed on the server, the data is loaded into DuckDB, which guesses a type for each column. In a test file I ran through the same DuckDB settings, a 16-digit ID column was kept intact and a ZIP column with leading zeros stayed as text, but if exact values matter, spot-check a few rows.

Dates that change. 01/02/03 can mean 1 February, 2 January or 3 February depending on locale, and Excel converts what it thinks are dates on opening. The same Text column trick in Power Query stops that. The browser viewer shows dates exactly as written.

Quotes inside fields. The CSV convention (RFC 4180) is that a field containing a comma, quote or line break is wrapped in double quotes, and a quote inside it is doubled: "He said ""hi""". Some programs write \" instead. The viewer follows the doubled-quote rule, so a file that uses backslash escapes can have fields split in the wrong place. If one column suddenly contains fragments of the next, that is the usual cause.

Rows with the wrong number of fields. A stray comma or an unquoted field gives a row more cells than the header, and a truncated export gives fewer. In the browser the viewer keeps such rows as they are rather than refusing the file. On the server, DuckDB is run with settings that pad short rows with empty values and skip rows it cannot parse at all. In my test, a row with one extra field added an extra column instead of being dropped. If the row count matters, compare it with the source system's count.

Which to use

• Under about 1 million rows: Excel is fine.
• Over 1 million rows and you need to look through or search the data: a paging viewer.
• Over 1 million rows and you need totals or pivots: Excel's Data Model, or a database such as SQLite or DuckDB if you do this often.