Migrating Data from Excel into a CRM: The Order That Prevents Duplicates

The expensive mistake in a data migration is importing first and cleaning afterwards. After the load, a duplicate is not a repeated row in a file - it is two live records that reps are already working against, and merging them costs several times more than cleaning would have.

The working order, seven steps

1. Export everything into one file and back it up separately before touching anything.
2. Normalise phone numbers into one format - this is the key duplicate detection will rest on.
3. Identify duplicates by phone, by company number, and by normalised name.
4. Map fields from your columns to the system's fields, in writing.
5. Load 20 rows and check them on screen, in an export and in search.
6. Load everything and record how many rows were accepted against how many were sent.
7. Check a sample of 20 random records after the full load.

Step 6 is the one that gets skipped and paid for: a 4,000-row file of which 3,860 loaded is often reported as a success, and nobody knows which 140 are missing.

Why phone is the right key in Israel

Name is a poor key: the same business appears as "Avi Renovations", "Avi Renovations Ltd" and "A. Renovations". Email exists on only some records. A company number is excellent but is rarely collected. Phone, by contrast, is nearly always present - and after normalisation it compares well.

Normalising an Israeli phone number means: strip spaces and hyphens, drop the international prefix and restore the leading zero, and ignore extensions. After that, 050-123-4567, +972501234567 and 0501234567 become the same value, and only then can duplicates genuinely be counted.

Note the exception: in businesses with several branches or several roles, the same number can legitimately appear on multiple records. An identical phone number is therefore a merge candidate, not a merge instruction.

What not to migrate

• Customers untouched for three years. Leave them in an archive file.
• Columns filled in on less than a third of rows.
• Personal notes where it is unclear who wrote them and when.
• Historic prices that are no longer relevant.
• Calculated columns the system will compute itself.

Every row that does not migrate is a row you need not clean, map or verify. It is the cheapest way to shorten a migration, and it almost always meets emotional resistance - "we might need it". The practical compromise is an accessible archive file rather than a load into the live system.

How do you find duplicates without special tools?

In Excel or Google Sheets, in four steps: build a normalised key column; sort by it; flag rows where the key matches the previous row; and review those by hand. For files up to a few thousand rows this works well and takes hours, not days.

More important than the tool is the rule: merging is a human decision. Automatic merging on similar names destroys information about genuinely different customers who happen to sound alike, and that is a mistake that is hard to detect and impossible to reverse. Flag and decide manually - especially in Israel, where business names repeat.

After the load: three things on day one

• Block new duplicates: configure a phone check when a record is created.
• Freeze the old file as read-only, so nobody keeps updating in two places.
• Test search by half a phone number and by a misspelled name.

The first is the only one that prevents the problem returning. Without it you will have the identical problem in the new system within two months, and the full explanation is in duplicate customer records between systems.

Field mapping: where it actually breaks

Mapping looks like technical work and is in fact a sequence of business decisions. Four collisions recur in nearly every file:

• One column holding two things. "Name and city" in a single cell, or "phone / contact". Split before loading, or both fields end up wrong.
• A free-text field where the system expects a list. A status written in eleven variants - "active", "Active ", "in progress", "open". Convert to a closed list first, and decide what happens to values that do not match.
• Dates in mixed formats. A typical Israeli file contains both 01/02/2026 and 2026-02-01, and sometimes plain text. Excel "helpfully" converts some of them on its own, which is a source of silent errors.
• A field with no home in the system. Here the decision is real: add a custom field, compress it into notes, or drop it. Compressing into notes means you will never be able to search on it.

Write the mapping in a simple table - column in file, field in system, what happens to odd values - and keep it. You will need it again for the second load, and there is nearly always a second load.

What to do when the file comes from several sources

The common situation is three files: a customer list from bookkeeping, a lead list from marketing, and one rep's personal list. The temptation is to merge them into one file and load. What works better is loading in order of reliability: the bookkeeping file first, where data is verified against documents, then the rest, with each additional file only filling in missing records rather than overwriting.

The decision that must be made explicitly is what wins in a conflict: the address from bookkeeping, or the address a rep updated most recently. There is no single right answer, but there is one answer that stays consistent - and without it nobody will know in six months why that particular address is showing.

How do you verify the load worked?

Not by the "import completed successfully" message. Three checks that take fifteen minutes and catch nearly every problem:

• The count. Records in the system against rows in the file, minus whatever you decided not to load. A gap - even of one per cent - needs an explanation.
• The sample. Twenty random records, checking on each that the name, the phone number and the field that matters most to the business look right.
• Search. Look up five customers you know - by partial name, by phone without the leading zero, and by a misspelled name. If search cannot find them, reps will create new records.

The third check is the least performed and has the largest effect on what happens in the first month.

Sources

• Fireberry - import and data
• Kala CRM - receiving enquiries and customers from multiple sources