Bad record error trapping

[tma1313]

Bruce,

Looking for some sample code to handle a bad csv record.

I know how to handle changing a "field", but I have a table with a
couple million records and I have just discovered that some (uncertain
how many) records are badly formatted.

How can I trap for bad csv rows and yet allow the remaining records to
import using bulkcopy?

John

[shriop]

The specific answer depends on just how bad the record actually is. If
the data is still parsable into columns, just maybe some required
fields are empty, you can wire up the ReadRecord event the same as you
would for modifying a field, do all your validation in that event
handler, and then set e.SkipRecord = true, which will cause that
record to be skipped during the bulk load. You could optionally create
an exception file writer to write the bad values back out to a
separate file to send back to the data source for correction. If the
records are so bad, like maybe not even lining up with columns
properly and such, but still roughly a csv, you can do a preprocess
against the whole file using CsvReader and grab the values by name and
do the business validation there and field count checks etc. The
biggest advantage here is that CsvReader has a RawRecord property,
which I've recently realized I probably need to make available in the
ReadRecord event of CsvDataReader, that you can send back to the data
source as unparsable data. If it's not even really a parsable csv
file, then you'd almost want to go at it with a StreamReader and do
some kind of validation based on what you do know about good records.
If your data source is trully untrustable, you might almost want to do
all 3 in separate steps, but it really depends on how critical the
data is and how relevant you want the error messages to be.

Bruce Dunwiddie