How to Reduce the Size of a CSV File

An upload form rejects anything over 100 MB, or an email bounces because the attachment is too big, and the CSV you need to send is larger than that. There are two ways out: make the file smaller, or split it into parts. This guide is about the first, and says when to give up and use the second.

Instead of listing tips, we tried each one on the same 99.4 MB export — 2,500,000 rows of IDs, store names, ZIP codes, customer names, amounts and notes — and measured the result.

What Each Method Did to a 99.4 MB CSV

Saving as an Excel workbook is missing from the table because Excel cannot hold all 2.5 million rows. On a 1,000,000-row part of the same file, it turned 38.5 MB of CSV into a 35.9 MB .xlsx, a saving of 7%; zipping that part gave 11.0 MB.

1. Compress It

Compression is the biggest win by far, and it loses nothing. CSV text is highly repetitive — the same store names, the same digits, a comma after every value — and a compressor stores repeated patterns once. The file shrank to about a quarter of its size.

On Windows, right-click the file and choose Send to › Compressed (zipped) folder, or in PowerShell run Compress-Archive -Path big.csv -DestinationPath big.zip, which is what produced the 25.4 MB figure. On a Mac, right-click and choose Compress. Maximum-level gzip came out at almost exactly the same size, so there is little to gain from hunting for a better compressor for a file like this.

The catch is that the recipient has to accept a compressed file. Email does, and so does cloud storage. Some data tools read compressed CSVs directly: pandas handles .gz and single-file .zip, DuckDB recognises .csv.gz, and BigQuery loads gzip-compressed CSV up to 4 GB. Many upload forms want the plain .csv, and for those compression does not help.

Note how close the result came to one common limit: a personal Gmail account allows 25 MB of attachments, and the zip was 25.4 MB. When the compressed file is still slightly too big, splitting is the next step, not a better compressor.

2. Remove Columns Nobody Needs

This is the best option when the result must stay a plain CSV. Exports often include every field the system has, and the destination needs a handful. The columns were not equal in size, either:

The rest of the file is commas, quotes and line breaks. Keeping id, store and amount halved it to 51.9 MB. In real exports, the columns worth checking first are free text, URLs, and anything that looks like JSON.

This Python script keeps the columns you list, by name, and parses the file properly:

import csv

KEEP = ["id", "store", "amount"]

with open("big.csv", encoding="utf-8-sig", newline="") as source, \
     open("smaller.csv", "w", encoding="utf-8", newline="") as out:
    reader = csv.DictReader(source)
    writer = csv.DictWriter(out, fieldnames=KEEP, extrasaction="ignore")
    writer.writeheader()
    writer.writerows(reader)

If you have DuckDB, one line does the same:

COPY (SELECT id, store, amount FROM read_csv('big.csv', all_varchar = true)) TO 'smaller.csv';

Both produced the same 2,500,000 rows. DuckDB’s file was 49.5 MB rather than 51.9 MB because it wrote Unix line endings. all_varchar = true makes DuckDB treat every value as text, so it writes values exactly as they were instead of re-formatting numbers.

3. Remove Rows Nobody Needs

Often the recipient needs only part of the data: one region, one year, one client. Each file you make that way is smaller than the whole. The split by column tool writes one file per value of a column, so you can send each team only its own rows. If the export contains repeated rows, the duplicate remover takes them out and tells you how many there were.

4. The Small Savings, and Two Habits That Cost

Line endings. Windows ends each line with two characters, Unix with one. Switching saved 2.4 MB on 2.5 million lines. It is rarely enough on its own, but it costs nothing: the encoding fixer can set line endings to Mac and Linux (LF).

Quotes around every value. Quotes are only needed around values that contain a comma, a quote or a line break. Writing them everywhere, as Windows PowerShell’s Export-Csv does, took the file to 127.0 MB. Rewriting it with quotes only where needed brought it back to 99.4 MB. In PowerShell 7, add -UseQuotes AsNeeded.

UTF-16. Microsoft’s own summary of the difference: for ordinary English letters and digits, “UTF-8 uses 1 byte per character, UTF-16 uses 2 bytes per character.” Our file doubled to 197.9 MB. Excel’s Unicode Text format produces UTF-16, and so do some other programs’ exports. If a CSV looks suspiciously large, convert it to UTF-8 with the encoding fixer, which recognises a UTF-16 file by the byte order mark at its start.

What Did Not Help Much

Saving as .xlsx. An Excel workbook is a compressed package, and Microsoft says the format can be “up to 75 percent smaller in some cases”. In ours it was 7% smaller. It also means opening the CSV in Excel first, which converts values as it loads: ZIP codes lose their leading zeros, and the file is limited to 1,048,576 rows.

Parquet. A columnar format used by data tools, and 34.1 MB with DuckDB’s default settings: much smaller than the CSV, but larger than the plain zip. It is a good choice when the receiver is a data pipeline that reads Parquet. It is no use to someone who wants to open the file in a spreadsheet.

When It Has to Fit a Limit: Split It

If the file must stay a CSV and stay whole, removing columns is the only large saving, and sometimes every column is needed. Then split the file into parts that each fit. The CSV splitter has a maximum-size mode, puts the header in every part and never cuts a row in half; set it a little under the limit, such as 95 MB for a 100 MB form. Splitting a CSV to fit an import limit lists current limits for common tools, and is there a CSV file size limit? covers where the limits come from.