Blog

How to split a large CSV file (and when not to bother)

19 September 2026

Splitting is the usual advice when a CSV will not open: cut it into pieces small enough for Excel, work on each piece, add up the results. It works. It also quietly creates the problem it was meant to solve.

Here is how to do it properly on each platform, and how to tell whether you should.

macOS and Linux

The split command is already installed.

split -l 1000000 bigfile.csv part_

That produces part_aa, part_ab and so on, a million rows each. Only the first part has the header row. To put the header on every part:

head -1 bigfile.csv > header.csv
tail -n +2 bigfile.csv | split -l 1000000 - part_
for f in part_*; do cat header.csv "$f" > "$f.csv" && rm "$f"; done

Splitting by size instead of rows, 200 MB per part:

split -b 200m bigfile.csv part_

Avoid that one for CSV. It cuts mid-row and mid-quoted-field, so you get broken records at every boundary.

Windows PowerShell

No built-in split, but this does it, header included on every part:

$src = "bigfile.csv"
$rows = 1000000
$header = Get-Content $src -First 1
$i = 0; $part = 1
$out = New-Object System.IO.StreamWriter("part_1.csv")
$out.WriteLine($header)
Get-Content $src -ReadCount 10000 | ForEach-Object {
  foreach ($line in $_) {
    if ($i -gt 0) {
      if ($i % $rows -eq 0) {
        $out.Close(); $part++
        $out = New-Object System.IO.StreamWriter("part_$part.csv")
        $out.WriteLine($header)
      }
      $out.WriteLine($line)
    }
    $i++
  }
}
$out.Close()

Get-Content -ReadCount matters. Without it PowerShell reads line by line and a multi-gigabyte file takes hours.

The three ways splitting goes wrong

Quoted fields containing newlines. A CSV field wrapped in quotes may legally contain a line break. Address fields and free-text comments do this constantly. Every line-based splitter above, including split, will cut such a record in half and produce two corrupt rows. If your file has free-text columns, check the row counts add up:

wc -l bigfile.csv
wc -l part_*.csv

The parts should exceed the original by exactly the number of added headers.

Every question becomes N questions. This is the real cost. A total across nine parts is nine totals added by hand. A pivot is nine pivots reconciled manually. A deduplication across the whole dataset is not possible at all, because duplicates on either side of a split boundary are invisible to both parts. So is any sort, since sorting each part does not sort the whole.

The parts drift. Someone fixes a value in part_04.csv. Three weeks later nobody knows which parts were edited and the original is stale.

When splitting is right

• You need to hand one slice to someone else, one region or one month.
• An upstream system takes a maximum file size.
• You genuinely only need the first N rows, to inspect the structure.

When it is not

If you are splitting so that you can total, sort, filter, pivot or deduplicate the whole dataset, you are about to do the work N times and reconcile it by hand. The file does not need splitting. It needs opening.

Two options that do not involve cutting anything up:

DuckDB, if you write SQL. It reads the CSV in place, no import:

duckdb -c "SELECT region, sum(amount) FROM 'bigfile.csv' GROUP BY region"

A spreadsheet with no row limit, if you do not. Some desktop tools index the file and read only the rows on screen, so the whole thing stays one file and one total.

What we built

BigXL opens the whole CSV, at any size, without splitting or importing it. Sorting, filtering, deduplication and pivots run over every row, so a total is a total, not nine totals.

It runs entirely on your own machine, with no account and no network calls.

Opening, sorting, filtering, charting and CSV export are free at any file size.