Split a Large CSV with PowerShell, Bash or Python

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

In Windows PowerShell, which has no split command, use the PowerShell script below. Like split, it counts lines, not rows. The Python script counts rows with a real CSV parser and never cuts a row. If you would rather not run a script, split the CSV into multiple files online: RowSlice repeats the header in every part, never cuts a row in half and uploads nothing.

Your export has 2.5 million rows and Excel stops at 1,048,576. The usual advice is to split the file into parts on the command line. That works, with two catches the one-liners skip: the header row only ends up in the first part, and most commands count lines, which is not the same as counting rows.

Every script below was run on a 99.4 MB test file with 2,500,000 rows and six columns, including an id and a zip column with leading zeros. Like many real exports, one row in every 97 had a line break inside a quoted notes field. We then parsed every part to check the rows, the headers and the column counts.

Lines Are Not Rows

A CSV row normally sits on one line, but a quoted value is allowed to contain a line break, and that row then spans two lines. Tools such as split, and PowerShell’s ReadLine(), cut wherever the line count says, including in the middle of such a row. Here is a small file split every two lines:

id,note
1,ok
2,"first line
second line"
3,ok

== part_00.csv
1,ok
2,"first line
== part_01.csv
second line"
3,ok

Row 2 is now half in one file and half in the other. Both halves are broken: a CSV reader treats the first part’s opening quote as running to the end of the file, and it reads second line" in the second part as a one-column row.

On the 2.5-million-row file, splitting every 1,000,000 lines gave parts with 989,795, 989,796 and 520,409 rows, because about one line in a hundred belonged to a row that had already started. None of the cuts happened to land inside a value, so every part parsed correctly. That was luck: with different line breaks in the data, the same command would have cut a row. If your file has free-text fields, use the Python script further down, which counts rows instead of lines.

macOS and Linux: split

This version uses only options that the macOS and Linux versions of split share:

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

tail -n +2 sends everything after the header to split, which writes 1,000,000 lines at a time into part_aa, part_ab and so on. The loop then puts the header back on top of each part and adds .csv to the name. Run it in a folder with nothing else named part_, or the loop will process those files too. It took about a second on the test file.

On Linux, GNU split can also split by size without cutting a line in half, which suits an upload limit:

tail -n +2 big.csv | split -C 40m -d --additional-suffix=.csv - part_
for f in part_*.csv; do
  { head -n 1 big.csv; cat "$f"; } > "with-header-$f" && rm "$f"
done

-C 40m puts as many whole lines as fit into 40 MB, -d numbers the parts from 00, and --additional-suffix adds the extension. The test file became parts of 40.0, 40.0 and 19.4 MB. -C and --additional-suffix do not exist in the macOS version. Both of these commands count lines, so the warning above applies to them.

Windows: PowerShell

Windows has no split command. The obvious PowerShell route, Get-Content, sends every line through the pipeline as a separate object, which is slow on large files — more than twice as slow as .NET when we counted lines. Reading the file with .NET instead still reads one line at a time:

$source = 'C:\data\big.csv'
$folder = 'C:\data'
$rowsPerFile = 1000000

$reader = [System.IO.StreamReader]::new($source)
$header = $reader.ReadLine()
$utf8WithBom = [System.Text.UTF8Encoding]::new($true)
$part = 0; $count = 0; $writer = $null
while ($null -ne ($line = $reader.ReadLine())) {
  if ($count % $rowsPerFile -eq 0) {
    if ($writer) { $writer.Close() }
    $part++
    $writer = [System.IO.StreamWriter]::new("$folder\part-$part.csv", $false, $utf8WithBom)
    $writer.WriteLine($header)
  }
  $writer.WriteLine($line)
  $count++
}
if ($writer) { $writer.Close() }
$reader.Close()

Change the first three lines, and use full paths: .NET resolves a relative path against the folder PowerShell started in, which is not always the one you are looking at. Each part gets the header and a UTF-8 byte order mark, so accented letters display correctly when the part is double-clicked open in Excel. On the test file it wrote three parts in anywhere from 7 seconds to 2 minutes, depending on what else the laptop was doing. It reads lines, so the warning above applies here too.

Python: Split by Rows, Not Lines

When the file has free-text fields, count rows with a real CSV parser. This script uses only Python’s standard library:

import csv

ROWS_PER_FILE = 1_000_000

with open("big.csv", encoding="utf-8-sig", newline="") as source:
    reader = csv.reader(source)
    header = next(reader)
    out = None
    for i, row in enumerate(reader):
        if i % ROWS_PER_FILE == 0:
            if out:
                out.close()
            part = i // ROWS_PER_FILE + 1
            out = open(f"part-{part}.csv", "w", encoding="utf-8-sig", newline="")
            writer = csv.writer(out)
            writer.writerow(header)
        writer.writerow(row)
    if out:
        out.close()

It gave exactly 1,000,000, 1,000,000 and 500,000 rows, every one with six columns and every line break inside a value intact, in 6 to 42 seconds over our runs. newline="" is what makes the reader treat a line break inside quotes as part of the value. utf-8-sig strips a byte order mark from the source if it has one and writes one into each part for Excel. The values themselves do not change, including leading zeros, though quotes are added only where a value needs them, so a value that was quoted without needing it comes out unquoted.

Which Method to Use

Times are the fastest and slowest we saw on a Windows 11 laptop that was busy with other work; yours will differ. Choose by the middle column first: if nobody can promise the file has no line breaks inside values, the speed of the line-based commands is not worth the risk.

Without the Command Line

If you would rather split a large CSV file online, RowSlice’s splitter parses rows the way the Python script does, so a line break inside a value never ends a part, and it puts the header in every part. It can split by rows, by maximum file size or into a set number of files. It runs in your browser and reads the file from your disk, so nothing is uploaded. To choose the numbers, see Excel’s row limit and splitting a CSV to fit an import limit. To put parts back together, see merging CSV files from the command line.