Handling CSV Files Too Big to Open in Excel or Python

A file arrives that is too large to open. The spreadsheet freezes, or the notebook is killed, or the editor gives up. The reflex is to find a bigger machine or a smarter way to open it. The better move is to stop trying to open it. A large CSV is a table in a text file, and you can ask it questions without ever holding it in memory. This guide is a decision path: what to do first, how to know what is in the file, which tool to use for which question, and what each costs, with numbers measured on one 1.3 GB, 20,000,000-row file. The articles linked from each section go into detail.

First, do not open it

Opening a file means loading it. A text editor builds a line index, a spreadsheet allocates a cell for every field, pandas builds a column for every column. For a file the size of your memory, each of those fails differently: slowly, with a limit message, or with a killed process. The alternative is streaming: tools that read the file once, in order, keeping a little state, and print an answer. Almost everything you want to know about a big CSV can be had that way.

The steps below go from cheap to expensive. Stop at the first one that answers your question.

Step 1: look at the start

head -n 5 orders.csv
file orders.csv        # encoding, line endings
ls -l orders.csv       # the size

Five lines show you the header, the delimiter, the quoting, the kind of values and the line endings. head reads only the start, so it takes no time on any size of file. If the first lines look wrong (a title, a blank line, a BOM, ; as the delimiter), that is worth knowing before any tool is chosen; see the delimiter and encoding articles. The terminal preview article covers column, csvlook and friends.

Step 2: count what is in it

How many rows? The cheap answer is wc -l, and on this file it was wrong:

The 9,856-line difference is the number of records whose note contains a line break: a quoted field can span lines, and wc counts newlines. The counting article has the full table. The practical rule: use a parser to count records, and use wc -l only to detect that something is odd (if lines minus one differs from the record count, there are multi-line fields, and every line-based tool below needs care). An estimate from file size over average row size is nearly free, but sample from several places in the file: the first 100,000 rows overestimated the count by 4%, 20 random places by 0.26%.

Also check the last byte (tail -c 1): a file that does not end with a newline was cut off or is still being written (truncation checks, following a growing file).

Step 3: ask the question where the data is

The strongest tool for "a large CSV and a question" is a SQL engine that reads the file directly. DuckDB:

SELECT status, count(*) AS orders, round(sum(amount), 2) AS revenue
FROM read_csv('orders.csv')
GROUP BY 1 ORDER BY 1;

That grouped the 20,000,000 rows in 1.23 s with a 718,120 KB peak, without creating a table or a copy. The reader is multi-threaded and converts only the columns the query uses. Any question that is a filter, an aggregate, a join with a small table or a sort of a small result fits. The DuckDB articles cover the options, including types and bad rows.

The same job in pandas, loading everything:

df = pd.read_csv("orders.csv")
df.groupby("status")["amount"].agg(["count", "sum"])

took 5.55 s and a 3,924,400 KB peak (3.39 s and 3.9 GB on a second run) just for the load, about three times the file. On a machine with 16 GB that is fine; on one with 4 GB the file would not load. It also read the five-digit zip column as int64, so 09587 became 9587. The memory article explains the sizes and how to estimate them before loading.

Step 4: read less, or read in chunks

If you want pandas, shrink what it holds. Read only the columns you need and give them compact types:

df = pd.read_csv("orders.csv", usecols=["status", "amount"],
                 dtype={"status": "category", "amount": "float64"})

Two columns held 0.18 GB with a 459,744 KB peak. If even that does not fit, aggregate in chunks:

total = None
for chunk in pd.read_csv("orders.csv", usecols=["status", "amount"], chunksize=1_000_000):
    part = chunk.groupby("status")["amount"].agg(["count", "sum"])
    total = part if total is None else total.add(part, fill_value=0)

That took 6.21 s with a 150,740 KB peak and returned the same counts as DuckDB. It works for sums, counts, minima and maxima; a mean needs the sum and the count, and a median does not chunk. Three traps from the chunking article: types are inferred per chunk unless you pass dtype, identifiers lose leading zeros, and the pyarrow engine failed on this file because of the multi-line fields.

Step 5: take a sample

For exploration you want a few thousand rows, not a table. Five ways, from the sampling article:

• shuf -n 1000 in 4.39 s using 2.7 MB, with --random-source to make it repeatable;

• an awk reservoir in 5.05 s;

• a seek to random byte offsets in 0.08 s, which favors rows after long lines;

• DuckDB's USING SAMPLE reservoir(1000 ROWS) REPEATABLE (42) in 4.69 s;

• a record-aware Python reservoir in 26.83 s.

Only the DuckDB and Python versions return whole records. In a 100,000-line sample from shuf and from the seek script, 45 to 67 lines were halves of a row with a line break, and neither sample parsed as CSV. A sample is a view of the file, and the sampling method is part of the result.

Step 6: split it, if you must

Sometimes the consumer cannot take the size: a spreadsheet, an upload form, a loader. Then split, with a tool that matches the need (splitting article):

A cut that lands inside a quoted field breaks two parts, and a lenient reader like DuckDB reads the broken half as a row without complaint. Verify any split with a strict parser and a total record count. For Excel the target is under 1,048,576 rows including the header, though the better move is usually an extract or a summary (the Excel limits article).

Step 7: compress it

A CSV compresses to 25 to 35% of its size. On a 130 MB export, gzip -6 gave 42.2 MB in 4.6 s, zstd -3 43.7 MB in about the time of gzip -1, and zstd -19 35.4 MB in 58.8 s. You rarely need to unzip: zcat file.gz | head, pd.read_csv("file.csv.gz") and DuckDB's read_csv read compressed files directly. The cost is that a compressed stream has no random access, so compress in parts if readers want slices. Details and the truncated-archive trap are in the compression article and the gzip export article.

Step 8: convert it, if it will be read again

If the same file will be queried many times, pay the parse cost once. Convert to Parquet:

COPY (SELECT * FROM read_csv('orders.csv')) TO 'orders.parquet' (FORMAT parquet, COMPRESSION zstd);

The 130 MB, 2,000,000-row sample file became 34.2 MB in under a second, and a one-column maximum took 0.036 s instead of 0.203 s (why Parquet wins, the conversion). Decide the types when you convert: DuckDB read the amount as a DOUBLE, and a DECIMAL(10,2) keeps 103.10. Alternatively load into a database; PostgreSQL's COPY loaded 2,000,000 rows in 3.3 s (loading). A benchmark with the caveats spelled out compares the options.

The tools, one by one

**head, tail, wc, grep, cut, awk.** The Unix tools read a line at a time, use almost no memory and start instantly. They are right for looking, for counting lines, for filtering on a simple pattern and for cutting columns, as long as no field contains a comma or a line break that matters. Their weakness is that they do not understand CSV: awk -F, splits "gift, wrap" into two fields, and wc -l counts a two-line note twice. Use them to explore and to check; do not build a pipeline on them until you have confirmed the file is simple.

csvkit, qsv and Miller. These are CSV-aware command-line tools. They parse quotes properly, can select, filter, sort, join and summarize, and in the tests they were correct on the multi-line fields. qsv counted the 20,000,000 records in 2.20 s and split the file in 6.79 s; Miller was the slowest splitter at 35.48 s; csvkit's csvjson took 36 s with type inference on. Two cautions: they infer types unless told not to (Miller wrote +14192381408 as a number), and they re-serialize, so the output is the same records but not the same bytes.

DuckDB. A SQL engine in a single binary or a Python package. It reads CSV, compressed CSV and Parquet in place, runs on all cores, spills to disk and writes CSV, Parquet and JSON. It was the fastest tool for counting records (0.99 s) and for the grouped query (1.23 s), and it is the one I reach for first. Its leniency is the thing to watch: with default settings it read half of a split record as a row without a warning. Declare columns and use strict options when the file matters.

pandas. The right tool when the next step is Python: reshaping, modeling, plotting. It is not a streaming tool, but with usecols, dtype and chunksize it stays small. The default load made a zip an int64, and the pyarrow engine failed on a file with line breaks in fields, so read the defaults before trusting them.

PostgreSQL and MySQL. Right when the data will be used by an application, needs indexes or constraints, or has to be shared. COPY loaded 2,000,000 rows in 3.3 s. Loading is a project, not an afterthought, and the loading guide lists the failure messages you will meet. For analysis of a file you will look at once, a database is more ceremony than a query engine.

Excel and Sheets. For summaries and small extracts. They are limited to 1,048,576 rows in Excel's case, and they convert values when they open a file (zip codes, long numbers, anything that looks like a date), so import with text columns and never use them to open a file you are going to write back.

Parquet and the tools around it. Not a tool but a format, and the most effective single change for a file that is queried repeatedly. Convert once, with types decided, and every later query is cheaper. The cost is that the file is no longer something you can read in an editor.

By the size of the file

A rough map, from experience with the tests here and not a law.

Under about 100 MB, most things work, and the choice is about convenience. Open it in pandas or a spreadsheet if it has fewer than a million rows, and check for line breaks and encodings first. From 100 MB to a few gigabytes, loading everything into pandas starts to depend on memory: estimate it (about three times the file for the peak), type the columns, or switch to DuckDB. From a few gigabytes to a few hundred, stop thinking about loading: query in place, sample for exploration, compress for storage and convert to Parquet for repeated work. Beyond that, the file is better in a database or a columnar store, split by a meaningful key such as day, so that a query reads only the part it needs (partitioning with PARTITION_BY wrote seven country directories from the test file in 12.17 s).

None of these boundaries is sharp. A file of two long text columns is heavier than the same size of integers, and a wide file with fifty columns costs more per row than a narrow one. Let a small experiment on a slice of your data choose, as the benchmarking article describes.

Which tool for which question

A worked example: from "too big" to an answer

Say a colleague sends orders-2025.csv, 1.3 GB, and asks how much revenue came from refunded orders in Brazil, by month. A sensible session runs like this, and the whole thing takes minutes.

First, ls -l and head -n 5: the size is 1.3 GB, the header has eight columns, created_at is an ISO timestamp, amount has two decimals, the delimiter is a comma. Second, a record count with DuckDB, SELECT count(*) FROM read_csv('orders-2025.csv'), about a second, and 20000000. That is far over a spreadsheet's limit and fine for a query engine. Third, a check that nothing is odd: the last byte is a newline (tail -c 1), wc -l is 9,856 higher than the count (so there are multi-line fields; note it), and DESCRIBE shows the types DuckDB inferred. Fourth, the question itself:

SELECT date_trunc('month', created_at) AS month, round(sum(amount), 2) AS revenue
FROM read_csv('orders-2025.csv')
WHERE status = 'refunded' AND country = 'BR'
GROUP BY 1 ORDER BY 1;

Twelve rows come back in about a second, and they go into a message or a spreadsheet. If the colleague then asks "and which customers?", the same file answers the next query in another second, because the engine streams. If the questions keep coming, convert once to Parquet and ask the next fifty against that. At no point did anything need the whole file in memory, and at no point did anyone open it.

The mistakes to avoid in this session are small ones. Do not trust wc -l for the count. Do not let a tool guess that the zip is a number if you are going to join on it. Do not paste the twelve numbers without saying which file and query produced them; keep the query next to the result. And if the answer will be used for something that matters, run it a second way, with a different tool, and compare.

Budgeting memory and time

Before you start, decide how much you can afford. A default pandas load needs about three times the file size at peak and holds one and a half times; typed and trimmed it can hold a third of the file. DuckDB over a CSV used 718,120 KB for a grouped query on the 1.3 GB file and uses more for large joins and sorts, spilling to disk when it must. A chunked loop needs about the chunk size times the width of the columns you keep, and 150 MB was enough here. If the budget is tight, rank the tools by their peaks in this guide rather than by their speeds.

Time works the same way. Counting is a pass over the file, about a second per gigabyte for a good parser on a warm cache and several seconds from a cold disk. A query is one pass or a few; a conversion to Parquet is one pass plus a write. If you expect to read the file more than a handful of times, a pass per question adds up, and the conversion repays its cost. If you expect to read it once, do not convert. The loading benchmark lists what to hold constant when you compare.

Scenarios and what I would do

A vendor sends a 3 GB file every Monday. Do not open it by hand. Write a short script that checks the size and last byte, counts records with DuckDB, validates the header and a few columns (validation guide), and writes the files you actually need in Parquet. Keep the original compressed. Compare the counts week over week; a file that is half the usual size is a failed export.

A one-off 50 GB dump on a laptop with 8 GB. Stay with streaming tools. DuckDB spills to disk for large aggregations; if it complains, reduce the question or process in key ranges. Sample first to find the columns that matter, then query only those. Do not decompress to disk if you can read the compressed file in place.

A file that must go into Excel. It is the summary or the extract that goes in, not the file. Decide with the recipient what they are going to do with it, produce that, and name it for what it holds (the Excel article).

A file that is still being written. Follow it with tail -F and act only on complete lines (following a CSV); do not count or load it until the writer is done, or take a snapshot copy first.

A file that will not parse. Check the structure before blaming the tool: delimiter, quoting, ragged rows and encoding. The counting rows and columns article and the cleaning hub cover what to do about it.

Habits that scale

Keep the source immutable. Every derived file (sample, part, Parquet, extract) should be rebuildable from the original with a command you saved. Store a checksum of the original and note it in the derived file's name or manifest.

Name derived files for what they are. orders-br-march.csv, orders-sample-1000-seed42.csv, orders.parquet made from orders-2025.csv on a given date. A sample that looks like the real thing is a hazard.

Prefer parsers to line tools when the data is unknown. Line tools are fast and correct only when no field contains a line break. Check once, and write the answer down.

Say what the types are. Identifiers are text, money is a decimal, dates are dates. Inference is for exploring and not for pipelines.

Verify in a second way. A count from wc and a count from a parser, a sum from DuckDB and a sum from pandas, a record found by grep and in the output. Agreement between two methods is cheap evidence, and disagreement is worth an hour.

Measure, then move on. Most of the time, the first streaming query answers the question. The elaborate machinery (chunks, partitions, conversions) is for the cases in which it does not, and the numbers above tell you roughly when that is.

What goes wrong in every approach

Four hazards recur, and each of the spokes found at least one.

Line breaks inside fields. They make wc -l wrong, split -l, shuf and a seek sample unsafe, and a naive follower fragile. Find out early whether the file has any; if it does, use parser-aware tools, or have the producer remove them.

Type inference. Defaults turn identifiers into numbers, dates into strings and decimals into floats. Say what each column is.

Silent leniency. DuckDB read half a record as a row, pandas padded a short row, XlsxWriter dropped rows past the limit with a return code. Verify with a strict parser or a second tool.

The cache. All the timings here are warm. On a cold disk or a network share, the I/O dominates and the ranking of tools compresses.

A short glossary of the failure modes

It helps to name the problems, because the same few appear in every article of this cluster.

Peak versus resident memory. A load needs more memory while it runs than the result occupies afterwards. Plan for the peak, which is what the operating system or the scheduler enforces.

Line versus record. A line ends at a newline byte. A record ends at a newline that is not inside quotes. The two differ by the number of multi-line fields, and a tool that counts one when you wanted the other gives a plausible wrong answer.

Streaming versus materializing. A streaming tool keeps a bounded amount of state and passes over the data once. A materializing tool builds the whole table. The first scales with time, the second with memory.

Cache. A repeated run on the same file is quicker because the operating system keeps the bytes in memory. A benchmark that does not say which it measured is half a benchmark.

Leniency. Many readers accept input that a strict parser rejects, by padding, skipping, truncating or guessing. Leniency is helpful for exploration and harmful for validation, and a reader that never complains is not evidence of a clean file.

Derived artifact. A sample, a part, a Parquet file or an extract made from the source. It carries the source's checksum or name in your notes, and it can be thrown away and rebuilt.

With those six words, most conversations about "the file is too big" become specific enough to solve: is it the peak, the lines, the streaming, the cache, the leniency or the artifact?

How to tell it worked

Whatever you did, check the result against the source in a second way: a count from a different tool, a total you know, a record you can find by eye. For derived files (parts, samples, Parquet), check the row count and a checksum of the content. Keep the commands. The validation guide has the checks to run on the file before you trust it at all.

When the file is not the problem

Sometimes the size is a symptom. A file that is 20 GB because every row repeats a long text column could be a table plus a lookup. A daily full export that nobody diffs could be an incremental one. A request to "open the whole file" could be a request for three numbers. Ask what the reader will do with it before you pick a tool. And if the data lives in a database, query the database: it can do what these tools do without the export, and the export is where line breaks, types and encodings get damaged in the first place (exporting from databases).

Further reading: DuckDB CSV import; pandas scaling to large datasets; Excel specifications and limits.