Compressing CSV Files and Reading Them Without Unzipping

A CSV is text, and text compresses well. A 130 MB export becomes 35 to 45 MB with ordinary tools, which matters for disk, for transfers and for backups. The usual objection is that you then have to unzip it before you can look at it. You do not. Almost every tool that reads CSV also reads a compressed CSV, and the ones that do not can be fed through a pipe. This article shows the compression choices and, mainly, the ways of reading a compressed file in place.

Choosing a format

The practical candidates are gzip (.gz), zstd (.zst), bzip2 (.bz2), xz (.xz) and zip (.zip). For CSV the choice is mostly about who needs to read the result.

• gzip is everywhere. Every language reads it. gzip -6 took the 130 MB export to 42.2 MB.

• zstd is faster for the same ratio. zstd -3 made 43.7 MB in about the time of gzip -1, and level 19 made 35.4 MB but needed 58.8 s, which is only worth it for an archive.

• zip holds several files and has an index, which makes it the one format here with partial access to which file, though not to rows inside one. Windows and macOS open it without extra software.

• bzip2 and xz compress harder and run slower; reach for them for long-term archives.

If you do not know the reader, use gzip. If you control both ends and the data is large, zstd.

gzip -k orders.csv            # keeps the original, writes orders.csv.gz
zstd -3 orders.csv            # writes orders.csv.zst

Looking without unzipping

zcat orders.csv.gz | head -n 5        # first rows
zcat orders.csv.gz | wc -l            # line count (decompresses everything)
zgrep -c ',BR,' orders.csv.gz         # grep inside the compressed file
zstdcat orders.csv.zst | head         # the zstd equivalents
unzip -p orders.zip orders.csv | head # one file from a zip, to the pipe

head closes the pipe after five lines, zcat gets a broken pipe and stops, and so a peek at a 40 MB archive reads a few kilobytes. Counting or searching needs the whole stream, and runs at decompression speed. The line counting article explains why wc -l is a line count and not a record count.

Reading in place from code

pandas infers the compression from the extension:

import pandas as pd
df = pd.read_csv("orders.csv.gz")                  # also .bz2, .xz, .zst, .zip
chunks = pd.read_csv("orders.csv.gz", chunksize=1_000_000)

It read the 2,000,000-row file and reported 2,000,000 rows. Chunking works the same way as for an uncompressed file (chunks in pandas). DuckDB does the same and also reads several compressed files with a glob:

SELECT count(*) FROM read_csv('orders.csv.gz');
SELECT status, count(*) FROM read_csv('orders-*.csv.gz') GROUP BY 1;

The first query counted 2,000,000 rows. Python's standard library works too (gzip module documentation), using the text mode of gzip.open, with newline="" so quoted line breaks survive:

import csv, gzip
with gzip.open("orders.csv.gz", "rt", newline="", encoding="utf-8") as f:
    for row in csv.reader(f):
        ...

What you give up

A gzip or zstd file is a single stream. To read row 1,000,000 you decompress the first 999,999 rows. That means no seeking, no splitting the read across cores, and a tool like random sampling by byte offset does not work. The workable answer is to compress parts: one .csv.gz per day or per million rows. Each part is independent, a reader can take the parts it needs, and a glob in DuckDB or a loop in Python processes them in order. Splitting is covered in splitting a large CSV.

A second cost is that CPU time moves from the disk to the decompressor. On a fast local SSD an uncompressed file can be quicker to scan; over a network or from slow storage, the compressed one wins. Measure on your own storage.

Verifying a compressed file

gzip -t file.csv.gz tests the whole stream and exits non-zero if it is damaged or cut off. The trap: a pipeline reports the status of its last command.

zcat broken.csv.gz | wc -l; echo "status ${PIPESTATUS[0]} ${PIPESTATUS[1]}"

For a truncated file zcat fails with unexpected end of file and exits 1, wc prints the number of lines it did get and exits 0, so the pipeline looks fine. Use set -o pipefail in scripts and run gzip -t first. The truncation article measured this on a file cut at half its length: 96,942 lines came out of a stream that had about twice as many.

Choosing a level, and when not to compress

Compression levels trade time for size, and the curve flattens quickly. On the 2,000,000-row export, gzip -1 made 50.6 MB in 1.9 s, gzip -6 42.2 MB in 4.6 s and gzip -9 41.9 MB in 7.6 s: the last step costs 65% more time for under 1% less size. The default is almost always right. If the machine has spare cores, pigz (parallel gzip) matched the size of gzip -6 in 1.4 s, and zstd -3 was as fast with a slightly larger file. What you should not do is compress data that is already compressed or random, such as hashed identifiers or encoded blobs, where gzip will spend time and save little.

There are also reasons to leave a file alone. A CSV that a person opens daily in a spreadsheet is more convenient plain. A file that another system scans with a tool that cannot read .gz becomes a support request. And a tiny file saves no meaningful space. A good compromise for archives is to keep the current month plain and compress everything older, with a naming convention (orders-2026-09.csv.gz) that makes the compression obvious.

Finally, name the file honestly. orders.csv.gz tells every tool and every person what is inside and how it is wrapped. A gzip file called orders.csv is a trap that will be discovered at the worst moment.

How to tell it worked

Compare the decompressed content with the original: zcat orders.csv.gz | cmp - orders.csv is silent when they match. Compare record counts with a parser. Keep a checksum of the compressed file in the manifest, and a row count of the content. And test the reader you will actually use, because a library that cannot read .zst will say so only when it opens one.

When you need random access

If readers need to jump to row N, filter by a column or touch only some columns, a CSV in any compression is the wrong container, and Parquet does those things natively. If the file is small, compression adds a step for no gain. If it is going to a person with a spreadsheet, send the plain CSV or a zip, which they can open. The large-file hub compares the options.