How to clean customer data before a CRM import

Taking a copy, one row per person and a fixed set of columns, one format, duplicates, empty values, source and permission, a twenty-row test and the rules that keep the list clean.

· 13 min readAI

Short answer

Before importing customer data, take a copy, reduce every row to one person or company, put names, phones, dates and countries in one format, merge the duplicates, add source and permission columns and test with 20 rows.

Picture this: the salesperson at Örnek Firma, an invented company, merges the business-card list collected at a trade fair, the enquiries from the website form and accounts’ customer file into one spreadsheet and uploads it to the new CRM. The next morning “Örnek Müşteri” sits in the CRM as three separate records: one with the phone number “0555 000 00 00”, another with “+90 555 000 00 00”, the third with none at all. Three records means three separate reminders and a team that does not know who wrote to whom. The answer to how to clean customer data lies before the upload, in the spreadsheet, where fixing things is still easy.

Every name, phone number and address in this article is invented; for the domain we use example.com. The list you are cleaning may be going into a CRM or into an email tool, and the steps are the same. As examples we used the help pages of Google Sheets (for the spreadsheet) and HubSpot (for the CRM); menu names differ in other tools, but the job stays the same. We explained what a CRM does and how to choose one in what a CRM is and how to choose one; here we assume the choice is made and concentrate on the data to be moved.

The eight steps run in this order, and the rules that keep the list clean come at the end:

Eight steps before the import: copy, row and column layout, one format, duplicates, empty and wrong values, splitting one cell, source and permission, a twenty-row test; then the rules 1 Take a copy 2 Row and column layout 3 One format per field 4 Merge the duplicates 5 Empty and wrong values 6 Split one cell 7 Source and permission 8 Test with twenty rows Then: required fields, one owner, pick lists Eight steps before the import: copy, row and column layout, one format, duplicates, empty and wrong values, splitting one cell, source and permission, a twenty-row test; then the rules 1 Take a copy 2 Row and column layout 3 One format per field 4 Merge the duplicates 5 Empty and wrong values 6 Split one cell 7 Source and permission 8 Test with twenty rows Then: required fields, one owner, pick lists
Figure: The eight steps before the import are, in order, a copy, row and column layout, one format, duplicates, empty and wrong values, splitting one cell, source and permission, and a twenty-row test; the rules that keep the list clean follow.

Step 1: Take a copy first

Duplicate the file with “Save as”, add the date to its name (for example customers-raw-2026-11-14) and never touch the original again. Do the cleaning in the copy. If you have several sources, leave each one in its own tab in its raw state, and open the merged working tab separately. That way, when you delete a record by mistake, you can go back to where it came from. Do not share a file containing customer details through a public link; that is our advice.

Step 2: One row per person, a fixed set of columns

Let each row describe a single person or a single company. Keep people and companies in separate tabs, with the company name as a column on the person’s row. Split a row that holds two people, such as “Ayşe Örnek / Mehmet Örnek”, in two.

Fix the columns as well. Our suggested example list: first name, last name, company, email, phone, country, source, permission, owner, date last updated. Drop any column that has no counterpart in your own CRM. HubSpot’s help page requires that, in the file you import, every column header corresponds to a field in the CRM and that the file contains only one sheet. It also requires the file to be saved as UTF-8 if it contains foreign-language characters (such as ö, ş, İ). That is why you will export the people and company tabs as separate files.

Step 3: One format for each field

Five spellings of the same piece of information break every later search, filter and duplicate check. Choose one spelling for each field and convert every row to it. The table below shows, with invented data, the raw forms of four fields and the single format we chose:

Field Raw forms (invented) One format (our choice)
Full name ÖRNEK MÜŞTERİ, örnek müşteri Örnek Müşteri
Phone 0555 000 00 00, (555) 000-00-00 +905550000000
Date 3.11.2026, 2026/11/03, 3 Kas 26 2026-11-03
Country TR, Turkiye, Turkey Türkiye

For phone numbers, HubSpot’s documentation says that to have a number formatted automatically by country code you write +[country code][number]; if there is an extension, ext and the number are added at the end. For dates, the same document says it accepts day-month-year, month-day-year and year-month-day orders. Whether “03.11.2026” means the third of November or the eleventh of March is, in our view, something only you can know; one reading has to be chosen. Our choice is year-month-day: it has only one way of being read.

In email addresses, remove leading and trailing spaces and make the letters lower case. In Google Sheets there are two ways: the menu that the help page describes, Data, Data cleanup, Trim whitespace, or this formula in an empty column:

=LOWER(TRIM(D2))

The same page also says that a non-breaking space (a special invisible space) is not trimmed; suspect this if, after cleaning, two addresses still fail to match. If you had the capitalisation in names corrected automatically, check the Turkish letters İ and ı by eye; that is our recommendation.

Step 4: Find the duplicates and decide which record survives

Find first, delete afterwards. The most reliable key is the email address; on the person rows, put this helper formula next to the email column (assuming the email is in column D):

=COUNTIF(D:D, D2)

If your spreadsheet’s locale is set to Türkiye, the parts of a formula are separated by a semicolon; if the comma gives an error, write =COUNTIF(D:D; D2). Rows with a result above 1 are duplicates; filter them and look at them side by side. Google Sheets also has a “Remove duplicates” tool in the Data, Data cleanup menu, and according to its help page it counts cells with identical values but different letter case, formatting or formulas as duplicates. So the tool finds only values that are equal to each other: for it, “Ornek Musteri” and “Örnek Müşteri” are different people. That is why we converted to one format first. Do not leave the choice of which row stays to the tool either; make it yourself.

Put a written rule on which record survives. Our recommendation is to look in this order:

  1. The most recently updated record stays.
  2. If the dates are equal, the one with its source and permission filled in stays.
  3. If that is equal too, the fuller one stays.

Copy the missing details from the record that will go into the one that stays; instead of deleting the others, move them to a tab called “Duplicates”. If two records disagree on permission, write “no” or “unknown” on the one that stays.

The same logic applies at import: HubSpot’s documentation asks for a unique identifier in the file in order to update existing records and avoid duplicates; for contacts the email can serve as that identifier, for companies the company domain name. So an email on every person row and a domain on every company row is also a precondition of the duplicate check.

Step 5: Empty and obviously wrong values

First decide which fields cannot be empty. Our recommendation: for a person, the name and either the email or the phone; for a company, the domain. Do not delete rows that fail this test; move them to a tab called “To complete”. Use a filter to look at these: emails with no @ or with a space in them, phone numbers made only of zeros, one-letter names, very old or future dates. These go to a “To correct” tab. Do not fill in what you do not know by guessing; an empty field is better than a wrong one.

Step 6: Split what was typed into one cell

In hand-typed spreadsheets one cell carries several pieces of information: “Ayşe Nur Örnek” in one cell, “0555 000 00 00 ext 12” in one cell, “Örnek Müşteri Ltd., 1 Örnek Street” in one cell. According to the Google Sheets help page, the Data, Split text to columns menu spreads text written with a clear separator, such as a comma, over several columns; you can choose the separator yourself.

It matters to know the tool’s limits: in first and last names, middle names or multi-part surnames get mixed up, so check the result by eye. Move the extension out of the phone column into a column of its own, or use the ext form from step 3. If you do not need to split the address into street, district and postcode, leave it in one column; split only the information you want to filter or report on separately in the CRM.

Step 7: Source and permission columns

Every record should answer two questions: where did it come from, and what do we have that lets us contact this person? In the source column write fixed values such as “trade fair list”, “website form”, “accounts file”, “old CRM”, and add the date it was entered. Three columns are enough for permission: email permission (yes, no, unknown), the date of the permission, and where it was obtained (which form or file).

These columns are not legal advice. How permission has to be obtained, how long a record may be kept and what to do if a deletion request arrives depend on your situation. Settle them with your lawyer and read the distinctions in email and SMS consent in Turkey. Our recommendation is not to put records whose permission is “unknown” on the marketing email list, and to keep them apart with a separate label.

Step 8: Test with twenty rows

Do not upload the whole list in one go. Pick 20 rows from the spreadsheet; not from the top, but so that they cover the different cases. Those cases are a name with Turkish characters, a phone number with an extension, a record with no phone, a foreign country, a duplicate pair and a record whose permission is “unknown”. Import those 20 rows into the CRM and check these:

  • Do the Turkish letters look right?
  • Have phone numbers and dates arrived in the right format, with no day and month swapped in the dates?
  • Are the owner, source and permission fields filled in?
  • Did the duplicate pair open two records, or did they merge?

If anything is wrong, delete the test records, correct the spreadsheet or the column mapping and try again. When the test passes, import the rest, then compare the number of rows in the spreadsheet with the number of records in the CRM. Put the old spreadsheet in a read-only archive; after moving day, do not work in two places at once. The remaining steps of the move are in our CRM article.

Rules that keep it clean

An import cleans once; rules keep it clean. The five rules we recommend:

  • Required fields. Do not let the name, the email or phone, the source and the owner stay empty; make them required in the form and in the CRM’s settings.
  • A list instead of free text. Let country, source and permission status be picked from a list. According to the Google Sheets help page, a value that is not in a dropdown list is rejected if someone enters it; you can turn that into a warning if you prefer. HubSpot’s documentation likewise asks that values imported into list-type fields match the defined options.
  • One owner. Give every record a single person responsible. HubSpot’s documentation explains that an owner can be assigned by adding an owner column to the import file and writing the user’s name or email on each row; it recommends the email if several users have the same name.
  • One way in. Let a record from a form open automatically, not by hand; then the source and format always arrive the same. For example flows, see business process automation examples. Detmore’s service page describes an automation agency as the team that turns the data-moving done by hand every week between forms, CRM, accounting and email into flows that run on their own between the systems.
  • Ten minutes a month. Set up a view that filters duplicates, ownerless records and records whose permission is “unknown”, and write down who looks at it. For cleaning addresses that do not click in an email list, see how to grow an email list.

Common mistakes

  • Deleting the old file straight away. If a problem turns up in the first weeks after the import, the raw copy is where you go back to; do not delete it.
  • Not writing the decisions down. If which record survives and which field is required stay in your head, the next person will clean in a different way. Put the rules on one page.
  • Moving every record “in case it is needed”. Whether to move people who have not replied in years is a separate decision; ask your lawyer how long they may be kept.
  • Carrying on writing to the old spreadsheet after the import. Two lists drift apart within weeks, and a new cleaning job is needed.

Common questions

How long does cleaning customer data take?

The time depends less on the number of rows than on the number of decisions to be made: which record stays, which field is required, which format is chosen. If you make the decisions on the first 20 rows, the rest is mostly application.

Should I use Excel or Google Sheets?

Either will do; the eight steps are the same. Only the menu names and the formula spelling change. Whichever you choose, when you export, check the file type the CRM wants and the UTF-8 encoding.

Should I move old contacts who do not reply?

First look at where the record came from and what its permission status is. How long it may be kept is a legal question; ask your lawyer. Our recommendation is to keep these records in a separate tab with a separate label until you have decided.

One thing you can do alone today: make a copy of your current customer file and convert the first 20 rows by hand to the single formats in the table in step 3. Then note how many different spellings of the same piece of information you find. We describe the way we work, starting from a process inventory, on the automation and AI service page.

Sources

This article is general information, not legal, financial or technical advice. Platforms, their rules and the law change over time, so some details may have changed since 14 November 2026 or contain small errors. Before you make a decision for your business, check the current official sources or write to us.

Tell us about
your project.

Fill in the form or write to us directly, and we will agree on a day to talk.

Your first meeting is with Deniz Sezer, founder of Detmore.

First meeting free · Reply within one working day · Written proposal in 2 to 3 days · NDA ready

What do you need? pick as many as you like

Name, email and phone are required.

Contact

We reply within one working day.