Why clean data before importing into a CRM?

CRMs key contacts on email, so invalid addresses get rejected and duplicate emails create or merge records you didn't intend. Cleaning the email column and removing duplicates first means a clean import: no rejected rows, no duplicate contacts, and accurate counts you can trust for reporting and billing.

What does cleaning the email column do?

It lowercases addresses, trims stray spaces, and validates the format, flagging or dropping ones that aren't real email addresses. That stops your CRM from rejecting rows on import and prevents 'JANE@Acme.com ' and 'jane@acme.com' from being treated as two different people.

Is my customer list uploaded to a server?

No. Both the email cleaner and the duplicate remover run entirely inside your web browser using client-side JavaScript. Your customer list is never sent to a server and never leaves your device, so handling customer PII this way is GDPR-safe by design.

How do I prepare a CSV for a Salesforce data import?

Export the list as CSV, clean and lowercase the email column, remove duplicate contacts by email, split any single full-name column into first and last name, and make sure the header row is present with one column per field. Salesforce's Data Import Wizard accepts CSV only and imports up to 50,000 records at a time, so a large list has to be split into batches — and every row you deduplicate first is a row you don't have to fix inside Salesforce afterwards.

Will Salesforce catch the duplicates for me?

Partly, and not in a way you want to rely on. Salesforce's standard contact duplicate rule matches contacts against other contacts and against leads, so it can alert on or block duplicate rows during an import. But a rule set to block turns those rows into import errors you then have to reconcile by hand, and near-duplicates that differ only by casing or a trailing space can slip past matching entirely. Normalising and deduplicating the email column before the import avoids both outcomes.

Which Salesforce data import tool should I use?

Use the Data Import Wizard for a routine customer list: it runs in the browser, covers accounts, contacts, leads, campaign members, and custom objects, and takes up to 50,000 records per run. Reach for Data Loader when the list is bigger than that, when the target object isn't one the wizard supports, or when you want to script the load — with Bulk API 2.0 it handles files of up to 150 million records. Both read CSV, and neither one removes duplicate rows from inside your file, so the cleaning pass is the same either way.

Can I import an Excel file into Salesforce?

Not directly. Both the Data Import Wizard and Data Loader read CSV, so an .xlsx workbook has to be converted first. Save it as CSV, then check the columns Excel is known to mangle on the way out — phone numbers that lost a leading zero or plus sign, ZIP codes stripped to four digits, and long IDs turned into scientific notation. Those are worth fixing in the CSV before the import rather than in Salesforce afterwards.

Email cleaning or deduplication first?

Clean the email column first, then deduplicate. Normalising addresses (lowercasing and trimming) makes near-identical emails identical, so the duplicate remover can then catch every repeat that would otherwise slip through as a separate contact.

What file format does a Salesforce data import need?

A comma-delimited CSV saved in UTF-8. Salesforce's own CSV preparation guidance is explicit that import files must be in UTF-8 format, that any value containing a comma, a newline, or a quote has to be wrapped in double quotes, and that a literal #N/A is how you set a field to null. Saving a workbook as "CSV (Comma delimited)" on a non-English Windows locale is the usual way this goes wrong, because the file comes out in the system code page rather than UTF-8 and every accented name arrives mangled.

Why does Salesforce reject the country or state values in my CSV?

Usually because the org has state and country picklists turned on, and the file mixes formats. Salesforce's guidance is to send full names to the standard State and Country fields, where the integration values match the full names by default, or ISO codes to the StateCode and CountryCode fields, and to map each pair together. Standardize the column to one style before the import.

What date format should a CSV use for a CRM import?

Use yyyy-mm-dd for the whole column. A four-digit year first is unambiguous in every locale, so there is no reading in which 2026-04-03 is the 4th of March. Format the column in the spreadsheet before exporting rather than retyping values, because a CSV stores the characters that were on screen, not the underlying date.

Why did the dates shift after I imported my customer list?

Almost always because the file was ambiguous, not because the CRM is wrong. Microsoft documents that Excel's default date format follows your regional date and time settings and changes when those settings change, so 03/04/2026 means the 3rd of April in the UK and the 4th of March in the US. The CSV records that exact string, and the import reads it under its own assumption. Switching the column to yyyy-mm-dd before the export removes the ambiguity.

Why do my ZIP codes and phone numbers lose their leading zeros?

The spreadsheet did it, not Salesforce. Excel and Sheets read 07030 as the number 7030 and a phone number as a very large number, so the leading zero and the plus sign are gone before the CSV is ever written. Salesforce's CSV guidance calls out preserving leading zeros in numbers such as ZIP codes for exactly this reason. Fix the column in the CSV — format phone numbers to E.164 and pad the postal codes — rather than re-typing them inside Salesforce after the import.

Clean a customer list before an import

Prepare a customer CSV for your CRM, email platform, store or contact app. Normalize matching fields, review duplicates and preserve consent information before using the destination's importer. Sigmera cleans the file in your browser; you choose what to import and update.

Last updated: October 2026

Clean the email column Remove duplicates

Check the destination before importing

Clean the exported file locally, then import it through the receiving application. Sigmera does not connect to these accounts or synchronize records. A clean file still needs the right field mapping, update rules and consent records. Start with a small test and compare accepted and rejected row counts with the original.

ActiveCampaign

Map the email column and the name, phone, tag and custom fields explicitly. Clean the matching field before removing duplicate rows.

Choose subscription status and automation behavior deliberately. API bulk-import limits and requirements are separate from the CSV importer; use the instructions for your chosen route.

ActiveCampaign import documentation

Airtable

Choose whether to create records or merge into existing ones. For an update, select a reliable match field and review blank or repeated keys before importing.

Map into editable fields rather than formula or rollup outputs. Confirm field types and the limits of the import route you are using; importing a new table and using an extension are different workflows.

Airtable import documentation

Constant Contact

Prepare one clearly headed table. Check the email field, hidden characters and which worksheet the selected import method reads.

Preserve opt-out status and permission records. Check supported formats and current limits in the importer, then inspect rejected contacts and a sample of the mapped names and addresses.

Constant Contact import documentation

Google Contacts

Use the Google CSV template when round-tripping contacts. Preserve the header row and the label/value pairs for multiple emails and phone numbers.

Check the current import and account limits before splitting a large file. Review duplicate suggestions after import; do not assume importing merges existing contacts.

Google Contacts import documentation

Klaviyo

Map email and SMS fields separately, keeping international phone numbers as text. Check the required fields for the channel you intend to use.

Preserve subscription consent and suppression status. Review skipped rows and the import result; cleaning an address or phone number does not grant permission to send messages.

Klaviyo import documentation

Mailchimp

Map to audience fields and check whether your selected import method accepts CSV or tab-delimited text. Keep first and last names separate when needed for personalization.

Choose the correct subscription status, keep suppression records and inspect the option to update existing contacts. Check blank-field behavior before updating an audience; do not assume separate audiences deduplicate each other.

Mailchimp import documentation

Pipedrive

Map each column to the correct person, organization or deal field. Verify the required fields for each record type before importing.

Review duplicate detection and the skipped-row report. A CSV duplicate pass cannot decide how a live person, organization and deal should be linked.

Pipedrive import documentation

Salesforce

Choose the Data Import Wizard or Data Loader for the objects and operation you need. Check required fields, record IDs, dates and accepted picklist values.

Import rules can reject or update records differently from a local duplicate check. Test a small file first, review errors and keep a backup before bulk updates.

Salesforce import documentation

Shopify

Use the current customer CSV template and preserve its headers. Check phone formatting and the fields controlling whether existing customer information is updated.

Keep email and SMS marketing consent separate and accurate. Review duplicate emails or phone numbers and the overwrite options before importing; never infer consent from the presence of a contact detail.

Shopify import documentation

Zoho CRM

Select the destination module first, then check its mandatory fields and field mapping. Requirements can differ between leads, contacts and custom modules.

Check the duplicate-handling option before an update. Preserve identifiers as text, use a consistent encoding and review the rejected-row report instead of assuming every record was accepted.

Zoho CRM import documentation

Prepare a recipient list for a mail merge

Use one header row and one column per merge field. Split names only when the greeting needs separate fields, preserving compound names for review. Store postal codes and other identifiers as text. Check blank greeting fields, repeated recipients and the selected worksheet before connecting a Word template or an email merge tool.

Preview several merged outputs, including missing values and unusual names. A merge engine inserts the values supplied; it does not verify postal deliverability, email consent or whether two recipients are the same person. Sigmera prepares the file, not the messages.

Keep a repeatable customer-list workflow

For a small team, keep the original export, agree on matching fields, normalize them, review duplicates, then test the import. Assign one person to check rejected records and keep suppression or consent information with the customer record. Cleaning the CSV does not synchronize your other systems.

Once the list is reliable, decide which repeated business steps are worth automating and where a person should approve the result. Read the practical guide to AI for business.

A dirty list poisons your CRM on day one

The list you import into a new CRM becomes its foundation — and whatever mess goes in stays in. Invalid emails get rejected mid- import, so you lose contacts and have to reconcile what made it. Addresses with stray capitals and spaces sneak in as “different” people, then the same customer gets two records, two invoices, and two marketing emails. Your reported customer count is wrong from the start, and untangling duplicates inside a live CRM is painful. Cleaning and deduplicating the email column before the import keeps the database trustworthy from the very first row.

Step by step: prep your list for the CRM

1. Export your customer list to CSV. Pull it from your spreadsheet, billing system, or old CRM as one CSV file.
2. Clean and validate the email column. Open the email-column cleaner to lowercase, trim, and validate every address, dropping ones the CRM would reject.
3. Remove duplicate contacts. Open the duplicate remover and match on the cleaned email column so each customer appears once.
4. Preview, download, and import. Review the clean preview, download the CSV, and map columns in your CRM’s import wizard.

The seven-point CRM import checklist

Work through the file before importing it. Normalize the identifier column before deduplicating: a trailing space should not turn one customer into two records. Keep the original export so you can review the changes alongside the cleaned file.

Clean the email column. Trim spaces, normalize case, and review malformed addresses with the email cleaner. Syntax checks do not prove that a mailbox exists.
Remove duplicate rows. Choose the identifier your CRM uses, then decide which copy to keep. Use the CSV duplicate remover or follow the Excel deduplication guide. Different email addresses can still belong to the same person and need manual review.
Standardize phone numbers. Check the country before using the phone-number formatter. Review the E.164 format rules when the destination requires international numbers.
Match name columns. Split full names into the fields the importer expects. Review compound surnames and middle names. If the destination expects a combined field, join the source columns instead. Working in a workbook? Combining two columns in Excel covers the formula route and the date and blank-cell traps.
Fix headers and empty rows. Use one non-empty, unique header per column. Map it to a CRM property and remove empty rows before importing. In a workbook, clear the blank rows in Excel in one pass rather than one row at a time.
Check required fields and record IDs. Follow your CRM's template. For updates, preserve its record identifiers and choose the intended update mode so the import does not create new records by mistake.
Confirm encoding and file shape. Save a UTF-8 CSV and review accented names, quoted values, and leading zeros. Convert the relevant workbook sheet to CSV if necessary, then preview the result.

Clean before automations start

An imported contact can enter a welcome sequence, receive an owner, or collect activity history. Fixing it later can mean reconciling more than a spreadsheet row. Review your CRM's import settings and automations before submitting the cleaned file. See how duplicate records affect a CRM. If someone has to sign the list off first, a PDF copy of the cleaned CSV lets them read every row without touching the file you will import.

The cleaning steps run in your browser; the later CRM import is a separate upload you control. The checklist above is what every destination asks for; where a platform adds its own rules, read them in its own documentation before the final handoff. HubSpot is the one walked through here, in cleaning a CSV before a HubSpot import.

What a Salesforce data import expects from your file

Salesforce is the strictest of the common destinations, so it is worth naming its rules explicitly. The Data Import Wizard reads CSV only and imports up to 50,000 records at a time (Salesforce Help), so a bigger list has to be split into batches — and every duplicate you leave in the file eats one of those 50,000 slots.

Salesforce can also police duplicates on its own side: the standard contact duplicate rule matches contacts against other contacts and against leads (Salesforce Help). That sounds like a safety net, but a rule set to block converts each duplicate into an import error you have to reconcile row by row, and a rule set to alert lets them through. Neither is as cheap as collapsing “JANE@Acme.com” and “jane@acme.com” into one row before the file is ever read.

So the order below is the whole trick: normalise the email column, deduplicate on it, then import. It applies to Salesforce, HubSpot, Pipedrive, and every other CRM that keys contacts on email — see how to clean a CSV before a CRM import in the checklist above, including phone numbers and name columns.

Data Import Wizard or Data Loader for your Salesforce data import?

Salesforce ships two import paths, and the one you pick changes what your file has to look like. The Data Import Wizard runs in the browser and handles the common objects — accounts, contacts, leads, solutions, campaign members, and custom objects — up to 50,000 records per run (Salesforce Help). Data Loader is a desktop client that covers every object and, used with Bulk API 2.0, supports files of up to 150 million records (Salesforce Data Loader Guide).

	Data Import Wizard	Data Loader
Where it runs	In Setup, in your browser	Installed desktop client
Records per run	Up to 50,000	Up to 150 million with Bulk API 2.0
File format it reads	CSV	CSV (or a database connection)
Deduplicates rows inside your file	No	No

Note the last row, because it is the one that catches people out: neither tool looks for repeats within the file you hand it. Salesforce’s duplicate rules compare an incoming record against records already in the org, so forty copies of the same contact in one CSV import as forty contacts regardless of which path you use. That work has to happen in the file first — which is what the CSV duplicate remover is for.

Both paths read CSV, not .xlsx. If your customer list is currently a workbook, convert the Excel file to CSV before you start, and check the phone column afterwards — a spreadsheet will happily have eaten the leading zeros and plus signs that E.164 formatting puts back. The anatomy of an E.164 phone number explains what a correct value looks like, so you can spot a mangled column before the CRM does.

Four file rules that break a Salesforce data import

Most failed imports are not a Salesforce problem — they are a spreadsheet problem that Salesforce is the first thing to notice. Salesforce’s own CSV preparation guidance (Salesforce Help) names the four that catch a customer list most often.

The file has to be UTF-8. Salesforce requires import files in UTF-8 format. A workbook saved as “CSV (Comma delimited)” on a non-English Windows locale comes out in the system code page instead, and every accented name — Müller, Nováková, Öztürk — arrives corrupted. Exporting through the Excel-to-CSV converter writes UTF-8 directly, in the browser.
Leading zeros have to survive. The guidance calls out preserving leading zeros in numbers such as ZIP codes. A spreadsheet reads 07030 as 7030 and a phone number as a very large number, so the zero and the plus sign are lost before the CSV exists. Rewriting the phone column to E.164 puts the country code back on every row.
Email addresses have to be real ones. Salesforce expects addresses that comply with RFC 5321, so a trailing space or a missing TLD is a rejected row rather than a warning. That is the same pass the email-column cleaner makes before you deduplicate.
Every required field needs a value. You must include all required fields to create a record, and an unmapped column is not imported at all. A single “Full Name” column is the usual casualty, because Salesforce stores first and last name separately — split the name column into two first, or follow the walkthrough for splitting names in Excel if you would rather do it in the workbook. Blanks that should be empty rather than skipped are written as a literal #N/A.

All four are cheaper to fix in the file than in the org. Every one of these passes runs in your browser — the customer list is read into memory, rewritten, and downloaded again without ever being sent anywhere.

Country and state values on a picklist-enabled org

Many Salesforce orgs switch the address State and Country fields from free text to picklists. On those orgs the address columns stop being forgiving: Salesforce’s own article on import errors with state and country picklists enabled warns that importing or updating records “may result in data validation errors” when the values in the file do not fit. A customer list that mixes UK, U.K., Great Britain, and United Kingdom in one column is exactly that file.

The same article sets out what the file should look like:

Pick one style per column and keep it. Full names go to the standard State and Country fields, where the integration values “by default, match the State/Country full names”. Two-letter ISO codes go to the code fields (StateCode and CountryCode) instead.
Map state and country as a pair. Map both name fields together or both code fields together — a name field for one and a code field for the other is the mismatch the article is written about.
Check the default country before you import. Salesforce recommends setting the Default Country to --None-- in the picklist setup, so a default does not quietly fill in rows that arrived without a country.

Normalizing the column is a short list of whole-value swaps, and it belongs in the file rather than in the org. The column-scoped find and replace has a whole-cell option, so replacing UK with United Kingdom changes only cells that are exactly UK and leaves every other value in the column alone. Run one swap per variant, then check the column again before you map it.

The date column is the one nobody checks

Emails and duplicates get the attention because a bad one throws an error. Dates are worse precisely because they do not: a renewal date, a signup date, a contract end date all import cleanly and land on the wrong day. Nothing in the CRM flags it, and by the time a renewal reminder fires three weeks early, the file is long gone.

The damage is done in the spreadsheet, before the CSV exists. Microsoft’s date formatting reference states that Excel’s default date format “is based on the regional date and time settings that are specified in Control Panel, and changes when you adjust those settings”. So the same cell reads as one date in London and another in Chicago. A CSV is plain text: it stores the characters that were on screen, not the date behind them. Export 03/04/2026 and the file contains the string 03/04/2026 — the 3rd of April or the 4th of March depending on who reads it next.

Two rules remove the ambiguity for good:

Write dates as yyyy-mm-dd before you export. A four-digit year first is unambiguous in every locale — there is no reading in which 2026-04-03 is the 4th of March. Format the column in the workbook, then save the CSV; do not retype the values by hand.
Make the whole column one format. Mixed columns are the common case in a list assembled from several exports — some rows 03/04/2026, some 3 Apr 2026, some a bare serial number like 46115 where the cell lost its date formatting. An import maps the column one way, so whichever shape is in the minority is the one that lands wrong.

Sorting the column in the spreadsheet is the fastest check: real dates sort chronologically, and anything Excel is treating as text clumps at one end where you can see it. To rewrite a separator across the column afterwards, the column-scoped find and replace edits one column at a time, so a swap meant for dates leaves the phone numbers alone. And if the list is still a workbook, take the date column through the Excel to CSV export only once it reads yyyy-mm-dd — the conversion copies the cell through as-is, so whatever is on screen is what the CRM gets.

Sources

Before and after

Field	Messy list	CRM-ready
Email casing	JANE@Acme.com	jane@acme.com
Invalid emails	jane@acme (no TLD)	Flagged / removed
Duplicate contacts	2 rows, same email	1 row
Import result	Rejected rows / duplicates	Clean, accurate import

🔒 Your customer list stays on your device. Both tools run in your browser’s memory — no file is uploaded, which keeps customer PII GDPR-safe by design.

Frequently asked questions

Why clean data before importing into a CRM?: CRMs key contacts on email, so invalid addresses get rejected and duplicate emails create or merge records you didn't intend. Cleaning the email column and removing duplicates first means a clean import: no rejected rows, no duplicate contacts, and accurate counts you can trust for reporting and billing.
What does cleaning the email column do?: It lowercases addresses, trims stray spaces, and validates the format, flagging or dropping ones that aren't real email addresses. That stops your CRM from rejecting rows on import and prevents 'JANE@Acme.com ' and 'jane@acme.com' from being treated as two different people.
Is my customer list uploaded to a server?: No. Both the email cleaner and the duplicate remover run entirely inside your web browser using client-side JavaScript. Your customer list is never sent to a server and never leaves your device, so handling customer PII this way is GDPR-safe by design.
How do I prepare a CSV for a Salesforce data import?: Export the list as CSV, clean and lowercase the email column, remove duplicate contacts by email, split any single full-name column into first and last name, and make sure the header row is present with one column per field. Salesforce's Data Import Wizard accepts CSV only and imports up to 50,000 records at a time, so a large list has to be split into batches — and every row you deduplicate first is a row you don't have to fix inside Salesforce afterwards.
Will Salesforce catch the duplicates for me?: Partly, and not in a way you want to rely on. Salesforce's standard contact duplicate rule matches contacts against other contacts and against leads, so it can alert on or block duplicate rows during an import. But a rule set to block turns those rows into import errors you then have to reconcile by hand, and near-duplicates that differ only by casing or a trailing space can slip past matching entirely. Normalising and deduplicating the email column before the import avoids both outcomes.
Which Salesforce data import tool should I use?: Use the Data Import Wizard for a routine customer list: it runs in the browser, covers accounts, contacts, leads, campaign members, and custom objects, and takes up to 50,000 records per run. Reach for Data Loader when the list is bigger than that, when the target object isn't one the wizard supports, or when you want to script the load — with Bulk API 2.0 it handles files of up to 150 million records. Both read CSV, and neither one removes duplicate rows from inside your file, so the cleaning pass is the same either way.
Can I import an Excel file into Salesforce?: Not directly. Both the Data Import Wizard and Data Loader read CSV, so an .xlsx workbook has to be converted first. Save it as CSV, then check the columns Excel is known to mangle on the way out — phone numbers that lost a leading zero or plus sign, ZIP codes stripped to four digits, and long IDs turned into scientific notation. Those are worth fixing in the CSV before the import rather than in Salesforce afterwards.
Email cleaning or deduplication first?: Clean the email column first, then deduplicate. Normalising addresses (lowercasing and trimming) makes near-identical emails identical, so the duplicate remover can then catch every repeat that would otherwise slip through as a separate contact.
What file format does a Salesforce data import need?: A comma-delimited CSV saved in UTF-8. Salesforce's own CSV preparation guidance is explicit that import files must be in UTF-8 format, that any value containing a comma, a newline, or a quote has to be wrapped in double quotes, and that a literal #N/A is how you set a field to null. Saving a workbook as "CSV (Comma delimited)" on a non-English Windows locale is the usual way this goes wrong, because the file comes out in the system code page rather than UTF-8 and every accented name arrives mangled.
Why does Salesforce reject the country or state values in my CSV?: Usually because the org has state and country picklists turned on, and the file mixes formats. Salesforce's guidance is to send full names to the standard State and Country fields, where the integration values match the full names by default, or ISO codes to the StateCode and CountryCode fields, and to map each pair together. Standardize the column to one style before the import.
What date format should a CSV use for a CRM import?: Use yyyy-mm-dd for the whole column. A four-digit year first is unambiguous in every locale, so there is no reading in which 2026-04-03 is the 4th of March. Format the column in the spreadsheet before exporting rather than retyping values, because a CSV stores the characters that were on screen, not the underlying date.
Why did the dates shift after I imported my customer list?: Almost always because the file was ambiguous, not because the CRM is wrong. Microsoft documents that Excel's default date format follows your regional date and time settings and changes when those settings change, so 03/04/2026 means the 3rd of April in the UK and the 4th of March in the US. The CSV records that exact string, and the import reads it under its own assumption. Switching the column to yyyy-mm-dd before the export removes the ambiguity.
Why do my ZIP codes and phone numbers lose their leading zeros?: The spreadsheet did it, not Salesforce. Excel and Sheets read 07030 as the number 7030 and a phone number as a very large number, so the leading zero and the plus sign are gone before the CSV is ever written. Salesforce's CSV guidance calls out preserving leading zeros in numbers such as ZIP codes for exactly this reason. Fix the column in the CSV — format phone numbers to E.164 and pad the postal codes — rather than re-typing them inside Salesforce after the import.