Short answer
The most reliable way to migrate customers from Google Sheets to a CRM is to first clean your spreadsheet, then map columns to CRM fields accurately, and finally run a small test import before the full migration. This approach prevents data loss and duplication.
Start by auditing your Google Sheet for duplicates, blank cells, inconsistent phone and date formats, and stray spaces. Then, decide which columns correspond to your CRM's standard fields like Name, Email, and Company. Many CRMs provide a pre-formatted import template that only includes allowed fields-using it reduces errors. Finally, import a test batch of 5-10 rows to verify the mapping, then proceed with the full dataset in smaller batches to catch issues early.
Prepare Your Spreadsheet
Before migrating, open your Google Sheet and review it for common problems that often lead to data loss. Look for duplicate rows-especially by email-and decide whether to merge them or keep separate records. Also check for blank rows, merged cells, formulas that display errors, and text spilling into adjacent cells. Ensure that each column holds a single value. If a field contains multiple values, like several phone numbers, split them into separate columns.
Standardize data formats while the sheet is still in Google Sheets. Remove leading and trailing spaces using =TRIM(A1). Convert all dates to a consistent format (e.g., YYYY-MM-DD). Unify phone numbers into one international format, such as E.164, so they match CRM expectations. Using simple functions like UNIQUE() helps spot duplicates, and COUNTIF() can quantify them. Cleaning now avoids importing garbage that may crash or corrupt the CRM records.
- Delete empty rows and columns before export.
- Use =TRIM() to remove excess spaces.
- Check for duplicates with UNIQUE() and review them manually.
- Convert phone numbers to E.164 format (e.g., +14155552671).
Map Columns to CRM Fields and Plan Relationships
Understanding how your CRM stores data is key to a smooth migration. CRMs like HubSpot and Salesforce have separate objects for Contacts, Companies, Deals, and Tickets. Each object has standard properties (fields) that you can populate. When migrating customers, you typically create Contact records and link them to Company records. To avoid breaking these links, plan how you will re-create them after import-usually by using a unique key, such as company name or a customer ID that appears in both exported files.
Check your CRM’s documentation for import tools that support field mapping. Both HubSpot and Salesforce provide wizards that let you choose which spreadsheet column goes into which CRM property. Some CRMs offer an import template file that lists accepted fields-download and use it to avoid mapping unsupported columns. Also note that some CRMs can automatically associate records based on email or company domain during import, but if not, you’ll need to associate records after import via built-in bulk tools or an API.
- Identify which columns become standard fields (First Name, Last Name, Email, Phone, Company).
- Decide which spreadsheet rows become Contact records and which become Company records.
- Verify if your CRM can auto-link records via email or company name at import time.
- If not, plan to use the CRM’s association feature later-HubSpot’s Associations API lets you create relationships between records using their IDs, as documented in their developer guide.
Back Up and Prepare Clean CSV Files
Never migrate without a backup. Download an untouched copy of your Google Sheet as an Excel or CSV file and store it offline. Then, prepare separate CSV files for each CRM object you plan to import. For example, if you have contacts and companies, export contacts.csv containing only contact data, and companies.csv with only company data. Include a shared identifier (like a company name or custom ID) in both files so you can later reconnect them.
If your CRM offers an import template, use it. HubSpot’s import tool allows you to download a template that contains standard fields and required values. Salesforce’s Data Import Wizard also provides a similar template. Copy your cleaned data into that template, making sure each column matches the expected data type (e.g., text, number, date). Using a template reduces formatting errors and ensures you are not including fields the CRM will reject.
- Download a backup of your original spreadsheet as CSV or Excel.
- Split data into separate CSV files per object-one for contacts, one for companies.
- Always use the CRM’s own import template when available.
- Keep the unique key column in both CSVs to re-link later.
Run a Test Import and Validate
A test import is mandatory. Upload a sample of 5-10 rows that represent the variety in your data-include edge cases like international phone numbers or multi-word company names. After importing, open those records in the CRM and verify that fields are populated correctly. Check for mapped fields, correct date formats, and that no data is missing. If the CRM allows, compare the source row count to the imported records count.
If errors appear, read the error messages. Most CRMs produce an import report that lists failed rows and reasons. Fix the source CSV, not the CRM, and test again. Repeat until the sample imports cleanly. Only then should you proceed with the full dataset.
- Test with 5-10 realistic rows before full import.
- Check every field on a few records in the CRM.
- Observe error logs and update your CSV accordingly.
- Never import the whole dataset without a successful test run.
Import in Batches and Verify Relationships
When you’re ready, import the remaining data in batches of 100-500 rows rather than all at once. This reduces the chance of timeouts or partial imports and makes it easier to identify problems. After each batch, compare the row count in your source file to the number of records successfully imported in the CRM. This simple count check helps you catch missing records immediately.
After all objects are imported, verify relationships. If you have Contacts linked to Companies, check a sample set in the CRM to confirm each contact has the correct company. If your CRM did not auto-associate records during import, you must link them now. HubSpot’s Associations API allows you to create bulk associations using record IDs. You’ll need to map your unique key (e.g., company name) to the CRM record IDs-this might require an export or API query. Salesforce’s standard import tools often handle lookups during import, but for complex relationships you may need to use its Data Loader to update records with external IDs.
- Import in batches no larger than 500 rows.
- After each batch, confirm the imported count matches your source rows.
- Use the CRM’s association tools or API to link records after import.
- Refer to your CRM’s developer docs for API details-for example, the HubSpot guide explains how to associate records with or without labels.
What to verify
- CRM-specific steps and interface options change; check the latest official documentation for your CRM (e.g., HubSpot developer docs or Salesforce help pages).
- Association type IDs and default labels differ by subscription; confirm with your CRM provider.
- This is general guidance, not a substitute for your CRM vendor’s support or for professional consultation.
Questions and answers
What is the most common cause of data loss during migration?
Skipping data cleaning and not running a test import are the top reasons. Duplicate rows, wrong column mapping, and data type mismatches can cause records to be merged incorrectly or rejected. Always audit your spreadsheet and test with a small sample before full import.
How do I keep contact and company relationships when moving from spreadsheets?
Use a unique identifier that exists in both contacts and companies exports, like a company name or custom ID. After importing both object types, use your CRM’s association feature to link records. For example, HubSpot provides an Associations API to create relationships between contacts and companies using their internal record IDs, so you must map your unique key to those IDs. [1]
Should I use CSV or Google Sheets integration for migration?
CSV is generally safer because you have full control over data formatting and mapping. Google Sheets integrations often limit field mapping and may introduce sync issues. Export to CSV, clean it, and use the CRM’s import wizard for better reliability.
Sources and verification date
- Official source: developers.hubspot.comdevelopers.hubspot.com · Checked
- Official source: help.salesforce.comhelp.salesforce.com · Checked