The Ultimate 12-Step CSV Cleaning Checklist
Follow these 12 rigorous steps to audit and clean any CSV file locally before loading it into PostgreSQL, Snowflake, BigQuery, or Excel.
- Verify UTF-8 encoding and inspect byte order marks (BOM).
- Validate uniform column count across every row to detect unescaped delimiters.
- Strip leading and trailing whitespace from text fields.
- Standardize date formats to ISO 8601 (YYYY-MM-DD).
- Remove exact and fuzzy duplicate records.
- Audit numerical columns for illegal string characters.
- Enforce consistent casing across categorical fields.
- Mask sensitive PII fields (emails, phone numbers, credit cards).
- Verify boolean values (TRUE/FALSE, 1/0).
- Identify and handle missing or null values systematically.
- Validate primary key uniqueness.
- Export sanitized file and verify row count checksums.