A repeatable process for cleaning messy CSV exports

Every system exports CSV badly in its own way, but the failure modes are remarkably consistent. Here is the order I work in. Order matters — cleaning whitespace before fixing encoding just means doing it twice.

Step 1: Encoding

Open the file and look for é, ’ or ? where accented characters should be. That is UTF-8 being read as Windows-1252, or vice versa.

Fix it at import, not afterwards. Once mojibake is in your data, the original character is often unrecoverable. The CSV to Excel tool sniffs the encoding and shows you a preview before committing.

Step 2: Delimiters and quoting

Semicolon-delimited files from European systems, tab-delimited from database exports, and the special pain of a comma-delimited file where a description field contains commas and quoting was disabled.

If everything lands in column A, the delimiter was mis-detected. If rows appear duplicated or truncated, quoting is broken. Both are import-time settings, not post-import fixes.

Step 3: Types

Decide, per column, what it should be:

• Identifiers — always text. Order numbers, SKUs, postcodes and phone numbers are not arithmetic, and treating them as numbers destroys leading zeros.
• Dates — always real dates. Text that looks like a date will not sort, filter or group correctly.
• Amounts — always numbers. Strip currency symbols before, not after.

Step 4: Whitespace and hidden characters

Trim leading and trailing whitespace, collapse repeated internal spaces, and strip non-printing characters. This is the step that fixes lookups you have not written yet.

Step 5: Duplicates

Only now, with types and whitespace consistent, is it safe to deduplicate. Running Remove Duplicates before step 4 will leave you with pairs that differ only by an invisible character.

Choose your key columns deliberately. Whole-row matching finds far fewer duplicates than matching on the columns that actually define a record.

Step 6: Validate

Before the data goes anywhere, run it through Data Validator with rules for required fields, formats and ranges. The point is not to catch everything — it is to catch the systematic failure, the one where 4,000 rows have a null in a column that should never be null because an upstream integration broke last Tuesday.

Make it repeatable

Write the six steps down for each recurring export, with the specific settings. On our team, the same file arrives every Monday and the cleaning is now a checklist rather than a puzzle. That change alone cut the time from forty minutes to under ten, and eliminated the category of error where somebody skipped a step and nobody noticed until the report went out.