AngoraTek FR
← Back to Blog
Tutorials 6 min read

Excel Stops at 1,048,576 Rows. Your CSV Does Not.

This page is for the person whose spreadsheet has turned into a loading spinner: a timetable export with millions of lines, or two files that need a join no spreadsheet window can hold. The number to know first is 1,048,576, the exact count of rows one worksheet accepts, after which an import stops at whatever fits and the rest never arrives, with a warning you can click past. The escape hatch that follows is one command, and it is called DuckDB.

The claim this tutorial runs on: you do not need a warehouse, a server, or an account to ask useful questions of files that size. DuckDB is an in-process database that reads CSV, Parquet, and even .xlsx files where they sit, streams them instead of loading them, and answers in SQL on a plain laptop. Install it from duckdb.org, or with brew install duckdb, and open the CLI. Run these from the folder where your GTFS feed is unzipped. Our running example is a transit agency's timetable, because GTFS feeds are public, oversized on purpose, and shaped like the files that break spreadsheets. Your counts in every query below will differ per feed, so run them instead of trusting ours.

One command reads the file where it sits

No import wizard, and no schema picker either. Point DuckDB at the path:

SELECT count(*) AS rows_in_feed
FROM read_csv_auto('stop_times.txt');

The engine sniffs the separator, the header, and the column types, streams the file in, computes, and keeps nothing on disk unless you ask. The answer to that first count is usually the moment of conversion, because the operation is one process running on the laptop the file already lives on.

The sniffer is good and it is not psychic, a feature worth meeting early. In a GTFS feed, a vehicle's departure_time may legitimately read 24:15:00, because the GTFS schedule spec lets times run past midnight to cover late service. No type system called TIME accepts that anywhere else, so DuckDB leaves the column as text and you take the hour yourself with split_part. A spreadsheet doing the same conversion will mangle 24:15:00 into a time on the wrong day, or refuse, depending on the version. The database declines to guess, tells you, and moves on. When the vendor report arrives as .xlsx instead, the path is the same shape:

SELECT * FROM read_xlsx('vendor-report.xlsx');

The excel extension autoloads on first use, reads the first sheet by default (name another with sheet = '...'), and writes .xlsx as well, which matters at the end of this tutorial.

The join is the thing the spreadsheet cannot hold

Excel's hard part is rarely arithmetic on one sheet; it is two files past a million rows that need each other. stops.txt, the small file, names every stop. stop_times.txt, the giant, records every departure at every stop, and it is the file the import wizard chokes on. The join asks which stops are busiest, and in SQL it is one query with everything streamed:

SELECT s.stop_name, count(*) AS departures
FROM read_csv_auto('stops.txt') AS s
JOIN read_csv_auto('stop_times.txt') AS st USING (stop_id)
GROUP BY s.stop_name
ORDER BY departures DESC
LIMIT 20;

The same trick answers the question people actually open the spreadsheet for, the shape of the day. When does demand concentrate? Group the departures by hour and look:

SELECT split_part(departure_time, ':', 1)::INTEGER AS departure_hour,
       count(*) AS departures
FROM read_csv_auto('stop_times.txt')
GROUP BY 1
ORDER BY 1;

Hours 24 and above appear in the output because GTFS times can pass midnight, and they are real data, not a problem. In the spreadsheet those rows were either mangled or dropped; here they line up quietly at the end of the histogram and tell you how much of the network keeps running after the clock turns.

The second-file problem cuts the same way. Excel's classic answer to a cross-file question is VLOOKUP, which requires both files open and requires the giant one to fit, or Power Query, which tries to bring it in. DuckDB streams the scan and joins without needing the giant file to fit in a worksheet. The join that was a two-day project of waiting for load bars becomes one line whose plan you can print with EXPLAIN if you want to watch it prune.

The answer goes back into the spreadsheet, not the data

Twenty rows of ranked stops fit in a worksheet, an email, or a slide, and people still need to touch them. Export the small answer, not the giant source:

LOAD excel;
COPY (
    SELECT s.stop_name, count(*) AS departures
    FROM read_csv_auto('stops.txt') AS s
    JOIN read_csv_auto('stop_times.txt') AS st USING (stop_id)
    GROUP BY s.stop_name
    ORDER BY departures DESC
    LIMIT 20
) TO 'busiest-stops.xlsx' WITH (FORMAT xlsx, HEADER true);

Two details make this the honest round trip. The excel writer DuckDB ships has a sheet_row_limit whose default is 1,048,576, Excel's own ceiling, because a written sheet beyond that ceiling would be unreadable to the software it is meant for. Our twenty rows never approach it. And the source file stays where it was, unmodified, since nothing was ever loaded. The spreadsheet keeps the answers for the humans, and SQL keeps the questions. That division of labor is the whole argument in one sentence.

Repetition changes one thing: format. When the same file is queried weekly, copying it once to Parquet makes every later run cheaper, because Parquet keeps column statistics and a real schema instead of re-sniffing text:

COPY (SELECT * FROM read_csv_auto('stop_times.txt'))
TO 'stop_times.parquet' (FORMAT PARQUET);

Afterwards, queries read FROM 'stop_times.parquet' and skip both the sniffing and, on hour-level questions, most of the file, and the one-off script becomes a small reproducible pipeline with a one-line source row in it.

What we tried first, and what this tool cannot do

Our first instinct on requests like the busiest-stops question was to assemble infrastructure: a Postgres instance, a schema, a load job, because a join implies a database. The pivot came from noticing the question was asked once, and the file already sat on the laptop. The warehouse earned its keep only when the questions became scheduled and the results became a product. Until then it was a week of setup wrapped around the same query. Constraint first, then tooling, and rarely in the order people expect.

The honest limits are about sharing, not capability. DuckDB is in-process and single-writer: two colleagues querying the same database file simultaneously is either a read-only affair or a future argument, so a team that needs concurrent writes is a team for Postgres, not this. A spreadsheet cell that recomputes when its source changes has no equivalent here. The query is recomputed by re-running it, scheduled or otherwise. And none of this is a serving database: a customer-facing application has no business reading a CSV from disk per request. DuckDB answers you; it does not answer your users.

So the decision reads like a handoff. If one file outgrows the worksheet, or two files need a join and neither fits, run one query before asking anyone for infrastructure. Keep the spreadsheet for the twenty rows a person reads, keep the giant files as files, and let the escape hatch stream past the one-million-row ceiling it was never told about.

AngoraTek helps small teams put questions like these on real ground, files and laptops included. Show us the spreadsheet that spins and we will run the first query with you.

Thanks for reading this far. If this sounds like the problem you're wrestling with, tell us about it. The team reads every message.

Start a conversation