How to prepare a CSV before importing to a CRM

Preparing a CSV for a CRM import means fixing five things in the file first: remove duplicates, give every column one header and one kind of data, put dates and phone numbers in one consistent format, decide which column is the unique key, then import ten rows as a test. Cleaning in the file takes minutes. Cleaning after the import is the slowest work in a CRM.

Deduplicate before, not after

A duplicate in a spreadsheet is one delete. After import it is a merge that has to reconcile two sets of notes, emails and deals. Sort by email address and remove exact repeats: email is the only reliably unique field, so it is also the column to fix first if rows are missing one.

One header row, one field per column

Delete the title row, merged cells and blank spacer rows. Importers expect row one to be headers and every row after it to be data.

Split anything holding two facts. "Jane Smith" becomes first name and last name, because you cannot personalise an email from a full-name column without guessing where the first name ends.

Name headers as the thing they contain. A column called "info" is a decision postponed to someone who will not remember.

Formats: dates, phones, currency

Dates are the reliable disaster. 03/04/2026 is two different days depending on who reads it, and a spreadsheet will reformat the column when you open the file. Use YYYY-MM-DD.

Phone numbers need the country code and no decoration: +44 20 7946 0000, with the column formatted as text first, or a leading zero disappears silently. Currency should be a plain number.

Decide the unique key, then test ten rows

Decide which column identifies a record before you start, because the importer will ask. Email for people, domain for companies. Without a key, a second import creates a full second set of everything.

Then import ten rows, look at them in the CRM, and only then run the rest. Every mapping mistake is visible in ten rows and expensive in ten thousand. Check what your tool does with an existing match: create, skip or update. Getting that wrong is how one file becomes two thousand duplicates and a cleanup pass nobody needed.

Keep the file

Save the file you imported, with a date in its name. When something looks wrong in three weeks, the question is whether the data arrived that way, and only the original answers it. That is where a retention rule starts mattering.

HubSpot, Pipedrive, Attio and Syncek all offer column mapping and a match key. The mapping screen is worth reading slowly: it is the last point where a mistake is cheap. If you are setting up a new structure, decide the objects before the import, not after, and give each one stages you can write a test for.

Frequently asked questions

How do you prepare a CSV for a CRM import?

Fix five things before importing: remove duplicate rows, ensure one header row with one kind of data per column, standardise date and phone formats, choose the column that uniquely identifies each record, and run a ten-row test import. Doing this in the spreadsheet takes minutes, whereas correcting the same problems inside the CRM afterwards means merging records rather than deleting rows.

Should you deduplicate before or after importing?

Before, always. Removing a duplicate row in a spreadsheet is a single delete. The same duplicate inside a CRM becomes a merge that has to reconcile two sets of notes, emails, tasks and deals, and someone has to decide which record survives. Sorting by email address and removing exact repeats catches most of them in a couple of minutes.

What date format should a CRM import use?

Use YYYY-MM-DD. It is unambiguous, sorts correctly as text, and is accepted by every mainstream importer. Formats like 03/04/2026 mean two different dates depending on whether the reader expects day or month first, and spreadsheets often silently reformat a date column when the file is opened, so verify the format after your last edit rather than before.

What is a unique key in a CRM import and why does it matter?

It is the column the importer uses to decide whether a row is a new record or an existing one. Email works for people and web domain for companies. Without a key, every import creates fresh records, so running the same file twice produces a complete duplicate set. Check whether your tool creates, skips or updates on a match before running the full file.

Why should you test with ten rows first?

Because every mapping mistake is visible in ten rows and expensive in ten thousand. A ten-row import lets you open the records and confirm that names, dates and phone numbers landed in the right fields and kept their formatting. Fixing a wrong mapping after a full import usually means deleting everything and starting again, assuming the records can still be identified.

Should you keep the file you imported?

Yes, saved with the import date in its filename. When a record looks wrong weeks later, the only way to answer whether the data arrived that way or was changed afterwards is the original file. It also lets you re-run a corrected import against a known starting point rather than reconstructing what the source contained.