Problem
How to Open a Large CSV File When Excel Won't
You double-clicked the file, Excel thought about it for four minutes, and then either gave up or showed you something that is missing most of your data.
The short answer
Open it in something that reads the file from disk instead of loading it into memory. In DataTray, open Data Explorer and drop the file in — it counts the rows, shows you the detected delimiter and encoding, and starts displaying data. Filter down to what you actually need, then export just that. Free, no row limit, and nothing is uploaded.
Two different failures, and people confuse them
The row ceiling. A worksheet holds exactly 1,048,576 rows. This is not a memory limit or a version limit — it is the size of the grid, and no setting changes it.
Running out of memory. Long before the ceiling, Excel is loading the entire file and building a full object model over it. A 900 MB CSV becomes several gigabytes of working set, and the machine starts swapping.
Most people hit the second one first and blame the first.
The dangerous version of this problem
If your file has more than 1,048,576 rows, Excel does not refuse it. It opens the first 1,048,576 rows and warns you in a dialog that is one click from gone.
The file then looks open. Your filters work. Your SUM returns a number. That number is wrong,
and nothing on screen says so. This is the single best reason to stop opening large exports in a
spreadsheet at all — not that it fails, but that it half-succeeds.
Why a viewer that pages is different
DataTray does not load the file. It queries it where it lies, pulls the rows currently on screen, and re-runs filters and sorts against the file rather than against a loaded array.
The practical consequence: file size stops being the thing that decides whether you can look at your data. A multi-gigabyte CSV scrolls like a small one, and finding the eleven rows you care about in four million takes about as long as finding them in four thousand.
Get out the part you need
Usually you do not want the whole file in a spreadsheet — you want a slice of it. So the useful move is:
- Open the file and look at the columns to see what is actually in there.
- Filter to the rows that matter, and drop the columns that do not.
- Export exactly what you are looking at, as
.xlsxor a smaller CSV.
That result opens in Excel instantly, and it is the file you actually wanted.
If you genuinely need all of it in Excel
Split it. Data Converter splits by row count, by file size, or by a column's distinct values — one pass writing a folder of parts, with the header repeated in every one. Twelve regional files of 400,000 rows each are far more useful than one file Excel cannot open.
Other formats, same problem
The same applies to a 40-sheet workbook, a folder of Parquet files, or a JSON export that is mostly newlines. All of them open in the same viewer, and none of them care how large they are.
Every other free way to do this asks you to upload the file first. A file that is too big for Excel is also too big to want to hand to a website you have not heard of.
Do this in one pass with — Data Explorer
Read-only, always. Open a CSV, Excel workbook, Parquet directory or JSON file of any size and look at the rows — then switch to Columns for a full data profile: what every column actually holds, where the nulls and the mixed types and the outliers are, a data-quality read on each one, and a data dictionary you can hand to someone. A third tab takes SQL if you want it, and never asks you to write any if you don’t. Nothing here modifies the file; the only things written are the exports and reports you ask for. Learn more about the Data Explorer.
Windows 10/11 (x64) · Free · No account