A Pre-Import Checklist for Cleaning CSV Files

A CSV file can open neatly in a spreadsheet and still be unsafe to import. Duplicate headers, invisible whitespace, altered identifiers, mixed date formats, encoding damage, and unexpected delimiters may not become obvious until rows are rejected or—worse—accepted into the wrong fields. A short inspection before import is cheaper than cleaning a live system afterward.

Work on a copy and keep the untouched export. Load the copy into the utilkit CSV Cleaner to inspect columns and normalize common issues. If the file contains personal, financial, or regulated data, follow your organization’s handling rules and confirm any tool is approved before opening it.

Confirm the file structure

Check the delimiter, quoting, and line endings. Commas inside a field must be quoted; quotation marks inside a quoted field require escaping. RFC 4180 documents a widely used CSV format, but real exports vary, so the importer’s expectations remain the final authority.

Ensure there is exactly one header row, every header is unique, and no header is blank. Compare the column count across rows. A stray delimiter can shift every subsequent value to the wrong field while leaving the row visually plausible.

Protect identifiers from spreadsheet conversion

Account numbers, postal codes, SKU values, and phone-like identifiers are text even when they contain only digits. Spreadsheet software may remove leading zeros, display long values in scientific notation, or convert strings that resemble dates. Inspect the raw text file and explicitly map these columns as text.

Normalize dates to the format required by the destination and verify the timezone for timestamps. Values such as 03/04/27 are ambiguous across locales. An ISO-style date such as 2027-04-03 is easier to understand, but the destination still needs an agreed interpretation.

Clean content deliberately

Trim leading and trailing whitespace, normalize line breaks, and decide whether blank, NULL, N/A, and zero mean different things. Standardize categorical values against an allowed list rather than merely changing capitalization. “Active,” “active ” and “ACTIVE” may represent the same state, while “unknown” and blank may not.

Define the key used to detect duplicates. Two identical rows are easy to spot, but two rows for the same customer with different timestamps require a business rule: keep the newest, merge fields, or reject both for review. Never delete duplicates until that rule is explicit.

Validate before the full import

1. Record row and column counts for the original file.
2. Check required fields, value ranges, and allowed categories.
3. Review samples from the beginning, middle, and end.
4. Import a small batch into a staging or test environment.
5. Compare accepted, rejected, and changed row counts.
6. Retain the original, cleaned file, rules, and import report.

Create a reconciliation report for every cleanup

A cleaned file should travel with a short report that explains what happened. Record the original filename and checksum, row and column counts before and after, encoding, delimiter, header changes, normalization rules, duplicates removed, invalid rows quarantined, and the tool or script version used. This turns an edited export into a reproducible data product rather than an unexplained attachment.

Separate rejected records from silently dropped records. A quarantine file should preserve the original row, a stable row number or source identifier, and a specific reason such as “invalid date,” “missing customer ID,” or “unexpected column count.” Send that file to the data owner for correction. Do not invent a value merely to satisfy an import constraint unless a documented business rule authorizes it.

After the test import, reconcile more than the total row count. Compare distinct identifiers, sums of important numeric fields, null counts, and a sample from the first, middle, and last part of the source. If the destination deduplicates, transforms time zones, or truncates text, include those expected differences in the report. The import is complete when every material difference is either corrected or explained.

A clean-looking file is not the same as a validated file. The goal is a traceable transformation: you know what changed, why it changed, and whether the destination interpreted it as intended. That turns CSV cleanup from a last-minute formatting task into a controlled part of the data workflow.