How to clean a CSV before a CRM import

A CRM import fails for boring reasons: two rows share an email, a phone number has brackets the system won't parse, or a single "Full Name" column doesn't line up with the separate first and last name fields the CRM expects. The fix is to clean a CSV before a CRM import rather than after — to correct the file's contents while it is still a spreadsheet, so the CRM accepts every row on the first pass.

This guide walks through the four checks that catch almost every import problem, and shows how to do all of them in your browser without uploading the file anywhere. None of them require special software or a data engineer — a spreadsheet and a few minutes are enough. The order matters, though: deduplicate first, standardize the fields the CRM matches on next, and reshape your columns last, so each step works on the cleanest possible version of the file.

Why clean the CSV before importing, not after

The tempting shortcut is to import first and tidy up inside the CRM. It rarely pays off. The moment a row enters your CRM it stops being an inert line in a spreadsheet: it can trigger a welcome email, get routed to a sales rep, enroll in a sequence, and start collecting activity history. Cleaning it up afterward means reconciling all of that — merging duplicate timelines, reassigning owners, and hoping no automation already fired.

Cleaning the file first avoids the whole problem. A row that never enters the system never causes a mess. It is also faster: you are working with plain text, not a live database with relationships and triggers hanging off every record. HubSpot's own import guidance is explicit that a well-formed file — one column per property, consistent formatting, and a header row that maps cleanly to fields — is what makes an import succeed [1]. Every minute spent on the spreadsheet is a minute you do not spend untangling records later.

Remove duplicate rows first

Duplicates are the single most common reason an import inflates your numbers. Lists assembled from event sign-ups, web forms, and past exports routinely carry ten to thirty percent duplication, and CRMs match on a key field — usually email — to decide whether a row is new. Two rows with the same email can create two contacts, or collide and drop data, depending on the CRM's settings.

The trouble is that near-duplicates rarely look identical. One row spells the company "Acme Inc", the next "Acme, Inc."; one email has a trailing space the eye cannot see. So a plain "delete identical rows" pass misses most of them. What you actually want is to dedupe on the one field the CRM treats as the identity of a record, and to normalize that field first so near-matches collapse into true matches.

Before importing, decide the column that identifies a unique person — email is the usual choice — and remove rows that repeat it, keeping either the first or the last occurrence. Spreadsheet apps can do this manually; Microsoft's Remove Duplicates feature is one built-in option [2]. If you would rather not open the file in Excel at all, our remove duplicates from a CSV tool does the same thing in one pass and lets you choose which duplicate to keep. Deduplicating here, before the import, means the extra rows never reach your pipeline reports.

Standardize phone numbers and emails

Two columns generate most of the remaining rejected rows: phone numbers and emails.

Phone numbers arrive in every shape a human can type — (415) 555-0142, 415.555.0142, +1 415 555 0142. Most CRMs and every SMS tool want the E.164 standard: a plus sign, country code, and national number with no separators. Standardizing the whole column up front means texts actually send and no row bounces on a malformed number. Our clean phone numbers tool rewrites a messy column to E.164 in place.

Emails look simpler but hide the same trap. [email protected], [email protected] , and [email protected] are the same address to a person but three different strings to a CRM's duplicate check, because of the capital letter and the trailing space. Lowercasing, trimming whitespace, and dropping obvious typos before import collapses those back into one contact. The clean email column tool trims, lowercases, and validates an email column so the CRM's matching works as intended.

Match name columns to your CRM fields

CRMs store first name and last name separately. Exports and sign-up forms often hand you a single "Full Name" column instead. Import that as-is and you get a first-name field reading "Jane Smith" and an empty last name — which then breaks personalization tokens in every email that greets the contact by first name.

Split the full-name column into first and last name before importing so each maps to its own field. Middle names, suffixes, and two-word surnames make this fiddlier than it looks, which is why doing it in a dedicated tool beats a fragile spreadsheet formula. The split full names tool separates a name column into first and last (with an optional middle name) so your columns line up with the CRM's fields. If your CRM instead wants a combined field you do not have, the reverse — joining columns — is just as quick.

Do it without uploading the file

Here is the part most guides skip: a contact list is personal data, and uploading it to a random online "CSV cleaner" to fix it means handing that data to a third party you may never have vetted. Under the GDPR's data-minimisation principle, sending personal data somewhere it does not need to go is exactly the kind of processing you want to avoid.

You do not have to. Sigmera runs every one of these steps with client-side code in your browser, so the spreadsheet never leaves your device — nothing is uploaded to a server. You get the convenience of a dedicated tool without the privacy cost of a round trip to someone else's cloud. For a worked example against a specific CRM, see our guide on how to clean a CSV before a HubSpot import.

A pre-import checklist

Run this list against any file before you import it:

1. Duplicates removed on the CRM's match key (usually email).
2. Phone numbers standardized to E.164.
3. Emails lowercased, trimmed, and validated.
4. Name columns split or combined to match the CRM's fields.
5. Header row present, one column per property, names matching the CRM.
6. Empty rows and columns deleted.

Work top to bottom and the import stops being a gamble. The file arrives clean, the CRM accepts every row, and your reports start from accurate numbers instead of a pile of duplicates you have to fix under deadline. It also protects the relationships already in your CRM: no accidental duplicate that splits a customer's history across two records, no automation firing against a half-formed contact. Keep the cleaned file as your record of what you imported, and next quarter's list starts from a known-good template rather than a fresh mess. Best of all, none of it required uploading your contacts anywhere — the whole job stays on your machine, which is where personal data belongs.