How to split a large CSV file
Too many rows for Excel, too big for an import? How to split a CSV by row count, into equal parts or by a column, with the header on every file.
Large CSV files are common: a year of transactions, a full customer export, a log from a server. They’re also exactly the files that everyday software refuses. Excel stops at just over a million rows, email and CRM platforms cap imports and some web forms reject files over a few megabytes. Splitting the file is usually the fastest way through.
Three ways to split
Split CSV offers three:
- Every N rows. Files of a fixed size, with the last holding the remainder. Right for an import limit: set N to the limit and upload the parts one after another.
- Into N equal files. For sharing the work between people or machines.
- One file per value in a column. Group by region, customer, month or salesperson and each group gets its own file, named after the value.
Each part keeps the heading row and the numbering uses leading zeros so the files sort in order in any folder.
Why not just open it in Excel and cut
Because Excel won’t load past its row limit and because opening a CSV in Excel changes some of the data before you have done anything, removing leading zeros and rounding long numbers, as described in why Excel removes leading zeros. Splitting the raw file avoids both.
Rows that contain line breaks
Addresses and notes fields often contain line breaks inside quotes. A simple split by line count cuts those rows in half and shifts every column after them. Split CSV reads the file properly first, so a row with an embedded line break stays in one piece.
Tidying before and after
If the file has stray spaces, blank rows or duplicates, run it through the CSV cleaner first so the parts are clean. To recombine parts later or to combine exports from different months, merge CSV matches columns by heading. And if the data started life in a spreadsheet, Excel to CSV gets it out, including from old .xls files, as covered in how to open an .xls file without Excel.
Common questions
What is Excel's row limit?
1,048,576 rows per sheet. A CSV with more rows opens with the rest missing and Excel warns that the file wasn’t loaded completely. Google Sheets has a limit of 10 million cells per spreadsheet, which large files also reach.
Will splitting break rows that contain line breaks?
Not if the tool parses the CSV properly. A quoted field such as an address or a note can contain line breaks and splitting by counting lines cuts those rows in two. Split CSV parses first, so every row stays whole.
Does every part get the header row?
Yes, when the first row is marked as headings. A part without its headings would be almost useless to whatever imports it.
How do I put the parts back together?
Merge CSV combines them again, matching columns by their headings, so the result is identical to the original.