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.
1. 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.
2. 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.
3. 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.
4. 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 | 200,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 CRM | 10 MB | monday CRM'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:
1. Sort the file
Sort by company, or by whatever keeps related rows together.
2. 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.
3. 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.
4. 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.
5. 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. A short script splits it without opening it, on a Mac or on Windows. Both keep the header row in every part and read the file as a CSV: a row ends only where its quotes close, so a cell with a line break in it, such as an address typed over two lines, stays in its row.
On a Mac or Linux, open Terminal in the file's folder (in Finder, Control-click the folder in the path bar and choose Open in Terminal) and run this. Change contacts.csv to your file, name=contacts to the name you want for the parts, and 45000 to the rows in each part. The parts are saved in that folder as contacts_part1.csv, contacts_part2.csv and so on.
Split a CSV in the Terminal:
awk -v rows=45000 -v name=contacts '
{ q += gsub(/"/, "&"); rec = (inq ? rec "\n" $0 : $0) }
q % 2 { inq = 1; next }
{ inq = 0; q = 0 }
!header { header = rec; next }
n % rows == 0 { close(out); out = name "_part" (++part) ".csv"; print header > out }
{ print rec > out; n++ }
END { if (inq) print rec > out }
' contacts.csv
A file too big for Excel, on Windows
Windows PowerShell comes with Windows: open the Start menu, type Windows PowerShell and open it. Paste this in with $file set to your file's full path and 45000 changed to the rows you want in each part, then press Enter. The parts are saved next to the file as contacts_part1.csv, contacts_part2.csv and so on, named after your file. It was tested in PowerShell 7 and uses nothing Windows PowerShell 5.1 lacks, so it should run there too.
Split a CSV in PowerShell:
$file = "C:\Users\you\Downloads\contacts.csv"; $rows = 45000
$path = (Resolve-Path $file).Path
$name = [IO.Path]::GetFileNameWithoutExtension($path)
$enc = [Text.Encoding]::GetEncoding(28591)
$in = New-Object IO.StreamReader ($path, $enc, $false)
function Read-Row {
$row = $in.ReadLine()
while ($null -ne $row -and $row.Split('"').Length % 2 -eq 0) {
$more = $in.ReadLine(); if ($null -eq $more) { break }
$row += "`r`n" + $more
}
$row
}
$header = Read-Row; $out = $null; $part = 0; $n = $rows
while ($null -ne ($row = Read-Row)) {
if ($n -eq $rows) {
if ($out) { $out.Close() }
$part++; $n = 0
$dest = Join-Path (Split-Path $path) "$($name)_part$part.csv"
$out = New-Object IO.StreamWriter ($dest, $false, $enc)
$out.WriteLine($header)
}
$out.WriteLine($row); $n++
}
if ($out) { $out.Close() }; $in.Close()
A quote that is never closed puts the rest of the file into one row, in either script. If a part ends with one very long row, find the stray quote in that row, fix it and run the script again.
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 Row count, set 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 CRM 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.
Originally published in the Operelio how-tos. What's the biggest file you've had to get into a CRM?
Top comments (0)