CSV and imports
How to Clean a CSV File Before Importing It Into a CRM
Before any import, fix seven things: one header row with the exact field names your tool expects, UTF-8 encoding, trimmed lowercase emails, phone numbers in one international format, dates in one unambiguous format such as 2026-10-09, no duplicates on the key field (usually email) and no blank or merged cells in required columns. Then import 10 rows as a test before the full file.
A bad import is slow to undo. A CRM or email tool will happily create 4,000 duplicate contacts, scramble every accented name or reject half the rows with an error you only see at the end. Ten minutes of cleaning first avoids all of it. Work on a copy and keep the original file untouched.
The checklist at a glance
| Check | Why imports fail without it |
|---|---|
| One header row, names match your tool | Columns are skipped or mapped wrongly |
| UTF-8 encoding | Accents become garbled characters |
| Emails trimmed, lowercase, valid | Rejected rows, bounces |
| One phone format | Rejected or unusable numbers |
| One date format | Dates swap day and month, or fail |
| No duplicates on the key field | Duplicate contacts |
| No blank or merged cells in required columns | Rows rejected |
Step 1: one header row, clean field names
Delete title rows, notes and totals above and below the table. Keep a single header row. Rename headers to match the field names in your tool (for example Email, First Name, Phone), since most tools match columns by name. Remove empty columns and blank rows.
Step 2: save as UTF-8
In Excel choose File, Save As, and pick CSV UTF-8 (Comma delimited). The plain "CSV (Comma delimited)" option uses an older encoding that damages accents and non-Latin letters. See fixing garbled characters and broken CSV imports if the file already looks wrong.
Step 3: emails
Remove stray spaces and lowercase everything:
=LOWER(TRIM(A2))A quick syntax check for each address (it checks shape, not whether the mailbox exists):
=AND(ISNUMBER(FIND("@",A2)),ISNUMBER(FIND(".",A2,FIND("@",A2))),ISERROR(FIND(" ",A2)))Fix provider typos such as gmial.com first: see fixing email typos in Excel, or paste the column into the free email checker, which works in your browser.
Step 4: names and phones
If your tool wants separate first and last name columns, split them: splitting full names in Excel. Bring phone numbers into one format, preferably international with the country code: fixing phone numbers in Excel. Keep ZIP codes and IDs as text so their leading zeros survive: keeping leading zeros.
Step 5: dates
Different tools read 03/04/2026 differently (3 April or 4 March). Convert real dates to year-month-day, which has only one reading:
=TEXT(A2,"yyyy-mm-dd")If a date is stored as text, convert it first: fixing dates in Excel and CSV.
Step 6: duplicates
Decide which column identifies a person, usually email. Flag repeats:
=COUNTIF($A$2:$A$5000,A2)>1Review them before deleting: two rows with the same email may hold different phone numbers worth keeping. See removing duplicates without losing data and finding and highlighting duplicates.
Step 7: test with 10 rows
Copy 10 rows, including your three messiest ones, into a new CSV and import only that. Open the imported records and check names, accents, phone numbers, dates and the mapping of every column. Only then import the rest.
Tip: keep a column called Source or Imported on with today's date. If something goes wrong you can find and remove exactly the batch you added.
Warning: check your tool's own rules for required fields, the date format it expects and how it treats duplicates. They differ between products and change over time, so read its current import help page.
A whole file at once
DecodingPro trims text, fixes mistyped email domains, standardises phone numbers and dates, flags duplicates and lists every change, so you can review the result before importing it.
You start with 100 free credits. Every change is logged, and anything unclear is flagged, never guessed.
Fix a spreadsheetQuestions people ask
Should I delete duplicate rows or leave them to the CRM?
Clean them first. Many tools only match on one field such as email, so rows with a different spelling of the same address still become two contacts.
Why do accented names turn into strange characters after import?
The file was saved in an older encoding. Save it again as CSV UTF-8 and import that version.
Is it safe to import my whole list straight away?
Not the first time. Import 10 rows as a test, check them, then import the rest.