Large CSV files ยท Windows
How to open a CSV file that is too large for Excel
An Excel worksheet holds at most 1,048,576 rows. When a CSV file has more, Excel opens the first 1,048,576 rows, warns that the file was not loaded completely, and leaves the rest out. Saving that workbook back over the CSV loses the missing rows.
Which way works best depends on whether you need to read the rows, analyse them, or change them. These are the common options on Windows.
1. Load it with Power Query
In Excel, use Data > Get Data > From Text/CSV, then Load To and choose the Data Model or a connection only. The rows stay in the data model, not on a worksheet, so the row limit does not apply. You can filter and summarise with PivotTables. You cannot scroll through every row on a sheet, and very large files take time and memory to load.
2. Split the file into parts
Cut the file into pieces under the row limit and open each in Excel. This works when the rows are independent. Keep the header row in every part, and take care with quoted fields that contain line breaks: a tool that splits on every line break will cut such a record in two.
3. Query it with DuckDB
DuckDB runs SQL directly on a CSV file without importing it first.
For example, SELECT * FROM read_csv('data.csv') LIMIT 100;
shows the first rows, and a WHERE or
GROUP BY clause filters or summarises the whole file.
This suits people who are comfortable with SQL.
4. Read it in chunks with Python
With pandas, pd.read_csv('data.csv', chunksize=1_000_000)
reads the file one block of rows at a time, so you can filter or
aggregate a file larger than memory. This needs Python and some
scripting.
5. Open it in a large-file viewer
If you only need to look at the rows, search them, or copy a few out, a viewer built for large files avoids loading the whole file. LeanRows is one of these: a free, open-source, read-only viewer for Windows 10 and 11. It reads the file in blocks, keeps only the visible rows in memory, shows the first rows while it is still indexing, and can search, jump to a row number, and sort by a column without changing the file.
On the developer's Windows PC, LeanRows fully indexed a generated CSV of 20,000,001 rows and 1,532,454,643 bytes (1.5 GB) with a peak working set of 18.4 MB. The 0.2.0 release notes record two more runs on a 20,000,001-row CSV at 18.15 to 18.18 MiB. The PC's hardware is not recorded. LeanRows does not edit files, calculate formulas, or run on macOS or Linux. Other large-file viewers and text editors exist too, some of them paid; compare their limits before you choose.
See LeanRows and how to install it
Which one to use
To analyse the data inside Excel, use Power Query. To query or transform it repeatedly, use DuckDB or Python. To look at the rows quickly without writing anything, use a viewer. Splitting the file is a last resort, because it is easy to break quoted records.