How to Clean a CSV Before Importing It

A CSV can open perfectly in a spreadsheet and still fail a database import. The usual causes are mechanical: one row has an extra comma, blank lines become empty records, or invisible spaces turn two apparently identical values into different keys.

Preserve the original first

Treat cleanup as a repeatable transformation. Keep the source file untouched, write a new -cleaned.csv, and record how many rows were removed. If a count changes unexpectedly, you want a way back.

Trim cells, not meaningful interior spaces

Removing whitespace around a cell turns " London " into"London" without changing "New York". This is usually safe for exports assembled from forms or copied spreadsheets, but identifiers with intentional leading spaces deserve a schema-specific rule.

Count columns before deleting anything

The CSV cleaner reports the widest row and can pad short rows with empty cells. It never truncates a wide row. An unexpected extra column often means a comma was not quoted, so inspect it rather than hiding it.

Deduplicate only with a definition

Exact-row deduplication is safe when repeated exports append the same records. It does not decide that “Ada Lovelace” and “ada lovelace” are one customer, or which of two records with the same email is current. Those require business keys and conflict rules.

Watch types after cleanup

CSV does not know that 00123 is an account code rather than the number 123. Set import column types deliberately, then validate row counts and a sample of leading zeros, dates and non-ASCII names. If the destination is an API, the CSV to JSON converter can recover ordinary numbers, booleans and nulls — but domain-specific IDs still deserve explicit handling.