Split a Large Excel File into Files of N Rows

An import tool that accepts at most 10,000 rows per file. A web form with a 5 MB upload limit. A colleague whose laptop freezes opening a 300,000-row workbook. The fix is the same each time: cut the sheet into parts of a fixed number of rows, each with the header row, named so they sort in order. With pandas this is a few lines if the file fits in memory; with openpyxl's streaming modes it works on files of any size with flat memory use. Either way, the job ends with a check that the parts add up to the original. This page is part of Working with Large Excel Files in Python.

Prerequisites

pip install pandas openpyxl

transactions.xlsx has one sheet with 120,000 transactions: an ID, a date, an account code, an amount and a currency. The target is parts of at most 50,000 rows each — two full parts and one of 20,000.

Step 1 — The pandas version

If the file loads comfortably, read it once and write consecutive slices with iloc:

import math
from pathlib import Path
import pandas as pd

ROWS = 50_000
df = pd.read_excel("transactions.xlsx", sheet_name="Transactions", dtype={"Account": str})
parts = math.ceil(len(df) / ROWS)
out = Path("parts_pandas")
out.mkdir(exist_ok=True)
for i in range(parts):
    chunk = df.iloc[i * ROWS:(i + 1) * ROWS]
    chunk.to_excel(out / f"transactions_part_{i + 1:02d}.xlsx", index=False)
    print(f"part {i + 1}: rows {i * ROWS + 1}–{i * ROWS + len(chunk)}")

math.ceil rounds the number of parts up so the last, shorter part is included. The zero-padded number — 01, 02 — keeps the files in order in a file listing even past nine parts; part_10 sorts after part_09, whereas part_10 sorts before part_2. to_excel writes the header into every part automatically, and index=False stops pandas adding a column of row numbers that the importer would not expect.

Reading the whole sheet first is the limitation: memory holds the full DataFrame, and for a workbook of a few hundred thousand rows that is fine; for millions it is not.

Step 2 — Streaming: read-only in, write-only out

openpyxl can read a sheet as a stream of rows (read_only=True) and write new workbooks as a stream (write_only=True). Combining them moves rows from the source to the parts without ever holding more than one row plus the open output file:

from openpyxl import Workbook, load_workbook

def split_stream(path, rows_per_part, out_dir, sheet=None, transform=None):
    out_dir = Path(out_dir)
    out_dir.mkdir(exist_ok=True)
    src = load_workbook(path, read_only=True)
    ws = src[sheet] if sheet else src.worksheets[0]
    rows = ws.iter_rows(values_only=True)
    header = next(rows)
    written, part, count, book, sheet_out = [], 0, rows_per_part, None, None
    for row in rows:
        if count == rows_per_part:
            if book:
                book.save(written[-1][0])
            part += 1
            book = Workbook(write_only=True)
            sheet_out = book.create_sheet(ws.title)
            sheet_out.append(header)
            written.append([out_dir / f"{Path(path).stem}_part_{part:02d}.xlsx", 0])
            count = 0
        sheet_out.append(transform(sheet_out, row) if transform else row)
        count += 1
        written[-1][1] += 1
    if book:
        book.save(written[-1][0])
    src.close()
    return written

import time
t = time.perf_counter()
result = split_stream("transactions.xlsx", ROWS, "parts_stream")
print([(p.name, n) for p, n in result], f"{time.perf_counter() - t:.1f}s")

A new output workbook starts whenever the current one reaches the limit: the previous part is saved, a fresh write-only workbook is created, and the header is written first. Starting the counter at the limit makes the very first row open part one without a special case. Write-only sheets flush rows to a temporary file as they are appended, so memory stays roughly constant however large the source is. Speed Up openpyxl with Read-Only Mode and Write Large DataFrames to Excel with Write-Only Mode describe each mode in detail.

Step 3 — Keep dates as dates

values_only=True returns Python datetime objects for date cells, and a write-only workbook writes them as dates — but with openpyxl's default number format, which shows the time as well. Give the date column a format by writing styled cells for it:

from openpyxl.cell import WriteOnlyCell

def first_date_cell(path):
    wb = load_workbook(path, read_only=True)
    cell = next(wb.worksheets[0].iter_rows(min_row=2, max_row=2))[1]
    info = (type(cell.value).__name__, cell.number_format)
    wb.close()
    return info

print("before:", first_date_cell(result[0][0]))

def styled(sheet, row, date_cols=(1,)):
    cells = []
    for i, v in enumerate(row):
        if i in date_cols and v is not None:
            c = WriteOnlyCell(sheet, value=v)
            c.number_format = "yyyy-mm-dd"
            cells.append(c)
        else:
            cells.append(v)
    return cells

result = split_stream("transactions.xlsx", ROWS, "parts_stream", transform=styled)
print("after: ", first_date_cell(result[0][0]))

A WriteOnlyCell carries a style in write-only mode, where ordinary cells cannot be styled after they are written. Most import tools read the underlying date value and do not care about the display format, but anyone opening a part in Excel will.

Step 4 — Check the parts against the source

Splitting is exactly the kind of job where an off-by-one error loses a row at every boundary without anyone noticing. Count and compare — rows, and a key that should appear exactly once:

source_ids = pd.read_excel("transactions.xlsx", usecols=["TransactionID"])["TransactionID"]
part_ids = pd.concat([pd.read_excel(p, usecols=["TransactionID"])["TransactionID"] for p, _ in result])
print("rows:", len(source_ids), "->", len(part_ids))
print("same IDs:", sorted(source_ids) == sorted(part_ids), "| duplicates:", part_ids.duplicated().sum())
print("max rows per part:", max(n for _, n in result))

Same count, same IDs, no duplicates, and no part over the limit: the split is correct. Reading only the ID column with usecols keeps the check cheap. For files without a unique ID, compare row counts and the sum of a numeric column instead — see Reconcile Excel Totals with Control Totals.

Splitting by size instead of rows

An upload limit is often in megabytes, not rows. The size of an .xlsx depends on how well its XML compresses, so it cannot be computed exactly in advance. The practical approach: split one part with a guessed row count, measure the file size, and scale — here 50,000 rows made a 1.6 MB file, so for a 5 MB limit about 140,000 rows per part leaves a safety margin. Re-check the largest part afterwards, since text-heavy rows compress less well than numeric ones.

Performance notes

On this 120,000-row file the streaming split took about five seconds, nearly all of it spent parsing the source XML and writing the output XML; the pandas version takes a similar time because it uses the same parser underneath. What streaming changes is memory, not speed: its memory use stays roughly constant however long the sheet is — one row in flight plus buffers — while the pandas version holds the whole sheet at once and grows with the file. If the parts go to a system that also accepts CSV, writing CSV parts is several times faster and produces smaller files — Convert Excel to CSV with Python covers that route, including the encoding to choose.

Splitting also parallelises well when there are many source files rather than one big one: run one split per file in a process pool. For a single huge sheet it does not, because the read-only reader has to parse the sheet from the top to reach any given row.

Common pitfalls

• Forgetting the header in later parts. Each part needs its own header row; pandas adds it, a streaming splitter must write it.
• Unpadded numbers. part_10 sorts before part_2; zero-pad to the number of digits you need.
• Leading zeros in IDs. Reading with pandas without dtype=str turns "00123" into 123; the streaming version copies values as stored.
• Not closing the read-only source. It keeps the file open until close(), which locks it on Windows.
• Trusting the split without checking. Compare counts and keys; an off-by-one loses one row per part.

Conclusion

For files that load comfortably, read with pandas and write iloc slices of N rows to zero-padded file names. For large files, stream: openpyxl read-only to iterate the source, a write-only workbook per part with the header written first, and a new part whenever the current one is full — with WriteOnlyCell for date formats. Then compare row counts and IDs between the parts and the source before sending anything on.

Frequently asked questions

How do I split an Excel file into multiple files with Python? Read the sheet with pandas and write df.ilocstart:start + n slices with to_excel in a loop, one file per slice. For very large files, stream rows with openpyxl in read-only mode into write-only workbooks instead.

How do I keep the header row in every split file? pandas writes the header with each to_excel call. With openpyxl, read the first row once and append it to each new output sheet before any data rows.

How can I split a file that is too large to load into memory? Use openpyxl: load_workbook(path, read_only=True) to iterate rows without loading the sheet, and Workbook(write_only=True) for each part, which writes rows to disk as they are appended.

How do I split by file size rather than row count? Write one part, measure its size, and scale the row count to your size limit with a safety margin. The exact size depends on compression, so check the largest part afterwards.

Related

• Up one level: Working with Large Excel Files in Python — memory, speed and streaming.
• Read a Large Excel File in Chunks with pandas — processing a big sheet part by part.
• Split One Excel Sheet into Multiple Files by Value — splitting by a column instead of a count.
• Zip Large Excel Attachments Before Emailing — the other answer to size limits.