Can I split a CSV in Excel?

Only if the file opens fully, which means it must be under the row limit. Larger files need Power Query, a script or a command-line tool, because Excel truncates what it can't display and warns that not all data loaded.

Does splitting change my data?

Not if you read and write every column as text. Letting tools infer types can reformat dates and drop leading zeros.

How do I stop the header repeating when I rejoin CSV chunks?

Read each chunk as a CSV (pandas does this) or skip the first line of every file after the first when concatenating as text.

Why does my rejoined file have fewer rows than expected?

Usually a quoted field with a line break was split across two chunks by a line-based tool, or a part was saved with rows removed. Count rows per part to find which one.

How many rows per chunk should I use for Excel?

Stay under 1,048,576 including the header. Many people use 1,000,000 or less to leave room for edits.

Can I split by a column value instead of row count?

Yes. In pandas, loop over df.groupby("region") and write each group to its own file.

How to split a large file into chunks and rejoin it safely

By the Analistable team · Updated 2026-10-10 · 5 min read

Split by rows, not bytes, and repeat the header in every chunk so each part opens on its own. When rejoining, keep the header from the first chunk only, concatenate the rest in order, and confirm the total row count matches the original. Use a CSV-aware tool, because a quoted field with a line break inside it will break a plain line-based split.

Part of our guide: How to merge CSV files into one

Why you'd split and rejoin

The file has more rows than a spreadsheet can hold (Excel stops at 1,048,576 rows) and someone needs to review it in parts.
An upload form or email has a size limit.
You want to hand chunks to several people to clean, then put the cleaned parts back together.

The risks are the same every time: headers end up in the middle of the data, rows get dropped at chunk boundaries, or a multi-line field gets cut in half. For simply combining files that were never one file, see merging large CSV files.

Split with a header in every chunk

This pandas script writes chunks of 900,000 rows, each with its own header, numbered so they sort correctly.

import pandas as pd

for i, chunk in enumerate(pd.read_csv("customers.csv", dtype=str,
                                      chunksize=900_000), start=1):
    chunk.to_csv(f"customers_part{i:03d}.csv", index=False)
    print(i, len(chunk))

Reading everything as str means nothing is reformatted: leading zeros, long IDs and dates stay exactly as they were. Zero-padded numbers (part001, part002…) keep the files in order when sorted by name, so part010 doesn't land before part002.

On macOS or Linux you can split from the terminal, but split -l doesn't repeat the header and cuts by physical lines, so only use it when you know no field contains a line break:

tail -n +2 customers.csv | split -l 900000 -d -a 3 - part_
for f in part_*; do (head -n 1 customers.csv; cat "$f") > "$f.csv"; rm "$f"; done

The -d flag for numeric suffixes is in GNU split; on older macOS versions you may need to drop it and accept letter suffixes.

Rejoin with one header

from pathlib import Path
import pandas as pd

parts = sorted(Path(".").glob("customers_part*.csv"))
df = pd.concat((pd.read_csv(p, dtype=str) for p in parts), ignore_index=True)
df.to_csv("customers_rejoined.csv", index=False)
print(len(parts), "parts,", len(df), "rows")

Because each part is read as its own CSV, its header becomes the column names and isn't repeated in the data. If someone added or renamed a column in one part, concat will create a separate column for it, which is a useful signal that the parts no longer agree. The general patterns are in merging CSV files in Python.

Verify nothing was lost

Row counts for a 2,500,000-row file split into 900,000-row parts
File	Data rows
customers_part001.csv	900,000
customers_part002.csv	900,000
customers_part003.csv	700,000
Sum of parts	2,500,000
customers_rejoined.csv	2,500,000

Compare the row count of the rejoined file with the original.
Check the key column is still unique: df["customer_id"].is_unique.
Search for a header value appearing as data: (df["customer_id"] == "customer_id").sum() should be 0.
If nothing was meant to change, compare a checksum of a sorted export of both files.

When the parts were edited

If people cleaned their chunks in Excel before you rejoin, check for these side effects:

Leading zeros removed and long numbers shown in scientific notation, if the file was saved after opening it directly.
Dates converted to a local format such as 03/04/2026.
Different encodings (UTF-8 vs Windows-1252) if parts were saved on different machines; pass encoding= when reading.
Rows deleted as "duplicates" in one part but not another.

Keeping a unique ID in every row lets you join the cleaned parts back to the original and see exactly what changed, rather than trusting the edited copies.

If the only reason to split was to analyse the file, you may not need to. Analistable loads CSVs into DuckDB in your browser (no upload) and can query beyond the sheet row limit, within your device's memory. The free merge CSV tool rejoins parts client-side too. More in the CSV merging guide.

Why line-based tools can break rows

A CSV row is not always one line. If a field such as an address or comment contains a line break, the field is wrapped in double quotes and the record spans two or more physical lines. Tools that split every N lines, like split -l, can cut that record in half: the first part ends mid-field and the next starts with a fragment. Both parts then misread every row that follows. CSV-aware readers (pandas, the Python csv module, DuckDB, Power Query) understand quoting and always cut between whole records.

If you aren't sure whether your file has embedded line breaks, compare the number of physical lines (wc -l) with the number of records pandas reads plus one for the header. If they differ, use a CSV-aware method.

Keep a manifest

When you split, write a small manifest listing each part's file name, row count and first and last ID. When the parts come back, check them against it before rejoining. It turns 'I think it's all there' into a check anyone can repeat.

Frequently asked questions

Can I split a CSV in Excel?: Only if the file opens fully, which means it must be under the row limit. Larger files need Power Query, a script or a command-line tool, because Excel truncates what it can't display and warns that not all data loaded.
Does splitting change my data?: Not if you read and write every column as text. Letting tools infer types can reformat dates and drop leading zeros.
How do I stop the header repeating when I rejoin CSV chunks?: Read each chunk as a CSV (pandas does this) or skip the first line of every file after the first when concatenating as text.
Why does my rejoined file have fewer rows than expected?: Usually a quoted field with a line break was split across two chunks by a line-based tool, or a part was saved with rows removed. Count rows per part to find which one.
How many rows per chunk should I use for Excel?: Stay under 1,048,576 including the header. Many people use 1,000,000 or less to leave room for edits.
Can I split by a column value instead of row count?: Yes. In pandas, loop over df.groupby("region") and write each group to its own file.