SqlBulkCopy from Spreadsheet

asp.net c# sqlbulkcopy

Question

I'm using SqlBulkCopy to import records from a spreadsheet into a SQL Server database.

The bulk copy operation works fine under most conditions. However it's doing something I can't get my head around.

I have a column defined in my column mappings:

bCopy.ColumnMappings.Add("Amount", "Amount");

In the spreadsheet this is a varchar, as it is in my work table (I import to a work table to do some validation before I move the data to my live table).

While I was testing, I went into a record and changed the value from a number to the word FOO. Then I ran my import.

My validation routine correctly identifies a problem with the column - but it's because the column is null, not because the value is not a number. When I checked the database, I found this field was imported with a null value. I thought the value should have been FOO.

This strikes me as kind of weird.

If it makes any difference, I'm using OLEDB to access the spreadsheet like a database table and passing the OLEDBDataReader in as the source in my BulkCopy object.

Is the OLEDB driver smart enough to realize this isn't a valid value in that column? I thought everything would be string/varchar by default. (We don't make any modifications to the column datatype in the spreadsheet)

EDIT

I've checked the dataReader as suggested - and strange enough, when I change the Amount field to have a string value, the value in the datareader is null. This is the first time I've seen this kind of behavior in an Excel datareader. Other string fields work just fine.

1
1
9/11/2013 11:36:10 AM

Accepted Answer

I fixed the issue by adding IMEX=1 to my OLEDB Connection string. Now the value is shown in the datareader. (I guess I need to do this to support mixed value columns)

1
9/11/2013 11:46:51 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow