Validate before the import, not after
A partner sends you a 4,200-row customer file. It opens cleanly. The headers look right. You import it. Two weeks later, shipping flags 180 orders that can't be rated, and you trace it back to Massachusetts postcodes: 01810 came in as 1810. Somewhere between their export and your import, a leading zero got treated as a rounding error.
Nothing in that file was corrupt. It passed every eyeball test. It failed because nobody wrote down what "valid" meant before the data moved.
That's the whole argument for CSV validation rules. Not elaborate governance, just a written list of things that must be true about a file, checked at the door. The checks take minutes. The investigation after the fact takes days, and it costs you something harder to recover: once a stakeholder learns that a report was built on bad data, they question the next three reports too.
The original version of this guide listed the rules. This one gives you the implementation for each: the formula, the pattern, or the command to run before anything touches production. Copy what applies, delete what doesn't, and keep the list in the same repo or folder as the import job so it's impossible to skip.
Structural rules: does this file have the right shape?
Structural checks run first because if they fail, nothing downstream is trustworthy. A file with a shifted column doesn't have bad values, it has values in the wrong fields, and every row-level check will happily pass while your phone numbers land in the address column.
Three things to check before you look at a single value: consistent column count, exact header names, and encoding.
Column count is the fastest signal of a quoting problem. If a description field contains an unescaped comma, that row gains a column. Run this and expect exactly one number back:
- Field count per row: `awk -F',' '{print NF}' file.csv | sort | uniq -c` (good enough for files without quoted commas; if you use quoted fields, count with a proper CSV reader instead)
- Header names, exact and in order: `head -1 file.csv` and compare against a stored expected string
- Header drift in a spreadsheet: `=TEXTJOIN(",",TRUE,A1:AZ1)` in a helper cell, compared to your expected header string with `=EXACT(...)`
- Invisible whitespace in headers: `=SUMPRODUCT(--(TRIM(A1:AZ1)<>A1:AZ1))` should return 0
- Encoding: `file -I file.csv` should report `charset=utf-8`
- Byte order mark on the first header: `head -c 3 file.csv | xxd` showing `efbb bf` means your first column name is secretly `\ufeffcustomer_id`
- Mojibake sweep: search the file for `Ã`, `â€`, or ``. Any hit means a UTF-8 file was read as Latin-1 somewhere upstream.
Type rules: dates, numbers, booleans, and the leading zero problem
A CSV has no types. Every value is text until something decides otherwise, and that something is usually a spreadsheet making a guess on your behalf. Type rules exist to make the guess explicit.
Dates are the worst offender because 01/02/2024 is genuinely two different dates depending on who exported it. Enforce ISO 8601 (YYYY-MM-DD) and the ambiguity disappears. If you can't control the source format, at least flag the rows where the guess matters.
Numbers break in quieter ways. "1,234" and "$45.99" and "1.234,56" all look numeric to a human and read as text to a machine. Booleans are worse: one file uses TRUE/FALSE, the next uses Y/N, the next uses 1/0, and the fourth uses "yes" with a trailing space.
Leading zeros need a different kind of fix. You can't validate them back once they're gone, so the rule is procedural: never open a CSV containing postcodes, SKUs, or account numbers by double-clicking it. In Excel, use Data > From Text/CSV and set those columns to Text. In Google Sheets, use File > Import and turn off "Convert text to numbers, dates, and formulas." Then check that the lengths survived.
- ISO date pattern: `^\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])$`
- Date converts cleanly: `=ISNUMBER(DATEVALUE(A2))`
- Ambiguous day/month flag: `=AND(VALUE(LEFT(A2,2))<=12,VALUE(MID(A2,4,2))<=12)` returns TRUE where the date could be read either way
- Plausible date window: `=AND(A2>=DATE(2015,1,1),A2<=TODAY())` for anything representing a past event
- Number stored as text: `=AND(ISTEXT(A2),ISNUMBER(VALUE(SUBSTITUTE(SUBSTITUTE(A2,",",""),"$",""))))` returns TRUE for the rows to fix
- Strict decimal pattern: `^-?\d+(\.\d{1,2})?$` for currency with two places
- Boolean allowed list: `=ISNUMBER(MATCH(LOWER(TRIM(A2)),{"true";"false";"y";"n";"1";"0"},0))`
- Leading zero survived: `=LEN(A2)=5` for US postcodes, `=LEN(A2)=LEN(TEXT(A2,"00000"))` to catch silent conversion
Format rules: email, phone, postcode, country
Format rules check that a value looks like the thing it claims to be. They're the easiest rules to write and the easiest to over-engineer. The goal is to catch real breakage, not to win an argument about what the email specification technically permits.
For email, a simple structural pattern plus a domain sanity check catches nearly everything that matters. Elaborate patterns reject legitimate addresses and make you the person who told a customer their email was invalid.
For phone numbers, stop validating the formatting and start counting digits. Formatting varies by country and by how bored the person entering it was. Digit count is the honest check.
Postcodes and countries both need a pattern plus a membership check. A two-letter country code that matches the pattern but isn't a real ISO code ("UK" instead of "GB" is the classic) will pass a pattern test and fail at your shipping provider.
- Email, practical pattern: `^[^@\s]+@[^@\s]+\.[A-Za-z]{2,}$`
- Email, spreadsheet version: `=AND(ISNUMBER(FIND("@",A2)),ISNUMBER(FIND(".",MID(A2,FIND("@",A2),LEN(A2)))),ISERROR(FIND(" ",A2)))`
- Email, extra flags worth raising: role addresses (info@, sales@, noreply@), duplicate domains with 400+ rows each (someone pasted a placeholder), and consecutive dots
- Phone digit count: `=SUMPRODUCT(--ISNUMBER(VALUE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))))` should land between 7 and 15
- Phone, obvious fakes to reject outright: `0000000000`, `1234567890`, `5555555555`, and any value where all digits are identical
- US postcode: `^\d{5}(-\d{4})?$`
- UK postcode: `^[A-Z]{1,2}\d[A-Z\d]?\s?\d[A-Z]{2}$`
- Canadian postcode: `^[A-Z]\d[A-Z]\s?\d[A-Z]\d$`
- Country code shape: `^[A-Z]{2}$`, then membership: `=ISNUMBER(MATCH(A2,iso_codes,0))` against a pasted ISO 3166-1 alpha-2 list
- Country/postcode agreement: if country is `US`, the postcode must match the US pattern. Mismatches here almost always mean two sources were stacked into one file.
Referential rules: IDs that have to exist somewhere else
Format rules tell you a value is well formed. Referential rules tell you it's real. An order row with `customer_id` of `CU-88213` is perfectly valid text and completely useless if no customer `CU-88213` exists.
Check every foreign key against its reference list before import, not during. A database will reject the row for you, but by then you've got a half-loaded table and a decision to make under pressure.
The orphan rate matters more than the individual orphans. One missing ID out of 5,000 is a data entry slip. Four hundred missing IDs out of 5,000 means the two files came from different snapshots, or the reference export ran before the new records were created. That's a source problem, and cleaning the rows won't fix it.
- Row-level existence check: `=COUNTIF(reference!$A:$A,A2)>0`
- Orphan count for the whole file: `=SUMPRODUCT(--(COUNTIF(reference!$A:$A,A2:A5000)=0))`
- Orphan rate threshold: anything above 1% stops the import and goes back to the source
- Case and whitespace mismatch (the most common false orphan): `=EXACT(TRIM(A2),TRIM(VLOOKUP(A2,reference!$A:$A,1,FALSE)))`
- Duplicate primary keys in the incoming file: `=COUNTIF($A:$A,A2)>1`
- Self-referencing IDs: a parent_id must exist in the same file's id column, and must not equal its own row's id
Business rules: ranges, required-if, and mutual exclusivity
Business rules are the ones nobody else can write for you. They encode what your organization knows to be true, and they catch the errors that are technically valid and operationally absurd. A discount of 140% is a number. An order with a ship date before its order date is two perfectly good dates.
Three shapes cover most of them. Ranges set bounds. Required-if makes a field mandatory based on another field's value. Mutual exclusivity says two fields can't both be populated, or can't both be empty.
Write each one as a sentence first, in plain English, then as a check. "If country is US, state must be a valid two-letter code" becomes a formula in about thirty seconds. The sentence is the part that survives when someone rewrites the implementation.
Add dataset-level rules while you're here. Row count bounds catch truncated exports, which are invisible at the row level because every remaining row is fine. A file that normally has 4,000 to 6,000 rows and arrives with 212 is telling you the export timed out.
- Numeric range: `=AND(ISNUMBER(A2),A2>=0,A2<=120)`
- Percentage: `=AND(A2>=0,A2<=100)`
- Price, allowing credits only where you handle them: `=IF(B2="credit",TRUE,A2>=0)`
- Date ordering: `=A2<=B2` for start versus end, order versus ship
- Required-if: `=IF(LOWER(TRIM(B2))="us",C2<>"",TRUE)`
- Mutual exclusivity (exactly one of two): `=((A2<>"")+(B2<>""))=1`
- At least one of three contact methods: `=((A2<>"")+(B2<>"")+(C2<>""))>=1`
- Allowed values: `=ISNUMBER(MATCH(LOWER(TRIM(A2)),{"active";"inactive";"pending"},0))`
- Row count bounds: `=AND(COUNTA(A:A)-1>=4000,COUNTA(A:A)-1<=6000)`
- Null rate on a critical column: `=COUNTBLANK(C2:C6000)/COUNTA($A$2:$A$6000)` should stay under your agreed threshold
- Cardinality sanity: a status column with 47 distinct values means free text got in
Reject, quarantine, or auto-fix: deciding once instead of every time
Every failed check needs a predetermined response. If you decide in the moment, you'll decide differently on a Tuesday than on a Friday afternoon before a launch, and the inconsistency is worse than either choice.
Reject the whole file when the failure is structural. Ragged column counts, wrong encoding, missing required headers, a row count outside bounds, an orphan rate above your threshold. These mean you can't trust any row, and no amount of row-level cleaning recovers that. Send it back with the specific check that failed.
Quarantine individual rows when the file is sound but some records aren't. Missing required fields, orphan IDs, values outside business ranges, failed country and postcode agreement. Quarantined rows go to a separate file with a reason column, the good rows import, and someone owns the follow-up. The key discipline is that quarantine has an owner and a deadline. Otherwise it's a folder where data goes to be forgotten.
Auto-fix only when the correction is deterministic, reversible, and invents nothing. Trimming whitespace qualifies. Uppercasing a country code qualifies. Converting 03/15/2024 to 2024-03-15 qualifies, because the month is unambiguous. Guessing whether 01/02/2024 is January or February does not. Neither does picking a winner between two conflicting email addresses on the same record. Log every auto-fix with the before value, always.
- Reject: encoding, headers, ragged rows, row count, orphan rate above threshold, duplicate primary keys above threshold
- Quarantine: missing required fields, single orphan IDs, out-of-range values, failed required-if rules, unparsed dates
- Auto-fix: whitespace, letter case on codes and enumerations, unambiguous date reformatting, known country name to ISO code, stripping currency symbols and thousands separators
- Never auto-fix: ambiguous dates, inferred area codes, invented postcodes, choosing between conflicting values on the same record
When validation passed and the data was still wrong
This is the part that sends people looking for answers at 11pm. Every check went green and the import still produced nonsense. Four causes account for almost all of it.
You validated the wrong artifact. Someone opened the CSV in a spreadsheet to "take a quick look," it saved, and the leading zeros and dates were converted before your checks ran. Validate the file as delivered, not the version that's been through a spreadsheet. Compare file hashes if you need proof.
You validated a sample. Checks run on the first 1,000 rows will miss the source system switching date formats at row 3,400, which is exactly where the backfilled historical records start. Run checks on the whole file or on a random sample, never the top of it.
You checked format and not meaning. An empty cell, the string "NULL", the string "N/A", and a single space are four different values and only one of them is actually empty. Add `=OR(A2="",TRIM(A2)="",UPPER(TRIM(A2))="NULL",UPPER(TRIM(A2))="N/A",UPPER(TRIM(A2))="NONE")` to your required-field check and watch your completeness numbers change.
Or you validated the file but not the mapping. The file was perfect. Column 7 landed in the wrong destination field. Validate after mapping too, on a ten-row test import, before the full run.
Validate your next import automatically
Hand-run csv validation rules work right up until the week you're busy, and then they quietly stop happening. That's not a discipline failure, it's what manual checks do. The fix is to make validation part of the job rather than a thing you remember to do first.
CleanSmart runs these checks on upload. Import a CSV, or use DataBridge to read records straight out of Shopify, Klaviyo, Mailchimp, HubSpot, or Salesforce. AutoFormat standardizes dates, country codes, phone formats, and casing. LogicGuard flags the anomalies: out-of-range values, impossible date orders, columns that suddenly changed shape. SmartMatch resolves duplicate records, SmartFill closes the gaps it can close honestly, and you review everything before it goes anywhere.
After each cleaning run, which takes a few minutes, your Clarity Score is recalculated across four sub-scores: Completeness, Consistency, Uniqueness, and Validity. It's the number you can show a stakeholder when they ask whether the file is good enough to trust.
Then you export. Results come back as a CSV or JSON file plus a change log of every correction made, so you can review the edits and import them into your own platform on your own schedule. Nothing gets changed inside your connected tools without you doing it deliberately.
Try it on the last file that broke something. The self-serve product demo runs in your browser with sample data, no setup and no sales call, and you'll see the failed checks laid out before you decide what to fix.
Frequently Asked Questions
Where should CSV validation rules live, in the file check or the database?
Both, but they do different jobs. Pre-import checks give you a readable report and let you decide what to reject, quarantine, or fix, while database constraints (NOT NULL, CHECK, unique indexes, foreign keys) are your last line of defense against anything that slipped past. If you only have time for one, start with the pre-import check, because a failed database constraint mid-load leaves you with a half-loaded table.
How strict should my validation rules be?
Start stricter than feels comfortable and loosen the rules that generate false positives in the first month. It's easy to relax a rule that's blocking good data and painful to tighten one after bad data has been flowing through for a quarter. For financial, healthcare, or anything with a regulator attached, stay strict.
Should I validate files that come from our own internal systems?
Yes, and often those files deserve more scrutiny, not less, because nobody questions them. Internal exports get truncated, scheduled jobs fail quietly, and someone changes a field definition without telling anyone downstream. The checks take minutes and they don't care where the file came from.