How to clean customer data before importing into a CRM

A CRM import is close to irreversible in practice. Once ten thousand contacts are in, the duplicates have been created, the broken records have been assigned to owners, and unpicking it costs more than the import saved. Everything below is cheaper to do beforehand than to undo afterwards.

1. Back up the original export

Keep an untouched copy of the file as it came out of the source system, before anything opens it. Not a copy of your cleaned version — the raw export.

This matters more than it sounds, because several kinds of damage in this guide are irreversible once saved: a stripped leading zero cannot be recovered from the edited file, and neither can a mangled character. The original export is the only thing that lets you start again, and it is the first thing people skip.

2. Decide what makes a record unique

Do this before touching the data, because every later decision depends on it. Your CRM will match incoming rows against existing contacts using one field — usually email — and rows that share it will be merged or rejected.

If the export carries an ID from the source system, bring it across into a custom field — it is the only key that never drifts.

3. Deduplicate before you import, not after

Most CRMs will happily create two contacts from two rows that differ only by a trailing space, and their own merge tools are slower to use than a spreadsheet pass. The essential point is ordering: normalize the key column first, then deduplicate against it, because a duplicate hidden behind formatting is not visible to an exact-match check.

Deduplicate on the helper column, then delete it. Rows that share a key but disagree on the name — "Priya Nair" and "P. Nair" on one address — need a decision rather than a rule, so sort by the key and review those by eye. There will be fewer than you expect and they are usually obvious.

4. Standardize names and email addresses

Trim surrounding whitespace from emails. Lowercasing is common in contact-list cleaning, but do not assume all receiving systems ignore the casing before the @. Check the requirements of any case-sensitive mail systems before normalising those addresses.

The exception is narrow enough to check for directly: a list weighted towards self-hosted or corporate mail domains, holding two contacts whose addresses differ only by case. Those are the rows a lowercase pass would merge into one, and the only ones where it could cost you a real person. If that describes your data, build the same lowercased helper column from step 3, but use it to find those pairs rather than to delete them — sort by it, look at the handful of rows that collide, and decide each one yourself. That is the one place in this checklist where the helper column is a review aid rather than an input to Remove Duplicates. Either way the stored addresses stay exactly as they arrived.

Names need more care than the usual advice suggests. PROPER() handles ordinary personal names and mishandles a predictable set of others:

This is a limit of the rule, not of Excel — any automatic title-casing, in any tool, produces the same three results.

The practical consequence for a customer list is that a single "Name" column mixing people and businesses should not be casing-normalized at all. Split it into person and company columns if the CRM has both, which it almost certainly does, and normalize only the person column. A contact record addressed to "Abc Pty Ltd" is a worse outcome than one left in its original casing.

5. Standardize phone numbers to one format

Pick the format your CRM expects and apply it consistently. If it dials or sends SMS, E.164 — +61412345678 — is the format designed for machines and the least likely to be misread.

Check for numbers a spreadsheet has already damaged before formatting anything. A nine-digit Australian mobile beginning 4 has lost its leading zero and can be repaired; a value in scientific notation like 6.14123E+10 needs the column reformatted as Number with zero decimals to reveal the digits again.

6. Get dates into one format

Signup dates, renewal dates and dates of birth all need to arrive as real dates rather than text, or the CRM will either reject them or store them as strings that no filter can act on.

Check ambiguous slash-format dates against their source. A value such as 13/04/2026 is day-first, but it does not establish the convention for records merged from elsewhere.

The qualifier matters for customer data specifically, because these exports are so often merged — a CRM extract stacked on a webinar list stacked on a spreadsheet someone kept by hand. One decisive value proves the order for its own source, not for a column assembled from three. Look first for signs of more than one source, mixed separators being the most visible, then confirm the order within each group before converting any of it. Applying one group's answer to the whole column silently rewrites the others into dates that are valid, plausible, and wrong.

If no group has a decisive value, check the source system rather than guessing; a wrong guess corrupts roughly two rows in five, and every one of them still looks like a valid date afterwards.

ISO format (YYYY-MM-DD) is the safest thing to hand an importer, because it cannot be misread by a system configured for another locale.

7. Map columns to what the CRM expects

Open the CRM's import template alongside your file and rename your headers to match it exactly before uploading. Field mapping done in the import wizard is where imports go wrong most often, because the mapping screen is the one step people rush.

Splitting a full name on the first space breaks on "Mary Anne Smith" and "van der Berg" — check the tail of the column after splitting.

8. Check for blank required fields

Filter every field the CRM marks required and count the blanks before you upload:

A blank in a required field means the row will be rejected, and most importers reject the row rather than the file — so a partially successful import leaves you reconciling which rows made it. Decide upfront whether those rows should be fixed, dropped, or given a placeholder, and do it before the upload rather than during the cleanup afterwards.

9. Import a sample first

Take the first twenty rows into a test import — a sandbox if you have one, or a small batch you are prepared to delete. Then check the resulting records in the CRM's own interface rather than trusting the success message.

Test a representative set of rows before the full import, including blanks, unusual names, date boundaries and identifiers with leading zeros. A small test can reveal mapping problems, but cannot guarantee the remaining rows are correct.

Common questions

Should I clean in the spreadsheet or in the CRM? The spreadsheet, almost always. Bulk-editing after import means working through the CRM's UI a record at a time, and any mistake is already attached to a live contact with an owner and a history.

What if the same customer has two email addresses? Decide which is primary and put the other in a secondary field if one exists. Do not deduplicate them away — you will lose a working address.

How do I handle contacts with no email? Decide before importing whether they are worth having. If your key is email, they cannot be deduplicated or matched on later imports, so they accumulate as duplicates every time you load a new file.