A CSV can be perfectly valid and still be wrong for the import that consumes it.
I've been thinking about this while working on data import validation.
The obvious failures are usually the easy ones: a broken delimiter, an unreadable file, a value that cannot be parsed.
The more worrying cases are the ones where nothing actually fails.
A supplier changes a column name. Two columns switch position. A key that used to be unique appears twice. Dates arrive in a different format.
The file opens. The parser is happy. In some cases the import completes too.
But the data in the database may no longer mean what you think it means.
I now treat structure as part of validation
Before looking at individual values, I want to know whether the file is still the file I expected.
A few basic checks catch a surprising number of problems:
- are the expected columns still there?
- did new or renamed columns appear?
- do source columns still map to the right target fields?
- are required values present?
- is the key actually unique?
- are dates and numbers using formats I expect?
Column mapping deserves particular attention.
If an import relies on position, a file with exactly the right number of columns can still be dangerous. Swapping two columns doesn't necessarily make the CSV invalid.
It just makes the mapping wrong.
Database errors come too late
Constraints are useful, of course, but I don't want the database to be the first place where I discover a bad row.
I'd rather know:
Row 184 is missing CUSTOMER_ID
while I'm inspecting the source than find out after generating SQL or starting an import.
The same goes for duplicate keys. If that key will later drive a MERGE or UPSERT, I want to know about duplicates before generating the statement.
Keep bad rows out of the next step
One approach I've found useful is to stop treating validation as a simple pass/fail result.
Instead, split the data into two sets:
- rows that passed the checks;
- rows that need attention, together with the reason.
Then SQL generation or export operates on the first set by default.
It sounds simple, but it makes the workflow much easier to reason about.
My pre-import checklist is now pretty small
For a recurring CSV or Excel import, I want to check at least:
- expected and unexpected columns;
- source-to-target mappings;
- required values;
- duplicate keys;
- accepted date and number formats;
- which rows failed and why.
Only after that do I want to generate INSERT, MERGE or UPSERT statements.
I've written up the longer version, including a few Oracle, SQL Server and PostgreSQL considerations, in the complete pre-import validation guide.
This is also the workflow I'm experimenting with in RowMend, a small local-first browser tool I'm building around CSV/Excel import validation.
I'm curious about other people's experience with recurring imports: what causes more trouble in practice for you — schema changes, mappings, duplicate keys, date/type conversions, or something else?
