How to Clean a CSV File Before Importing It Into Your CRM

Step 1: fix the file shape

Shape first, because shape errors break everything else. One header row, with a clear name for each column. No merged cells, no blank rows in the middle, no totals row at the foot. Each row should have the same count of fields as the header. Rows that do not are flagged in a good tool with their line number, so you can fix them at the source instead of guessing.

Watch for the quiet shape traps. A notes field with commas in it must be quoted, or the row shifts a column to the right and a phone lands in the email field. A file saved from a sheet tool may use semicolons, not commas — that is fine, as long as the whole file agrees. And check the text encoding: odd marks in names mean the file was saved in one encoding and read in another.

While you are here, drop columns your CRM does not take. Extra columns slow you down and can confuse a mapping screen. But do not drop data you need — move it to a notes field or keep it in the source file. The source file is your safety net for the whole job. Keep it whole, and clean a copy.

Also decide the fate of the header row itself. Some exports carry two header rows, or a title row above the true headers. Some carry none, and your first “row” is a person. All three cases import badly in their own way. Name the columns yourself if the file will not. A file whose shape you set on purpose will behave. A file whose shape you guessed at will not — and the import will not tell you which you did.

Step 2: tidy the values

Now the values inside the fields. Trim extra spaces at the ends of values — they are the top cause of “the same, but not matched.” Set one form for phones: digits, with or without the country code, but the same throughout. Set one form for dates, and check a few by hand, since day-month flips are silent and cruel. Fold firm suffixes to one shape, or strip them for the match.

Emails deserve a hard look. Trim them, fold them to small letters, and scan for the classic slips: “.con” for “.com,” spaces in the middle, a missing “@.” A row with a bad email will fail to match its twin and will bounce when you mail it. Fix what you can prove. Flag what you cannot — a guessed email is worse than a blank one, since it mails a stranger or no one.

Last, fill the fields your CRM needs most. If your CRM keys records by email, rows with no email need a second key — phone, say — or a hand check. Count the blanks in each key column now. A file with 5% blank emails is fine. A file with 60% blank emails needs a different match plan, and it is far better to know that today than after the import.

Step 3: dedupe the file itself

A dirty file makes a dirty CRM, no matter how good the CRM's own checks are. So dedupe the file before it goes in. Match on the strong columns — email, then phone plus name. Review each group. Fill the gaps so the survivor holds the best facts from each row it absorbs. Keep the audit file the tool gives you: it is your proof of what was removed and why.

If you have never cleaned a file this way, the full walk-through is in our guide on removing duplicates from a CSV without losing data — the same steps, with the merge rules spelled out.

Step 4: map the fields to your CRM

Your CRM has its own field names and its own rules. Map each of your columns to a CRM field before you import, on paper or in the tool's mapping screen. Fields with no home need a call: add a custom field, fold the value into notes, or leave it in the source file. Do not let the import guess — guesses at scale become cleanup projects.

Check the CRM's rules for each field. Some fields cap length. Some take only set values — a “type” field that wants “Client” will reject “client” or “Customer” in strict setups. Some CRMs split names for you; some want first and last apart. Ten minutes with the CRM's import help page now is worth a day of fixes later. This is also the moment to set the match key the CRM will use, and to write it down for your team.

While you map, note which of your columns has no CRM home and which CRM fields will sit blank. Both lists are short in most imports, and both are worth a conscious call. A blank CRM field is fine when it is blank on purpose. It is a bug when it is blank because a column fell off the map. The map on paper — even a rough one — is what tells the two apart after the fact.

Step 5: test with a small batch, then import

Never let the first import be the full file. Take 50 rows — a mix of new, known, and tricky rows — and import those. Then check by hand: did known clients match, or did they double? Did the fields land in the right places? Did any value get cut or bent? A small test turns big surprises into small ones.

When the test passes, run the full file, log who ran it and when, and check the record counts the same day. If you clean files like this each month, save the routine. In DedupeCSV that is a recipe — match columns, form fixes, and merge rules in one click — and the paid plans raise the row limit well past the free 1,000; compare the plans if your files are large.

One last habit: keep the pre-import file, the clean file, and the audit file together in one folder, named with the date. Six months from now, when someone asks why a record looks the way it does, that folder is the answer. Imports you can explain are imports you can trust.

The fields that cause the most import pain

Some fields break imports far more often than others. Dates lead the list. A CRM may read 03/04/2026 as March 4 while your file meant April 3 — or reject the value outright. Convert all dates to one clear form before the import, and check a few rows where the day is above twelve. Those rows prove which way round the file's dates run.

Phone fields come next. CRMs store them as text or as numbers, and the two kinds treat formats very differently. A number-typed field will eat the plus sign in +1 and may drop a leading zero. If your list serves more than one country, keep phones as text with the country code, and test one foreign number in the small batch before the full run. One test row is a cheap guard.

True-and-false fields are a third trap. One file says “Yes,” the CRM wants “true,” a third source says “1.” Consent flags, opt-in flags, and “is a client” flags all live in this family — and a wrong read here can mail someone who opted out. Map these fields with extra care, and after the test batch, check one known opt-out by name to be sure the flag landed the right way.

Long text fields bite too. Notes get cut at a field's max length in some systems, with no warning. If your notes matter — and they usually hold the gold — check the longest note in your test batch after import. If it was cut, split the field, raise the limit, or keep the full text in the source folder and import a trimmed copy with a pointer to it.

Last, ID fields. If your file has IDs from another system, decide now whether they are just baggage or a key you will lean on later. Baggage should ride in a clearly named custom field, never in the CRM's own ID field. An import that writes foreign IDs into the home ID field can break links, reports, and syncs in ways that surface weeks later, far from the cause.

None of these five fields is hard to check. They are just easy to forget, because the mapping screen shows names, not behaviors. The small test batch exists to catch exactly this family of pain: five fields, five quick checks, while the batch is still small enough to throw away and redo.

What if the CRM is already messy?

Then clean in the other direction. Export the CRM to a CSV, clean the export with these same steps, and use the clean file to guide your fixes — or re-import it with a strict match key once you trust it. Work in waves: exact copies first, then strong near-matches, then the thin cases by hand. Do not try to fix a year of mess in one night.

And fix the door, not just the room. Most CRM mess comes from a few repeat doors: imports with no key, forms with no checks, and staff adding rows by hand when search “found nothing” because of a typo. Add the pre-flight check to each door. A clean CRM is not a project. It is a habit at the point where data comes in.

Clean first, import once

Shape, values, dedupe, map, test. That is the whole pre-flight check, and it fits on an index card. Tape the card to the wall where imports happen. The file you clean for an hour today is the CRM you do not have to clean for a month next year.

When the file holds rows from several sources at once, one more step comes first — lining up columns that do not match. Our guide on deduping lists from different sources shows how to map them before you clean.

Put the five steps on the wall, run them on the next file that lands, and let the import be the dullest part of your week. Dull imports are a sign of a healthy system.

Sources and further reading

• Preparing your data for eCommons — Cornell University Library — Cornell's guidance on getting spreadsheets and CSV files ready: one header row, clean fields, and notes that explain the file.
• Analysis Ready Datasets — UC Davis Library — UC Davis on preparing data files: documentation, standardization, and validation before the data is used or shared.