How to Validate CSV Data Before Import
Updated
Importing a CSV without validating it first leads to rejected rows, silent data corruption and hours of cleanup. Running a few checks before you upload saves time and protects the data already in your system.
Why validation matters
CRMs and databases reject rows that don't meet their field requirements — but not always loudly. Some tools skip invalid rows silently, others import them with blank or mangled fields. A phone number in the email column, a future date in a birthdate field, or a required field left empty can cascade into broken automations, bad segments and undeliverable messages.
Validating before import also catches problems that the system might accept but shouldn't: duplicate records, outdated email domains and values that don't match dropdown options.
Common validation rules
| Field | Rule | Example of invalid data |
|---|---|---|
| Contains @ and a valid domain | john@, john@.com, john at example.com | |
| Phone | Digits only (after stripping formatting), correct length for the country | +91987654321 (one digit short), abc1234567 |
| Date | Valid date in a consistent format, not in the future for historical fields | 31/02/2024 (February 31), 13/25/2024 |
| Required field | Not empty, not just whitespace | Empty cells, cells with only spaces |
| Dropdown/picklist | Value matches one of the allowed options | 'United States' when the CRM expects 'US' |
| Number | Numeric value within expected range | 'twelve', negative values for quantity fields |
Validating manually in Excel
- Check for blanks in required columns: select the column, use Ctrl+G → Special → Blanks to highlight empty cells.
- Validate emails with a formula: =AND(ISERROR(FIND(" ",A2)),NOT(ISERROR(FIND("@",A2))),FIND(".",A2,FIND("@",A2))>0) returns TRUE for basic email validity.
- Use Data Validation (Data → Data Validation) to set rules on cells — for example, restrict a date column to dates between 1900-01-01 and today.
- Apply conditional formatting to highlight cells that fail validation: Home → Conditional Formatting → New Rule → Use a formula.
- Sort or filter the flagged cells and fix them before exporting the cleaned file.
Excel's Data Validation works well for preventing bad data entry, but it doesn't catch issues in data that was pasted or imported — it only blocks typed input. Conditional formatting highlights problems but doesn't prevent them from being exported.
Beyond format checks: data quality
- Consistency — is the same country spelled three different ways? Are some phone numbers with country codes and others without?
- Completeness — how many records are missing optional but important fields like company name or job title?
- Freshness — are there email addresses from defunct domains (aol.com addresses might be fine, but company domains that no longer resolve are a problem)?
- Uniqueness — are there duplicate keys (emails, IDs) that will create duplicate records in the target system?
Validate with TheSpreadFix
TheSpreadFix runs format, completeness and consistency checks on your CSV in one pass. It flags invalid emails, malformed phone numbers, missing required fields and inconsistent dropdown values. Each issue is shown in context so you can fix it in the tool or note it for correction in the source system. The file is processed in your browser and nothing is uploaded to a server.
Frequently asked questions
Should I validate every time I import?
Yes. Even if the source system hasn't changed, the data inside it has. New records may have been entered with inconsistent formats, and a field that was required last month might now have blank values.
What's the difference between validation and cleaning?
Validation identifies problems — it tells you what's wrong. Cleaning fixes them. In practice you do both: validate first to understand the scope of issues, then clean the data, then validate again to confirm everything passes.