Analyzing More Than a Million Rows Without Excel Limits

Opening a CSV with three million rows in Excel fills the sheet to the limit of 1,048,576 rows and drops the rest. A message may appear, or the data just ends. Totals on the visible part are wrong, and nobody is told. The answer is not to find a way to fit the file but to analyze it somewhere that has no such limit and bring the answer back. This post compares the options: DuckDB over files, a database, chunked pandas and the Excel Data Model, with the SQL and code to do a typical analysis in each. It is a spoke of Excel files that are too big or too slow.

The limit and the symptom

An Excel worksheet holds 1,048,576 rows and 16,384 columns (Excel row and column limits). Double-clicking a larger CSV loads the first million rows. The symptom is a total that is lower than the source system's, or a last date that is earlier than expected. Check the last row of the sheet: if it is 1,048,576, you have lost data. Compare wc -l data.csv with the sheet's row count.

DuckDB: SQL on the file

DuckDB is an embedded analytical database. It reads CSV and Parquet directly, runs SQL and streams through the file, so memory use is modest. One install (pip install duckdb, or the standalone binary) and no server.

SELECT region,
       date_trunc('month', order_date) AS month,
       COUNT(*)                        AS orders,
       SUM(quantity * unit_price)      AS revenue
FROM read_csv('orders.csv')
GROUP BY region, month
ORDER BY region, month;

read_csv sniffs the delimiter, header and types; check what it decided with DESCRIBE SELECT * FROM read_csv('orders.csv'); and override anything wrong (DuckDB read_csv options). The result is a few hundred rows that fit anywhere. Export it for Excel:

COPY (
  SELECT region, date_trunc('month', order_date) AS month, SUM(quantity * unit_price) AS revenue
  FROM read_csv('orders.csv') GROUP BY ALL
) TO 'summary.csv' (HEADER);

For repeated questions, convert once to Parquet and query that. Parquet is columnar and typed, so queries that touch a few columns read a fraction of the data (converting CSV to Parquet with DuckDB). DuckDB can read a folder of files with a glob (read_csv('data/*.csv')) and join files to each other. See querying CSV files with DuckDB instead of Excel for more.

A database

If the data is queried often, joined to other tables or used by several people, load it into a database. SQLite is the easiest: a file, no server.

sqlite3 sales.db
.mode csv
.import --skip 1 orders.csv orders_raw

Then create typed tables and indexes, and query with SQL (importing CSV files into SQLite with the command line). PostgreSQL is the choice for concurrent users, large volumes and sharing, with \copy for bulk loading (load a CSV file into PostgreSQL). In either, an index on the columns you filter and group by makes repeated questions fast. The load is a one-time cost that pays back with the second query.

Excel can connect to the database for the summary, with a refresh (connecting Excel directly to a database).

pandas in chunks

When you want Python and the file is too large to hold, read it in pieces and accumulate:

import pandas as pd

totals = {}
for chunk in pd.read_csv("orders.csv", chunksize=500_000, dtype={"order_id": str},
                         parse_dates=["order_date"]):
    chunk["revenue"] = chunk["quantity"] * chunk["unit_price"]
    chunk["month"] = chunk["order_date"].dt.to_period("M").astype(str)
    part = chunk.groupby(["region", "month"])["revenue"].sum()
    for key, value in part.items():
        totals[key] = totals.get(key, 0) + value

summary = (pd.Series(totals, name="revenue")
             .rename_axis(["region", "month"]).reset_index())

Each chunk is summarized and the partial sums are combined. This works for sums, counts, minimums and maximums. It does not work directly for medians, distinct counts or percentiles, which cannot be combined from chunk results (use DuckDB or a database for those, or approximate sketches). Specify types: with chunks, pandas may infer a different type per chunk and produce mixed columns (reading large CSV files in chunks with pandas).

For Excel input rather than CSV, pandas cannot read in chunks, so convert first (converting a huge Excel file to CSV or Parquet).

The Excel Data Model

Excel itself can hold more rows than a sheet through the Data Model: load the CSV with Power Query to the model, not to a sheet, and build a PivotTable on it. The model compresses columns, so a few million rows of repeating values fit. It suits an analyst who wants to stay in Excel and whose machine has the memory (64-bit Excel). It has limits: it lives in RAM, 32-bit Excel runs out quickly, the file grows, and complex logic needs the DAX language (using the Excel Data Model for larger datasets).

Choosing

All of them return a small result. The principle is the same in each: reduce the data where it lives, then move the answer.

Check against the source

Count rows first, before analysis. DuckDB: SELECT COUNT(*) FROM read_csv('orders.csv');. Compare with wc -l minus the header, allowing for quoted line breaks. Then compare a control total with the source system. If your row count is a few lines short, a quote or line-ending problem is cutting records (escaping quotes and newlines inside CSV). A million-row file deserves the same checks as a small one, and the mistakes are harder to see by eye.

Compare the summary with the sheet-limited version too. If Excel's partial pivot shows 1,048,575 rows' worth of revenue and your full query shows more, the difference is the data you were missing, and you can say how much.

Bring the answer back to Excel

Excel is good at the last mile. A summary of a few thousand rows opens instantly, charts well and can be formatted and shared. Write the result to CSV or use a connection. Include in the summary the grain (monthly by region), the date of extraction, the source and the row count of the underlying data, so a reader knows what the figures cover. Keep the query or script with it. A number that came from three million rows should say so.

When a million rows still does not need new tools

The analysis is a single pass over a CSV. awk or csvkit can sum a column in a few seconds with no installation beyond the shell.

You only need the first rows. For a look at the structure, head -1000 into a new file is enough.

The data is nearly a million. Between a few hundred thousand and the limit, Excel may be slow but works, particularly with the Data Model and a pivot (making a pivot table from data larger than Excel allows).

The question changes daily. A repeatable pipeline into a database may be better than a different tool each time.

See DuckDB's documentation on querying CSV files and Microsoft's Excel specifications and limits. To check a small summary as JSON, use the CSV to JSON converter.