How to Prepare a CSV for CRM Migration (Without Losing Data)

CRM migrations fail in two stages. First, the export looks fine. Then the import runs, and a week later someone notices that 30% of the deals have no close date, half the lead sources are blank, and every lifecycle stage says “Subscriber” because the source values didn’t match the target picklist.

By the time you spot it, the data is already in the new system. Untangling it after the fact is harder than fixing the file before upload.

This guide walks through how to prepare a CSV export for CRM migration: the audit, the schema mapping, the picklist normalisation, and the date and number gotchas that quietly destroy migrations.

Why CRM migrations lose data

The export from your old CRM is rarely shaped like the import the new CRM expects. The differences fall into four categories, and each one is a place where data goes missing if you don’t handle it before upload:

Schema mismatch. Field names, field types, and the set of available fields all differ. Deal Stage in HubSpot maps to StageName in Salesforce, and Salesforce expects very specific values.
Picklist value mismatch. Each CRM has its own default picklist values for lead status, lifecycle stage, industry, country. A value that was valid in the old CRM is rejected or silently dropped in the new one.
Date and number formatting. Exports often use the locale of the user who ran them. A US user exporting 01/15/2024 and a French user importing into a system configured for 15/01/2024 will see every date misinterpreted.
Relational data. Contacts belong to companies. Deals belong to contacts. The IDs that linked them in the old CRM are meaningless in the new one. You need a re-key strategy before import, not after.

Each of these problems is invisible in the export file. The CSV looks fine in Excel. It only breaks once it hits the new CRM, where the cost of fixing it is much higher.

Step 1: Audit the source export before you touch it

Before mapping anything, run a column-by-column audit on the export from the old CRM. The goal is to know exactly what’s in the file: which columns matter, which are empty, and which contain values you’ll need to clean.

For each column, check:

Fill rate. How many rows have a value? A column that’s 95% empty is a candidate to drop, not migrate.
Unique value count. A picklist column should have a small number of unique values. If Lead Source has 400 unique values, you have free-text data masquerading as a picklist, and that needs to be normalised before it lands in the new system.
Sample values. Pull 5–10 random values per column. Look for inconsistent capitalisation, trailing spaces, language variants, and obvious typos.
Date format. Are dates ISO (2024-01-15), regional (15/01/2024 or 01/15/2024), or text (January 15, 2024)? Mixed formats in the same column are a red flag.
Number format. EU exports use 1.234,56. US exports use 1,234.56. The new CRM’s parser expects one specific convention.

Write the audit findings down. The list of columns to keep, drop, or transform becomes the input to your mapping step.

Step 2: Map the old schema to the new schema

Don’t assume the field names in your export will match the field names the new CRM expects. They won’t.

Build a mapping table with three columns: old field, new field, and transformation needed.

Old field (HubSpot export)	New field (Salesforce import)	Transformation
`First Name`	`FirstName`	Rename
`Last Name`	`LastName`	Rename
`Email`	`Email`	Direct
`Lifecycle Stage`	`LeadSource` (partial)	Picklist remap
`Lead Status`	`Status`	Picklist remap, value differences
`Create Date`	`CreatedDate`	Reformat to ISO 8601
`Original Source`	`LeadSource`	Picklist remap
`Country`	`Country`	Normalise to ISO country names

The mapping table is the single source of truth for the migration. It lives outside the CSV and outside the CRM, so you can review it, share it with stakeholders, and apply it the same way every time you re-export and re-import during testing.

A few rules that make schema mapping survive contact with reality:

Map to fields that exist. Pull the field list from the target CRM directly. Don’t guess. Salesforce’s Status has a specific set of allowed values that depend on the org’s configuration, see Salesforce CSV import: column names, picklist values, and common errors for the Data Import Wizard vs Data Loader distinction. HubSpot’s lifecyclestage uses internal values like marketingqualifiedlead (no spaces, all lowercase). Pipedrive’s stage names are pipeline-specific, and are covered in Pipedrive CSV import: column names, field types, and common errors.

Decide what happens to fields with no destination. Some old fields won’t have a clean home in the new CRM. Either create custom fields in the new CRM before import, concatenate them into a notes field, or accept that they’re being dropped, and document the decision.

Plan for relational data. If the export contains both contacts and the companies they belong to, you usually import companies first, then contacts with a Company Name (or external ID) reference. The new CRM links them on import using that name. Map your old company-ID-based relationships to name-based or external-ID-based relationships before export.

Step 3: Normalise picklist values to the target CRM’s allowed values

Picklist values are where most migrations bleed data quietly. The old CRM accepted Marketing Qualified Lead. The new CRM expects MQL or marketingqualifiedlead or Marketing qualified lead (case-sensitive). Any mismatch means the field is blanked or the row is rejected, depending on the platform.

For each picklist column, build a normalisation table: every observed source value mapped to the exact target value.

For a Lifecycle Stage column going from a generic CRM into HubSpot:

Source value	HubSpot value
`Subscriber`	`subscriber`
`Lead`	`lead`
`MQL`	`marketingqualifiedlead`
`SQL`	`salesqualifiedlead`
`Customer`	`customer`
`Other`	`other` (no direct equivalent)

The two things that ruin this step:

Defaulting to “leave it as-is” for unmapped values. If a value isn’t in your table, it shouldn’t pass through unchanged into the import: that’s how silent failures happen. Either map every observed value, set unmatched values to a sane default, or leave them blank for manual review.

Not pulling values from the target CRM directly. The defaults documented online are a starting point. Your org’s actual allowed values often differ. For HubSpot, check Settings → Properties → [Property Name] → Field options. For Salesforce, Setup → Object Manager → [Object] → Fields & Relationships → [Picklist Field] → Values. For Pipedrive, Settings → Data Fields.

For a deeper walk-through of picklist normalisation specifically, see how to standardise picklist values before a CRM import.

Step 4: Standardise dates and numbers

Date and number formats are the second-most-common silent failure mode in migrations.

Dates. Most CRMs accept ISO 8601 (YYYY-MM-DD) reliably. If your export has dates in regional format (DD/MM/YYYY or MM/DD/YYYY), convert them all to ISO before upload. The ambiguous ones (01/02/2024 could be January 2nd or February 1st depending on locale) are the dangerous ones, and you need to know which format the export was written in to disambiguate.

If your team works across regions and the source data mixes formats, you cannot fix this at import time. The format has to be declared per file, not guessed per row.

Numbers. EU formats use comma as decimal separator and period as thousands (1.234,56). US formats use period as decimal and comma as thousands (1,234.56). If the new CRM’s locale doesn’t match the export’s locale, every numeric value is misread. A deal of 1.000,00 (one thousand, EU) becomes 1.0 (one) when parsed as US format. Always set the parser’s expected number format explicitly.

Currencies. If your exports include multiple currencies and the target CRM has a single base currency, decide upfront whether to convert at export time, at import time, or store an additional currency code field.

For more on this, why European CSV files break in CRM imports covers the EU/US format collision in detail.

Step 5: Test with a small subset first

Don’t run the full import on the first try. Pick a representative slice: 50 to 200 rows that cover the edge cases you found in the audit (every picklist value, every date format, every type of relational link).

Run that subset through the import, then audit the result inside the new CRM:

Did the picklist values land in the right buckets?
Are dates showing correctly in the user’s locale?
Are relational links resolved (contacts attached to the right companies)?
Are required fields populated on every row?
Are any rows in error state, and if so, why?

Fix the mapping, re-run the subset, audit again. Only after a clean test run should you go for the full migration.

This loop is where a reusable mapping pays for itself. If you’re tweaking a Google Sheet formula chain between every test run, the iteration cost is high. If your mapping is a saved configuration that re-applies in one click, you can iterate fast and catch issues before they’re at scale.

Step 6: Migrate, then verify in the target CRM

After the full import, run a sanity check before declaring the migration done.

A useful set of post-import audits:

Row count match. Did every row in your file land in the new CRM? Compare expected vs. imported counts per object.
Required field fill rate. For each required field in the target CRM, what percentage of imported records have a value? Anything below 100% means the field was rejected or silently dropped.
Picklist distribution. Compare the distribution of picklist values in the source vs. the target. If 40% of records had Lifecycle Stage = Customer in the old CRM and only 5% do in the new one, your mapping silently re-bucketed them.
Spot-check linked records. Pick 20 records at random and verify their related records (contacts on companies, activities on deals) are linked correctly.
Date-bound queries. Run a “created in 2023” query in the new CRM. The count should match the same query in the old one.

If anything’s off, your mapping needs a fix and you may need to re-run a subset. This is much cheaper to do in the first 24 hours after migration than three months later when the new CRM has new data layered on top.

Migration checklist

Before you run the full import, walk through this list:

Where Asphorem fits in

A CRM migration usually involves several iterations of the export → clean → import → audit loop. Each cycle, you re-pull the export from the old CRM and re-apply the same transformations.

Asphorem’s CSV Normalizer is built around this. The mapping (schema rename, picklist normalisation, date format, output settings) is saved once and re-applied to every export. AI-assisted matching handles the picklist values you didn’t anticipate without making you write a new formula every time. File rows never leave your browser, which matters when you’re moving customer records around.

The mapping also survives the migration. Once the new CRM is the system of record, the same mapping handles ongoing imports from data warehouses, partner systems, or list-buying sources into the new schema.

CRM Migration: Frequently Asked Questions

What’s the most common cause of data loss in a CRM migration?

Picklist value mismatches. The source CRM’s value for a field doesn’t match the target CRM’s allowed values, so the field is silently set to blank or the row is rejected. This is invisible in the CSV and only surfaces inside the new CRM after import. Always normalise picklist values to the target CRM’s exact allowed set before upload.

Should I migrate all historical data or only active records?

It depends on how much value the historical data has and how clean it is. Migrating ten years of low-quality lead records means ten years of dirty data in your new system. A common pattern is to migrate active and recently-active records into the main CRM and archive everything else into a separate data store you can query if needed.

How do I handle custom fields that don’t exist in the target CRM?

Either create the equivalent custom field in the target CRM before import, concatenate the data into an existing free-text field (like Notes), or accept that the field is being dropped. Document the decision so it’s clear later why the data isn’t there.

Can I migrate contacts and companies in a single import?

Most CRMs require you to import the parent objects (companies/accounts) first, then the child objects (contacts/leads) with a reference to the parent. The reference is usually by name or external ID, since the internal IDs from the old CRM aren’t valid in the new one. Plan the order of imports carefully.

How long should a CRM migration take?

A clean migration of 10,000–100,000 records, with a well-prepared mapping and a tested subset run, usually takes 1–2 days of execution time. The preparation (audit, schema mapping, picklist normalisation, test runs) takes longer than the actual migration. If your prep is rushed, your migration will leak data, and the cleanup afterwards costs more than doing the prep right would have.