How to split a large file for a CRM import
When a file is over your CRM's import limit: check its real size, pick a safe size for each part, split it in Excel or Operelio, then import the parts in order.
By Operelio team
On this page9
When a file is too big to import
Every CRM's importer has a ceiling: a limit on file size, on rows per file, or both. A file over it is refused, or in some CRMs goes in only in part. The fix is to split it into parts that each fit, with the header row in every part, and import them one after another.
Before you split anything, check that the file really is that big. A spreadsheet can report far more rows than it shows, and then the right fix is to clean it, not to split it.
First, check how big the file really is
An importer counts every row the spreadsheet says it uses. Formatting, a stray space or a value far below your data makes those empty-looking rows count, so a list of a few thousand contacts can be refused as hundreds of thousands of rows.
Find the real last cell
Open the file in Excel and press Ctrl+End (on a Mac, Control+End, or Fn+Control+Right arrow on a laptop keyboard). Excel jumps to the last cell it thinks is in use. If that is far below or to the right of your data, the file is carrying empty rows or columns.
Delete what is past your data
Select the first empty row below your data, press Ctrl+Shift+Down arrow to reach the bottom of the sheet, and delete those rows (right-click, Delete, not just the Delete key, which only clears them). Do the same for the empty columns to the right.
Save, close and check again
Save the file, close it and open it again, then press Ctrl+End once more. It should now stop at your last real row. If it still does not, copy only your data into a new sheet and save that instead.
Save it as CSV UTF-8
In Excel, File, Save As, CSV UTF-8. A CSV keeps no formatting, so it is usually smaller than the workbook, and it is the one format every CRM's importer takes. It saves only the sheet you are on.
If the real row count is now under your CRM's limit, you are done: import the cleaned file as it is.
Work out how many rows go in each part
Each CRM's importer caps the file size, the rows per file, or both. These are from each vendor's own help pages; the CRM import limits page has every limit in full, with the sources.
Leave some room under a row limit: aim for about 10% fewer rows than the cap, so a header row or a few extra lines never tip a part over. For a CRM that limits file size only, work out the size of a row first: divide the file's size by its number of rows. A 12 MB file with 40,000 rows is about 300 bytes a row, so Copper's 3 MB holds about 10,000 of those rows, and parts of 8,000 leave room.
| CRM | Largest file | Rows per file |
|---|---|---|
| HubSpot | 512 MB on paid accounts, 20 MB on free | 1,048,576 per file on paid accounts; 500,000 a day on free |
| Salesforce | 100 MB, and 400 KB per record | 50,000 records per import |
| Pipedrive | Under 50 MB | 50,000 rows |
| Zoho CRM | 25 MB per file | By edition, per file: Free 1,000, Standard 20,000, Professional 50,000, Enterprise 100,000, Ultimate 200,000 |
| Freshsales | 5 MB | No row limit documented |
| Close | 15 MB per CSV file; no limit documented for Excel files or pasted rows | No row limit documented |
| Monday.com | 10 MB | Monday.com's pages differ: 8,000 rows for a new board, 10,000 for an existing one |
| Copper | 3 MB | No row limit documented |
Split so related records still link
Sort the file by company before you split it, so all of one company's contacts land in the same part. Some importers build one record from several rows: Close groups rows into one lead by a column you pick, so one company's rows spread over two parts can end up as two leads, unless the second import is set to match the lead the first one made.
If your CRM wants companies before contacts, split them that way too. Copper says to import companies first, then people, then opportunities, so each can link to the one before. Where one file holds both, a company's rows stay together once the file is sorted by company.
Split it in Excel
For a handful of parts, Excel is enough:
Sort the file
Sort by company, or by whatever keeps related rows together.
Copy the header and the first block of rows
Click the Name Box (left of the formula bar), type a range such as A1:Z45001 for the header plus 45,000 rows, press Enter and copy.
Paste into a new workbook and save it
Paste into a new workbook and save it as CSV UTF-8 with a numbered name, such as contacts_part1.csv.
Repeat with the header each time
Type the header and the next block into the Name Box together, such as A1:Z1,A45002:Z90001, then copy, paste and save it as part 2. Every part needs the header row, or the importer reads your first contact as the column names.
Check the totals
Add up the rows in the parts, not counting their headers. The total should match the original file. A shortfall means a block was missed or overlapped.
A file too big to open in Excel
An Excel sheet holds at most 1,048,576 rows, so a larger CSV will not open whole. On a Mac or Linux, the Terminal can split it without opening it. This keeps the header in every part (change 45000 to the rows you want in each):
head -n 1 contacts.csv > header.csv tail -n +2 contacts.csv | split -l 45000 - part_ for f in part_*; do cat header.csv "$f" > "$f.csv" && rm "$f"; done
This splits on line breaks. If a cell holds a line break, such as an address typed over two lines, a part can end in the middle of that row. Check the last row of each part, or use a tool that reads the file as a CSV.
Split it by row count in Operelio
Operelio's Split File tool, on every plan, splits a CSV or Excel file into parts of the size you set. Choose a fixed number of rows per file, and it writes contacts_part1.csv, contacts_part2.csv and so on into one ZIP, each with the header row and every column. The file you split is limited by your plan: 2,000 rows and 2 MB on Free, 50,000 rows and 25 MB on Starter, 75,000 rows and 35 MB on Pro, and 150,000 rows and 100 MB on Agency. A file with more rows than your plan takes is split up to that many rows only, and Operelio tells you so; a file over the size limit is refused when you upload it. One job makes up to 25 files on Free, 500 on Starter, 1,000 on Pro and 2,000 on Agency.
It does not sort, so sort by company first if related rows have to stay together. If you format the file for your CRM in the CRM Formatter first, split the file it gives you, so every part already has your CRM's field names.
Import the parts
Import one part, check it, then the next. Each importer reports the rows that failed, and a mistake caught on part 1 is one fix instead of ten. Give each import a name that says which part it was, so you can find it in the import history, and undo it where your CRM allows.
Some importers limit how many run at once. HubSpot runs up to 3 imports at a time, with no more than 2 over 10,000 rows. Freshsales runs one import per module at a time. Monday.com takes one file at a time per board and 100 files an hour per account. HubSpot also caps rows per day, on a rolling 24 hours: 10,000,000 on paid accounts and 500,000 on free.
Split a file into parts of a set number of rows, each with the header row, ready to import one by one.
Related
Frequently asked questions
How do I split a CSV into smaller files?
Decide how many rows each part can hold, then copy the header and a block of that many rows into a new file, and repeat until every row is used. Excel does this by hand for a few parts; Operelio's Split File does it in one run and puts the header in every part; the Terminal splits a file too big to open in Excel.
Does every part need the header row?
Yes. The importer reads the first row of each file as the column names. A part without the header loses its first row of data to that, and its columns map to nothing.
Why was my file refused as too big when it isn't?
The spreadsheet counts rows that look empty but hold formatting or a stray value, and the importer counts them too. Press Ctrl+End to see where Excel thinks the data ends, delete the rows and columns past your data, and save again.
How many rows should each part have?
About 10% fewer than your CRM's row limit, and small enough to stay under its file size limit. For a CRM that limits size only, divide the file's size by its rows to get the size of a row, then work out how many rows fit.
Can I import the parts at the same time?
Import them one after another and check each. Some CRMs limit it anyway: HubSpot runs 3 imports at once, Freshsales one per module and Monday.com one file per board.
Ready to get started?
Upload a file and run your first transformation. Free, no credit card required.