Add Custom Field not In CSV

[shriop]

Here's an answer I gave to someone that I thought might be useful to
others so I decided to copy it here.

Question:
Is there a way I can add a custom field to the csvDataReader object.
I need to add a GUID field, that won't be in the csv and that needs to
be added to each "row" of the csv. This GUID is the SAME for each
row. It's just a way for me to keep track of multiple imports that
may be sitting in my staging table at the same time.

I don't want to have to open the csv and add this in row by row as
some of these csv could be 100,000 rows or more.

Answer:
There's a really simple way to do what you're trying to do, and I
specifically had it included in my old code samples page, but thought
it was a little confusing to people so I took it out. Basically, you
should add a column to the Columns collection as if it did exist in
the file, but as the very last column. This will cause it to be read
in as an empty string before the casting stage. You'll also need to
add a corresponding entry in the bulk copy column mappings of course.
I've built in an inbetween layer between when the data is first read
in as just strings from the CsvReader component, and when it's cast/
parsed into the end data types. You can hook into that layer by
setting up a listener for the CsvDataReader.ReadRecord event that
fires for each record read from the file,
http://www.csvreader.com/csv/docs/DataStreams.Common.DataReaderBase.ReadRecord.html
. You can then access the column value by index or by name and set its
value to your upload specific guid. This same event can also be used
to perform data validation and skipping records that fail, or sending
them to an error log. You can also trim out unwanted characters or
manipulate the data however you want before it's casted.

The code would be something like this:

AddHandler csvData.ReadRecord, AddressOf AddGuid

...

Private Sub AddGuid(ByVal args As CsvDataReader.ReadRecordEventArgs)

args.Values.Item("guid") = uploadGuid.ToString()

End Sub

Bruce Dunwiddie

[jwcolby]

BTW, it appears that this is a common method of handling this. If you put a
custom named field at the front, even if the name does not match any in the
first line of header info, many programs will incorrectly place the first
col of data into your custom field, which causes all the data to be slewed
out of place. If you place such custom fields out at the end, then most
such programs just ignore them and they end up filled with nulls.

John W. Colby
Colby Consulting
www.ColbyConsulting.com