The Danger of Dirty Imports

Importing unscrubbed data into a production database is one of the most common ways to cause silent corruption. A single stray comma, an unescaped quote, or a mixed date format can cascade into hundreds of broken queries, inaccurate reports, and frustrated users. Before you click "Import," your data needs to be sanitized.

1. Standardize Your Delimiters and Encodings

CSV stands for Comma-Separated Values, but in practice, you'll encounter tabs, semicolons, and pipes. Even worse is encoding: a file saved in Windows-1252 but imported as UTF-8 will turn accented characters into unreadable symbols (mojibake). Always verify that your file is saved in strict UTF-8.

2. Trim Whitespace and Handle Line Breaks

A string like " John Doe " is not equal to "John Doe" in SQL. Trailing and leading spaces cause joins to fail and duplicates to slip through unique constraints. Additionally, rogue line breaks inside unquoted text cells will break the CSV parser entirely, causing columns to shift into the wrong rows.

3. Normalize Date Formats

Databases expect dates in strict ISO 8601 format (YYYY-MM-DD). If your CSV contains US formats (MM/DD/YYYY) mixed with EU formats (DD/MM/YYYY), your database might misinterpret January 2nd as February 1st without throwing an error. Always convert dates to ISO format before import.

4. Use a Data Profiler

Don't rely on scrolling through Excel. Use a data profiling tool (like the one built into DataScrub) to get a statistical summary of each column. A profiler will tell you instantly if a column that should be 100% numeric has text hiding in row 14,502.

By taking 5 minutes to scrub your CSV using a tool like DataScrub's Text Cleaner and Formatter, you can save hours of database rollback and cleanup work.