CSV Data Cleaning for CRM Imports: The Complete Guide

Most people who fight with CSV imports think of it as a formatting problem. Get the columns right, get the dates right, and the file goes in. That mental model works until the file has more than a few hundred rows, comes from more than one source, or gets imported more than once. Then it stops working, and the reason it stops working is that a CRM import isn’t really a file transfer. It’s a validation pipeline, and every stage of that pipeline can reject your data for a different reason.

Once you see it that way, the recurring failures stop looking random. HubSpot flags a picklist value. Salesforce blanks a date field. Pipedrive silently drops a value nobody asked it to touch. These aren’t bugs, and they aren’t really about your file being “messy.” They’re the predictable output of a system checking your data against rules it never told you about, in an order it never explained. This guide walks through that pipeline stage by stage, because understanding it is what turns CSV cleanup from a recurring chore into something you do once per file format and never think about again.

Why import validation happens in stages, and why that matters

Every CRM import, regardless of vendor, runs through the same four stages, whether the interface shows them to you or not.

Parsing comes first. The system reads the raw bytes of your file, decides what delimiter separates your columns, what encoding the text is in, and where each row ends. If this stage gets it wrong, everything downstream is wrong too, and the failure often doesn’t look like an encoding problem. It looks like a phantom extra column, or a name field containing half of the address field, because a comma inside an unquoted value shifted every subsequent column by one.

Schema mapping comes second. The system tries to match your column headers to its own field names, either automatically (if the names match exactly) or by asking you to map them manually. This stage doesn’t validate content at all. It only decides where each column’s data is going to land.

Type and value validation comes third, and this is where most of the visible errors happen. Every field has a type: text, number, date, single-select, multi-select. The system checks each cell against the expected type and, for picklists, against a fixed list of allowed values. This is also where locale-dependent formats cause trouble, because 01/05/2024 is a valid date in two different ways depending on which convention the system assumes.

Relationship resolution comes last, and it’s the stage almost nobody talks about. If your file references an owner, an account, a parent company, or any other linked record, the system has to resolve that reference to an actual record in the database before it can commit the row. This is where imports fail in the most confusing way, because the row can pass every other check and still fail, or worse, succeed with the wrong relationship silently attached.

The reason this staged model matters practically is that it tells you where a fix belongs. A fix that happens before parsing (re-encoding the file, confirming the delimiter) is different in kind from a fix that happens before type validation (standardising picklist values), and different again from a fix that only applies at the relationship stage (making sure an owner’s email exactly matches their CRM login, not their display name). Trying to fix a relationship problem by re-checking your date formats wastes time because you’re solving the wrong stage.

It also explains why some failures are catastrophic and others are quiet. Most modern import tools, including HubSpot’s and Pipedrive’s, commit row by row: one row failing type validation doesn’t stop the rest of the file from importing, it just gets skipped or blanked. Salesforce’s Bulk API batches rows and can fail an entire batch depending on the error, which is why a single bad row in a Salesforce import can sometimes take out 199 good ones with it if you haven’t set the batch size and error handling correctly. Knowing which behavior your target system uses changes how cautious you need to be before you upload, and how you test.

Schema drift: the problem that hides in plain sight

The first stage most people skip past is schema mapping, because it looks trivial. Your file has a column called Company, the CRM has a field called Company Name, you click a dropdown once, done. The trouble starts when you do this every week for the same recurring export and the mapping isn’t saved anywhere, so you repeat the same 20 or 30 clicks every time, and one missed mapping means a column silently imports as blank with no error at all, because from the system’s point of view, an unmapped column simply wasn’t imported. Nothing failed. Nothing was flagged. The data just isn’t there.

The fix is to stop treating column names as something you match at import time and start treating them as something you standardise at export time, or immediately after. If your source system’s column names are renamed to match your CRM’s internal field names before the file ever reaches the import wizard, the mapping step disappears, because the system auto-matches exact names. This sounds like a small optimisation but it removes an entire category of silent data loss. Why your HubSpot CSV import keeps failing covers the specific internal property names HubSpot expects, including the non-obvious ones like the owner field, which has to be the rep’s login email rather than their display name to resolve correctly.

Picklist and enum mismatches: the single biggest source of failed imports

If schema drift is a silent failure, picklist mismatches are the loud one, and they’re the most common reason an import produces errors at all. The mechanism is simple: a picklist or single-select field only accepts a fixed, pre-defined set of string values, matched exactly, usually case-sensitively. Your file doesn’t know that list. It knows whatever string a human typed, or whatever string a different system exported, and those two things drift apart constantly. Closed Won, closed won, CLOSED-WON, and Won might all mean the same thing to a person reading the spreadsheet. To a picklist validator, they’re four different values, and only one of them, if any, matches what the field actually allows.

What makes this worse than it looks is that the mismatch compounds with data provenance. A file assembled from a Salesforce export, a LinkedIn export, and a manually maintained spreadsheet will have three different conventions for the same concept, because each source picked its own. Every merge adds new variants to a list that was already inconsistent. By the time you’ve imported the same recurring file for six months, a single column can hold twenty or thirty spellings of five or six actual values, and the only reliable fix is a canonical list: define the exact set of values your CRM allows, and map every variant you’ve ever seen, including ones you haven’t seen yet, to the right one.

This is also the stage where CRMs diverge the most in behavior, which is worth understanding before you decide how much manual review you’re willing to do. How to standardise picklist values before a CRM import covers building that canonical mapping, and how to bulk replace values in a CSV column covers applying it across a large file without going value by value, which is the part that actually doesn’t scale by hand once you’re past a few hundred unique entries.

Dates, locale, and the ambiguity that never announces itself

Date fields fail differently from picklists, because a bad date rarely looks bad. 01/05/2024 parses successfully in almost every system. The problem is that it parses to two different dates depending on whether the system assumes month-first or day-first ordering, and both January 5th and May 1st are valid outputs. Nothing errors. Nothing gets flagged. You just end up with deals closed on the wrong day, or contacts created with a birthdate a month off, and you don’t find out until someone questions a report weeks later.

This is why the fix for dates isn’t really about formatting, it’s about removing ambiguity entirely. YYYY-MM-DD is the one format that can’t be misread, because the largest unit comes first and there’s no version of it that means two different things. Converting to it before upload, rather than trusting the destination system to guess correctly, is the only way to be sure the date that lands in the CRM is the date you intended. How to standardise date formats in a CSV file covers the conversion mechanics in Excel and Google Sheets, including the case where a column mixes multiple source formats because it was compiled from more than one export.

Encoding, delimiters, and the regional formatting nobody warns you about

Parsing failures are the least visible of all, because when they happen, the file often looks completely fine when you open it. Encoding problems show up as garbled characters, é instead of é, or as characters that vanish entirely, and they happen because a file saved on Windows in Latin-1 or Windows-1252 gets read by a system expecting UTF-8. Delimiter problems are subtler still: a CSV saved on a European Windows machine typically uses a semicolon as its field separator, because the comma is already spoken for as the decimal separator in that locale. Open that file with a system expecting commas, and every row lands in a single column, or splits at the wrong point if there happen to be commas inside a value.

These are parsing-stage problems, which means they’re the earliest thing to check, before you even think about picklists or dates, because if parsing goes wrong, none of the later validation is checking the data you think it’s checking. Why European CSV files break in CRM imports covers the semicolon-delimiter and decimal-separator issues specific to EU-sourced exports, and how to fix encoding errors in a CSV file covers detecting and correcting the encoding itself, including the BOM character that Excel silently adds to UTF-8 files and that some stricter importers choke on.

Relationship resolution: the stage that fails after everything else passes

This is the part of the pipeline that gets the least attention, and it’s worth understanding on its own because it fails in a way none of the earlier stages do: silently, and after the row has already passed type and picklist validation.

Any field that points to another record, an owner, a parent account, a related contact, isn’t validated against a fixed list of strings the way a picklist is. It’s resolved by looking up a matching record in the database at import time. If your file has an “Owner” column containing “John Smith,” the system has to find a user record whose name matches, and if two reps share a first and last name, or if the match is done by display name instead of a unique identifier like an email address, the row can import successfully and attach to the wrong owner without any error at all. The import didn’t fail. It just resolved to the wrong thing, which is arguably worse than a visible failure because nothing prompts you to go check.

The practical rule is to always import relationship fields using the most unique identifier available, typically an email address or an internal record ID, rather than a display name. It’s slightly more setup work up front, exporting the right identifier alongside the human-readable name, but it’s the only way to guarantee the row attaches to the record you actually meant.

Multi-value fields: where a familiar separator becomes the wrong one

Checkbox and multi-select fields accept more than one value per cell, but they need those values separated by something other than a comma, because a comma is already the file’s own column delimiter. Most CRMs expect a semicolon inside the cell for this reason. The failure mode here is specific and easy to miss: if a multi-select field receives a comma-separated string, some systems will try to match the entire string as a single value, fail to find it in the allowed list, and blank the field, rather than partially importing the values it does recognise. The visible symptom, an empty multi-select field on an otherwise successful import, gives no indication that the separator was the actual cause.

The fix is a targeted find-and-replace scoped to that specific column, converting commas to semicolons within the cell values, not across the whole file, since the file’s actual delimiter still needs to stay a comma.

How the major CRMs actually differ in failure behavior

Everything above applies to any CRM, but the three most common import targets diverge in one important respect: what happens when a row fails.

HubSpot validates picklist and type mismatches at the point of import and interrupts the wizard to ask you to remap unrecognised values by hand, one column at a time. That’s tolerable for a single column with a handful of variants. It becomes the bulk of your import time once you have ten picklist columns with dozens of unique values each, because none of those manual remaps are saved, and the next import starts the process over from nothing.

Salesforce’s behavior depends heavily on which import path you use. The standard Data Import Wizard behaves similarly to HubSpot for picklists, but the Bulk API, which most serious volume imports go through, processes rows in batches and can fail an entire batch on certain error types rather than skipping just the offending row. This is the detail that catches experienced admins off guard: a single malformed row in a large file, if it triggers a batch-level failure rather than a row-level one, can take a couple hundred otherwise valid rows down with it, and the fix is either correcting the row before you resubmit or configuring the batch size and error-handling behavior so a bad row doesn’t have that blast radius.

Pipedrive is the quietest of the three, and that quietness is itself the risk. Unrecognised values for custom fields frequently get dropped without prompting for a remap, silently leaving the field blank rather than surfacing an error you’d notice. That makes pre-upload validation more important for Pipedrive than for the other two, precisely because the system won’t tell you anything went wrong.

Why your HubSpot CSV import keeps failing, Salesforce CSV import: column names, picklist values, and common errors, and Pipedrive CSV import: column names, field types, and common errors each go deep on their respective system’s specific quirks. If you’re not importing into one of these but migrating data between two CRMs entirely, the schema-mapping and relationship-resolution problems above compound on both ends at once, which how to prepare a CSV for CRM migration addresses directly.

Turning a one-off fix into a system that survives the next file

Everything described so far solves the file in front of you. It doesn’t solve the version of this file that arrives next week, because the underlying cause, an upstream system exporting inconsistent picklist values and column names, hasn’t changed. The mapping you just built by hand is disposable unless you deliberately make it reusable.

The shift that actually fixes this long-term is to stop thinking of cleaning as a task you perform on a file and start thinking of it as a transformation you define once and apply repeatedly: a saved column mapping, a saved picklist canonicalisation table, and a validation pass that runs the same checks every time, in the same order the CRM itself will apply them, so you catch relationship and picklist failures before you upload rather than after the wizard rejects them. Repeatable CSV import workflows for RevOps teams covers building that into an actual saved process rather than redoing the same manual decisions from memory every cycle.

Two recurring data sources deserve special mention because they combine several of the failure modes above in one file. Event registration exports are messy in a specific way: they’re user-generated, which means job titles, company sizes, and industries arrive as free text with no consistent convention at all, on top of the usual picklist and relationship problems. How to clean an event attendees list before importing it into your CRM covers that case specifically, including deduplication and attendance-status tagging that a standard picklist mapping doesn’t address. Marketing campaign data has its own version of the same drift problem: if UTM source and medium values aren’t standardised the same way picklist values are, your attribution reporting fragments the same way a CRM segment count does when “Technology” and “tech” are treated as different values. How to standardise UTM parameters across your marketing team applies the same canonical-value discipline to that problem.

When the question isn’t whether the file will import, but whether it should leave at all

Everything above assumes the goal is getting a file successfully into a CRM. Sometimes the actual question is different: whether a file should be shared, in its raw form, with a third party at all. A contractor, an agency, or an external data warehouse often needs the structure and shape of your data without needing the real customer names, emails, or deal values inside it, and the same discipline that governs import validation, know exactly what each column contains and treat it deliberately rather than passing it through unexamined, applies here too.

GDPR and CSV exports: what you can and can’t share with a third party covers the compliance reasoning, redact, hash, or fake: which anonymization strategy to use for each column covers choosing the right treatment per column rather than applying one blanket approach to the whole file, and how to anonymize a CSV before sharing it with a contractor walks through a complete example end to end.

Where manual tools stop scaling

Everything in this guide can be done by hand, in Excel or Google Sheets, for a file with a few hundred rows and a handful of picklist columns. TRIM() handles whitespace, TEXT() handles date conversion, Data > Remove Duplicates handles deduplication. Where manual tools genuinely stop scaling is picklist normalisation across a large file with many unique values, because that’s a many-to-one mapping problem that a formula can’t apply on its own, it needs an actual lookup table applied consistently across every cell that matches. The best free CSV import tools for CRM and free CSV cleaner online: what it can (and can’t) fix cover what free tools handle well and where that ceiling shows up in practice. For everything covered in this guide condensed into a single working checklist, the complete CSV cleaning checklist before any data import is the reference to work through file by file.

Asphorem’s CSV Normalizer is built around the two things that don’t scale manually: it applies a saved column mapping and picklist canonicalisation automatically on every subsequent import of the same file format, and the AI matching step catches typos, language variants, and capitalisation drift across a whole column in one pass, all while keeping your file rows local to your browser. Only the unique picklist values, never full rows, are sent for AI matching.

CSV Cleaning For CRM Import: Frequently Asked Questions

Why does my CRM import fail even though the file looks correct in Excel?+

Because “looks correct” and “passes validation” check different things. Excel shows you what a human reading the sheet would call correct. The CRM checks each cell against a type, a picklist’s exact allowed values, and for relationship fields, whether a matching record actually exists. A trailing space, a case difference, or a display name instead of a login email can all look identical to a person and still fail validation.

What’s the most common reason a CSV import fails?+

A picklist value in the file doesn’t exactly match one of the allowed values in the destination field. It’s more common than column-name or date problems because a single column can hold a dozen spelling and capitalisation variants of the same underlying value, especially once a file has been merged from more than one source. See how to standardise picklist values before a CRM import.

Do HubSpot, Salesforce, and Pipedrive fail imports the same way?+

No. HubSpot and the Salesforce Data Import Wizard interrupt at the row or field level and prompt for a manual remap of unrecognised picklist values. Salesforce’s Bulk API, used for most high-volume imports, batches rows and can fail an entire batch on certain error types rather than skipping just the offending row. Pipedrive is the quietest: it frequently drops unrecognised custom-field values without prompting at all, which makes pre-upload validation more important there than with the other two.

What’s the safest date format to use across every CRM?+

YYYY-MM-DD (ISO 8601). It’s unambiguous because the largest unit comes first, it sorts correctly as plain text, and every major CRM and database parses it the same way regardless of regional settings, unlike MM/DD/YYYY or DD/MM/YYYY, which are read differently depending on the system’s locale assumptions.

Why did a contact import with the wrong owner even though the import showed no errors?+

Relationship fields like Owner are resolved by looking up a matching record, not validated against a fixed list. If the match is done on a display name and two people share that name, or the name doesn’t exactly match any user record, the row can still import successfully and attach to the wrong person, or an unexpected one, without triggering an error. Importing owner and other relationship fields by a unique identifier, typically a login email, avoids this.

How do I stop fixing the same CSV problems every time a file arrives?+

Save the column mapping, the picklist value mapping, and the validation checks as a reusable configuration instead of rebuilding them from memory on each import. See repeatable CSV import workflows for RevOps teams for how to structure that as an actual process rather than a one-off cleanup.