Most CRM migrations hit a wall before a single record lands in the system. The culprit is almost never the CRM itself — it is the spreadsheet that feeds it. When you prepare data for CRM import carefully, the actual upload takes minutes. Skip the prep, and you spend days cleaning up the aftermath.

Why Spreadsheet Quality Determines Migration Success

Think about where that contact list came from. Probably a mix of an old CRM export, a few sales reps' personal sheets, a trade-show CSV from two years ago, and maybe a manually typed list someone maintained in their inbox. Each source has its own column names, date styles, phone formats, and general level of discipline.

A CRM import wizard does not know that "Cell Phone", "Mobile", and "Ph #2" all mean the same thing. It maps what it sees. Feed it chaos, get chaos back.

The good news: the problems are predictable. And predictable problems have solutions.

Step 1 — Consolidate Into One File

Before any formatting work begins, merge everything into a single sheet. Use one row per contact, one row per company, or one row per deal — whichever object you are importing first. Do not mix object types in the same file.

If you are working from multiple sources:

  • Copy all sheets into one "master" tab.
  • Add a column called source and tag each batch (e.g., hubspot_export, events_2024, sales_maria).
  • Do not delete source files yet. You will need them for the dedup check later.

That source column costs nothing and saves you hours if something goes wrong post-import.

Step 2 — Standardize Column Headers

This is where most imports break down. CRM systems expect specific field names or give you a mapping screen where you match your columns to theirs. Either way, clean column headers make the job faster and reduce human error during crm column mapping.

A rule of thumb: use lowercase, no spaces, no special characters. first_name beats First Name every time. Rename aggressively before you open the CRM import wizard.

Messy Header Clean Header Notes
First Name first_name Consistent casing, no space
COMPANY NAME company_name Avoid all-caps
Ph # / Cell phone Pick one field per concept
E-Mail Address email Simpler, standard
Date Added (MM/DD) created_date Include year format too
Notes / Comments notes Merge if both exist
Sales Rep Assigned owner_email Use email, not display name

One column per piece of data. If someone crammed "City, State, ZIP" into a single cell, split it now. Most CRMs store these as separate fields.

Step 3 — Handle Required Fields First

Every CRM has at least one required field — usually email or company name. If a row is missing that value, the row will either error out or import as a shell record that causes problems later.

Go through your sheet and filter for blanks in required columns. For contacts, that typically means:

  • email — the most common unique identifier
  • first_name or last_name — at minimum one of these
  • company_name for B2B records

Rows that genuinely cannot be completed should be moved to a separate "incomplete" tab. Do not delete them — flag them for manual follow-up after the import finishes.

Step 4 — Normalize Data Formats

Data formatting before import is tedious, but it is not optional. CRMs store dates, phones, and yes/no fields in specific ways. Mismatches silently corrupt records.

Dates. Pick one format and apply it everywhere. ISO 8601 (YYYY-MM-DD) is the safest bet across systems and locales. If your sheet shows 03/12/24, that date is ambiguous — March 12 in the US, 3 December in much of Europe. Fix it.

In Excel or Google Sheets, use a custom number format YYYY-MM-DD on date columns, or write a formula like =TEXT(A2,"YYYY-MM-DD") to convert.

Phone numbers. Strip everything except digits and a leading + for international format. (555) 867-5309 becomes +15558675309. Some CRMs accept the formatted version, but the stripped version never fails.

Boolean fields. If your sheet has columns like "Active Customer" with values like "Y", "yes", "TRUE", "1", "active" — pick one and standardize. Most CRMs prefer true/false or 1/0.

Currency. Remove currency symbols and thousand separators from numeric columns. $1,250.00 should become 1250.00.

Step 5 — Run a Deduplication Pass

Duplicates are the gift that keeps giving — in all the wrong ways. Import a list with 200 duplicate email addresses and your CRM will happily create 200 records for the same person. Then your sales team emails someone six times in a week and wonders why churn is up.

The simplest contact list cleanup method: sort by email, use a formula to flag duplicates, then decide which record to keep.

In Excel: =COUNTIF($B$2:$B2,B2)>1 in a helper column (where B is your email column) marks every duplicate after the first occurrence. Filter for TRUE, review, delete.

For larger lists, a pivot table on email gives you a count instantly. Anything above 1 is a candidate for removal or merge.

Step 6 — Validate Email Addresses

Not just duplicates — invalid addresses. A contact list full of @gmial.com typos, placeholder entries like noreply@example.com, or outright junk (aaa@bbb) will inflate your contact count and damage deliverability if you send campaigns.

A basic check in a spreadsheet: use a formula to verify the presence of @ and a domain dot. For deeper validation, run the list through a free bulk email verifier before import. Remove hard-bounce-prone addresses now rather than after your first email send.

Step 7 — Check Character Encoding

This one is easy to overlook and painful to fix after the fact. If your contact list includes names with accented characters — Müller, García, Dupont — make sure your file is saved as UTF-8.

When you save a CSV in Excel, the default encoding is often Windows-1252 (Latin-1). Open that file in a CRM expecting UTF-8 and Müller becomes M�ller. Embarrassing in a sales email.

Force UTF-8 by using "Save As" > CSV UTF-8 (with BOM) in Excel, or export directly from Google Sheets (which defaults to UTF-8).

Step 8 — Build Your CRM Import CSV Template

Once your data is clean, restructure the sheet to match the exact column order and naming that your target CRM expects. Many CRMs publish a downloadable template — use it as your final destination format.

If no template exists, check the CRM's import documentation and list every field you plan to populate. Map your clean column headers to the CRM fields one by one before you start the wizard. Doing this on paper (or a mapping tab in your sheet) before touching the CRM import screen cuts the chance of a misfire significantly.

This is also the moment to decide what you are NOT importing. Legacy notes from 2017 that nobody will ever read? Leave them behind. Importing less but importing it correctly beats importing everything and spending a week correcting bad data.

A Quick Pre-Import Sanity Checklist

Before you click "Import" in the CRM, run through these:

  1. One row per record — no merged cells, no summary rows, no header rows buried mid-sheet.
  2. Required fields populated for every row.
  3. Dates in YYYY-MM-DD format.
  4. Email column has no obvious typos or blanks.
  5. No duplicate emails (or intentional duplicates are handled).
  6. File saved as CSV UTF-8.
  7. Column headers match your mapping plan.
  8. Numeric fields have no currency symbols or commas.

If all eight pass, you are ready.

After the Import — What to Check Immediately

Even a clean import deserves a spot-check. Pull 10-15 random records from the CRM after the job completes and compare them against your source sheet. Look for truncated names, missing phone numbers, or fields that landed in the wrong column.

Most CRMs show an import summary with a count of successful records and errors. Download the error log if one exists — it lists the exact rows that failed and why. Fix those in the source sheet and re-import just the error batch.

For teams doing this more than once a year, it is worth saving your cleaned sheet as a reusable crm import csv template. The column structure, the validation formulas, the dedup logic — all of it carries forward to the next migration. You do the work once, not ten times.

The question most teams ask too late: "What does our data actually look like?" Run that audit before you start the migration, not after. Clean data going in means a CRM that reflects reality — and a sales team that can actually trust what it sees.