How to Import Contacts Into a CRM From a Spreadsheet

Moving from a spreadsheet to a CRM is mostly a data-cleaning job with a short import at the end. Do the cleaning first and the import takes minutes. Skip it and you spend weeks fixing records.

This article is part of our guide to choosing a small business CRM. If you are still deciding whether to switch, start with CRM vs spreadsheet.

Before you touch the file

1. Make a copy. Keep the original spreadsheet untouched until you have checked the CRM.
2. Decide what you want to keep. Old leads you never contacted are rarely worth importing. A smaller, accurate list beats a large messy one.
3. List the fields you need. Name, email, phone, company, address and where the lead came from cover most small businesses. Add fields only when you will actually use them.

Prepare the file

Most CRMs read a CSV or Excel file with one header row and one contact per row. HubSpot's import guide, for instance, accepts .csv, .xlsx and .xls files with a single sheet and a header row where each column matches a property in the CRM. Other systems have similar rules, but the details differ, so read your CRM's own guide.

Then check each of these:

• One row, one contact. Split any row that holds two people or several phone numbers in one cell.
• Headers that match CRM fields. Rename columns like "Cust Name" to the field names your CRM uses, or map them during the import.
• A unique identifier. Email is the usual one for people. HubSpot's guide names email for contacts and the company domain for companies as the fields used to avoid duplicates.
• Consistent phone numbers. HubSpot's guide asks for the format +[country code][number], with an optional extension. Pick one format and apply it to the whole column.
• Consistent dates. Choose one date format for the whole file, and check that your CRM accepts it.
• Exact values for dropdowns. If a field only allows set options, such as lead source, the values in your file must match those options. HubSpot's guide says they must match either the label or the internal value.
• Encoding. Save as UTF-8 if names include accents or non-English characters.

Remove duplicates first

Sort by email and by phone number and look for repeats. Merge them by hand, keeping the most recent details. It is far easier to fix a duplicate in a spreadsheet than after the CRM has linked it to notes and deals.

Import a small test first

1. Copy 10 to 20 rows into a separate test file.
2. Import that file and open the records.
3. Check that names, phone numbers, dates and dropdown values landed in the right fields.
4. Fix the source file, then import the full list.

If the test shows a problem, you have 20 records to correct instead of 2,000.

After the import

• Spot-check a sample of records against the original spreadsheet.
• Check the counts. If the file had 640 contacts and the CRM shows 610, find out why, because rejected rows and merged duplicates are both possible.
• Add the source or tag you want on these contacts, such as "imported from spreadsheet", so you can find them later.
• Keep the original file for a while as a backup. Then stop using it. Two live lists are how contact details drift apart.

Limits to check

CRMs cap file size, rows and imports per day, and the caps often depend on your plan. HubSpot's guide lists different limits for its free plan and its paid plans, for example. If you have a large list, check the current limits before you start.

Not sure a CRM is worth the effort? Put your own numbers into our CRM ROI calculator, or start with a free CRM for small business.