PoppyOS

Home › Guides

How to split a CSV that is too big for Excel (the 1,048,576-row limit)

Excel stops loading at row 1,048,576 and tells you the data was not fully loaded; the rest is simply not shown, and if you save, the rest is gone from that copy. That number is the worksheet size limit stated in Microsoft's Excel specifications. If your file has more rows than that, you either work with it in something that is not a spreadsheet, or you split it into pieces that each fit.

Why you cannot just split by lines

The usual tip is the split -l command, or cutting the file by line count. A CSV row can contain a line break inside a quoted cell (an address, a comment), so one row can span several lines. A cut in the wrong place produces two broken files. Also, only the first piece would have the header. A correct split counts records with a CSV parser and repeats the header in every piece.

Python script

First, make a small test file of 25 rows, one of which has a line break inside a cell, so you can see the behaviour:

import csv

with open("big.csv", "w", newline="", encoding="utf-8") as f:
    w = csv.writer(f)
    w.writerow(["id", "note"])
    for i in range(1, 26):
        w.writerow([i, "line one\nline two" if i == 12 else f"row {i}"])
print("wrote big.csv")
wrote big.csv

Now split it. For this demonstration each piece gets 10 data rows. For real files set PER_FILE to 1,048,575 (one row less than the limit, because the header takes a row) or, to leave Excel some room to work, something like 500,000.

import csv

PER_FILE = 10        # use 1_048_575 for Excel's limit
SOURCE = "big.csv"

def write_part(number, header, rows):
    name = f"big-part{number}.csv"
    with open(name, "w", newline="", encoding="utf-8-sig") as f:
        w = csv.writer(f)
        w.writerow(header)
        w.writerows(rows)
    print(name, len(rows), "rows")

with open(SOURCE, newline="", encoding="utf-8-sig") as f:
    reader = csv.reader(f)
    header = next(reader)
    part, rows = 1, []
    for row in reader:
        rows.append(row)
        if len(rows) == PER_FILE:
            write_part(part, header, rows)
            part, rows = part + 1, []
    if rows:
        write_part(part, header, rows)

with open("big-part2.csv", newline="", encoding="utf-8-sig") as f:
    print(repr(list(csv.reader(f))[2][1]))
big-part1.csv 10 rows
big-part2.csv 10 rows
big-part3.csv 5 rows
'line one\nline two'

The last line shows that the row with the line break (id 12) arrived in piece 2 intact, as one cell, instead of being cut in half. The script holds one piece in memory at a time, so memory use is bounded by PER_FILE.

Other options

After splitting, keep the original file untouched, and remember that the formatting trouble in leading zeros and garbled characters can still bite when you open each piece.

Last updated 2026-10-27. Code on this page was run on that date and the output shown is the real output. Spreadsheet menu steps are written from Microsoft's and Google's documented behaviour; where we have not run something ourselves, the page says so.

More guides

All guides