Every import failure feels like a tooling problem and almost never is. The file was assembled from three sources, edited by four people, and exported by a system that quietly changed a date format. The importer is simply the first thing strict enough to notice.
Cleaning data is much easier when you do it in a fixed order, because each step makes the next one simpler.
Step 1: Fix the structure before the content
Do not correct a single value until the shape of the file is right. That means:
- Exactly one header row, at the very top.
- No merged cells, no blank spacer rows, no totals row at the bottom.
- No second table hiding below or beside the first one.
- One value per cell - a "Contact" column containing "Jane Doe <jane@example.com>" is two columns wearing a trench coat.
Fixing values in a badly structured file means doing the work twice, because restructuring will shift everything you just corrected.
Step 2: Normalise whitespace and case
Trailing spaces are the most destructive invisible character in data work. They break joins, defeat deduplication, and cause "duplicate" records that the system insists are different. Trim leading and trailing whitespace across every text column, collapse double spaces, and normalise the case of anything that should be case-insensitive - email addresses above all.
Step 3: Deduplicate
Only now is deduplication reliable, because Jane@Example.com and jane@example.com finally look the same to a comparison. Decide explicitly what makes two rows "the same" - usually email or an external ID, rarely the full row - and keep the most complete version, not just the first one you encounter. For a simple line-separated list, paste it into the Duplicate Line Remover and copy the result back.
Step 4: Validate the fields that will be rejected
Most importers fail on a small, predictable set of columns:
- Email addresses - malformed entries stop the whole import in systems with a unique or format constraint. Run the list through the Email Validator first.
- Dates - the single worst offender. Decide on ISO format (
YYYY-MM-DD) and convert everything to it, because03/04/2026is genuinely ambiguous and spreadsheets will "helpfully" reinterpret it. - Phone numbers - pick a format, usually E.164 (
+14155550123), and strip everything else. - Numbers stored as text - currency symbols, thousand separators and stray spaces all make a numeric column fail.
Step 5: Check the encoding and delimiter
If names come through as José rendered as mojibake, the file was saved in one encoding and read in another. Save as UTF-8 and, when the importer allows it, say so explicitly. Watch the delimiter too: a comma-separated file containing commas inside values needs proper quoting, and exporting as tab-separated often sidesteps the problem entirely.
Step 6: Test with ten rows
Never test an import with the full file. Cut the first ten rows into a separate file, import those, and inspect the result in the destination system - not the success message, the actual records. You will catch mapped-to-the-wrong-field errors in two minutes instead of discovering them after 40,000 rows have landed in the wrong place.
Step 7: Write down what you did
Three lines in a text file next to the export: where the data came from, what you changed, and what the row count was before and after. Next quarter, when the same export arrives with the same problems, that note turns an afternoon into ten minutes. It is also the difference between a cleaning process and a cleaning habit.