How to clean CSV data before database import

Manual spreadsheet cleanup is difficult to repeat and easy to forget. A better approach is to build an explicit sequence of small transformations that can be reviewed before export.

1. Keep the original dataset untouched

Always preserve the source data. Apply transformations to a working copy so that every change can be reviewed, removed or reapplied without losing the original evidence.

2. Remove accidental whitespace deliberately

Leading and trailing spaces can break joins, keys and equality checks. Trimming text is often useful, but it should be a visible transformation rather than an invisible side effect.

3. Normalize casing only where casing is not meaningful

Status codes, country codes and some identifiers may benefit from consistent uppercase or lowercase. Free-form names and case-sensitive identifiers may not. Apply case normalization by column rather than globally when possible.

4. Replace known placeholder values

Source systems often use values such as “N/A”, “-” or legacy labels. A repeatable find-and-replace step makes those changes auditable and avoids ad-hoc manual edits.

5. Treat empty values and NULL intentionally

An empty string is not always equivalent to database NULL. Decide which representation the downstream process expects and normalize only when that rule is appropriate for the target system.

6. Remove duplicates using the right rule

Exact duplicate rows are straightforward. Duplicate business keys require more care: choose the key columns explicitly and decide whether the first or last occurrence should survive.

7. Preview before exporting

A before/after preview should be part of the workflow. Review row counts, column names and representative values before generating the clean output used by an import or migration process.

If you have not inspected the file yet, start with profiling the CSV before import.