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.

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 a CRM import

To clean data before a CRM import, validate and normalise the email column first, then remove duplicate contacts by email so your CRM doesn’t reject rows or create duplicates. Sigmera does both in your browser — your customer list is never uploaded to a server, so it stays GDPR-safe by design.

Last updated: September 2026

Clean the email column Remove duplicates

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.
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.
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.

The cleaning steps run in your browser; the later CRM import is a separate upload you control. Check the destination-specific instructions for HubSpot, Zoho CRM, or ActiveCampaign before the final handoff.

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.

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.
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.