Open a CSV too large for Excel
An export of three million rows: Excel opens just over a million, warns, and ignores the rest. You have to filter or split it first, or not go through a sheet.
In Excel, step by step
Filter on import
Data, From Text/CSV, Transform Data: filter on the period or region you want, then Load.
Load into the data model
Load To, tick "Add this data to the Data Model": rows are not in a sheet, but a pivot table reads them all.
Split it
Cut the file into chunks of fewer than a million rows, and open each.
Pitfalls
- Opening the file directly loses the rows beyond the limit, and a total calculated afterwards is wrong without any error.
- A multi-gigabyte file can exhaust the memory of 32-bit Excel well before the row limit.
- An ordinary text editor freezes on a file of that size.
Faster, on the whole file
Open a large file reads a multi-gigabyte CSV from your disk, in chunks: browse, filter, sort and export what fits in Excel. Split a file cuts it into files of the size you want.
Frequently asked questions
- How many rows can an Excel file hold?
- 1,048,576 rows and 16,384 columns per sheet.