The 12-Point CSV Cleaning Checklist Before Every CRM Import

The moment you click "Import" in your CRM is the moment your data quality problem becomes permanent — at least until someone cleans it up manually. A CRM import is not a reversible action in any practical sense. Yes, you can delete the records, but the automation triggers have already fired, the duplicate contacts have already been matched or missed, and your email sequences may have already started sending to people with incorrect data.

This checklist is what our team runs through with customers before every CRM import. It takes between 30 minutes and two hours depending on file size, but it consistently prevents weeks of cleanup work downstream. Save it, share it with your team, and run it every time.

Before You Open the File

The first checks happen before you look at a single row of data. These are structural and process questions that determine whether you should even proceed.

• Do you know the source of this file? A CSV exported from another CRM is very different from one assembled manually in a spreadsheet. Source determines what errors to expect.
• What's the date range? Data older than 18 months should be treated with extra suspicion. Email addresses, phone numbers, job titles, and company names all change rapidly.
• Has this file been through any transformation? Manual edits in Excel are the most common source of encoding corruption, accidental row deletions, and hidden characters.

Step 1 — File Integrity Check

Open the raw file in a text editor (not Excel) and check the following:

• Confirm the delimiter is actually a comma (or the character you expect). Tab-separated files accidentally opened as CSV will parse as a single column.
• Check for a byte-order mark (BOM) at the start of the file. Some Excel exports include a BOM that causes the first column header to display as name instead of name.
• Spot-check 5 random rows near the middle and end of the file. Last rows are often where truncation and encoding issues accumulate.
• Check that the row count matches what you expect. A file described as "5,000 contacts" that has 4,887 rows needs an explanation before you proceed.

Step 2 — Column Mapping Audit

CRM column names are notoriously inconsistent across systems. Before you try to match columns, create a mapping document — even a simple two-column table — that maps source column names to your CRM's field names. Flag any source columns with no clear destination as "needs decision."

• Check for split name columns. Source files often have "First Name" and "Last Name" as separate columns. Your CRM may expect a single "Full Name" field, or vice versa.
• Identify phone number columns. A single contact often has multiple phone fields: main, mobile, fax. Decide which one maps to which CRM field before import.
• Look for custom fields you don't have in your CRM yet. It's faster to create them before import than to retroactively add them after.

Step 3 — Duplicate Detection

Running a deduplication check on the import file before import is the single most valuable thing you can do. After import, deduplication is a multi-hour manual process. Before import, it takes minutes with the right tool.

Check for duplicates on these combinations:

• Exact email match. This is the gold standard — if two rows have the same email, they are the same contact.
• Fuzzy name + company match. "John Smith at Acme" and "Jon Smith at Acme Corp" are probably the same person. Fuzzy matching catches these.
• Phone number match after normalization. "+1 (555) 123-4567" and "5551234567" are the same phone number and should trigger a duplicate flag.

In a typical sales-qualified lead list, we find between 8% and 15% duplicate rate. That means for every 1,000 contacts you import, 80–150 are redundant.

Step 4 — Email Validation

Email is the single most important field in most CRM records. Run each email address through these checks:

• Format validation. Does the address have an @ symbol, a domain, and a TLD? Remove any that don't pass basic format checks.
• Domain existence check. A DNS MX lookup tells you whether the domain currently accepts email. This catches addresses at companies that no longer exist.
• Role address detection. Addresses like info@, sales@, admin@, or noreply@ are role addresses that rarely belong to a specific person. Flag these for manual review before adding to sequences.
• Spam trap patterns. Certain email address patterns are known spam traps. A reputable email validation service will flag these automatically.

Step 5 — Phone Number Normalization

Phone numbers are stored in an almost infinite variety of formats. Before import, normalize all phone fields to a single consistent format. We recommend E.164 format (+15551234567) for international compatibility, or a consistent national format like (555) 123-4567 if you only operate in one country.

Remove all non-numeric characters, then apply your target format. Strip any extensions and move them to a separate field. Flag numbers with fewer than 7 digits or more than 15 as invalid.

Step 6 — Name and Company Normalization

• Apply consistent title casing to names. ALL CAPS and all lowercase are both common artifacts of poor data entry and broken exports.
• Remove leading and trailing whitespace. This is the most common cause of exact-match failures and is invisible in most spreadsheet views.
• Standardize common company suffixes: "Inc", "Inc.", "Incorporated", "LLC", "L.L.C." should resolve to a single canonical form.
• Strip special characters that will break downstream systems: null bytes, directional quotes, and non-breaking spaces are common culprits.

Step 7 — Required Field Completeness Check

Before import, identify the fields your CRM requires for records to function correctly in your automation workflows. Generate a completeness report by column — what percentage of records have a value in each field? Flag any record that's missing more than 2 required fields as "do not import" until enriched.

Step 8 — Final Row Count Reconciliation

After all cleaning steps, confirm your final row count, your duplicate removal count, and your rejected record count. Document these numbers before import. This gives you an audit trail if questions arise later, and helps you measure the health of your data sources over time.

A clean CRM is not maintained by cleaning it after it gets dirty. It's maintained by having a gate at the entry point. This checklist is that gate.

Making This Checklist Repeatable

Running this checklist manually every time is unsustainable. The goal is to automate as many of these checks as possible so that they happen automatically before any import. With the right tooling, the entire checklist — deduplication, normalization, validation, completeness check — can run in under a minute on a file of any size.

Our free cleaning tool handles steps 3 through 7 automatically. For teams that run regular CRM imports, our scheduled job feature on the Growth plan runs these checks on every incoming file automatically and flags exceptions for human review.