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.

  1. Verify UTF-8 encoding and inspect byte order marks (BOM).
  2. Validate uniform column count across every row to detect unescaped delimiters.
  3. Strip leading and trailing whitespace from text fields.
  4. Standardize date formats to ISO 8601 (YYYY-MM-DD).
  5. Remove exact and fuzzy duplicate records.
  6. Audit numerical columns for illegal string characters.
  7. Enforce consistent casing across categorical fields.
  8. Mask sensitive PII fields (emails, phone numbers, credit cards).
  9. Verify boolean values (TRUE/FALSE, 1/0).
  10. Identify and handle missing or null values systematically.
  11. Validate primary key uniqueness.
  12. Export sanitized file and verify row count checksums.