SqlBulkCopy from Spreadsheet

asp.net c# sqlbulkcopy

Question

I'm importing data from a spreadsheet into a SQL Server database using SqlBulkCopy.

In most cases, the bulk copy process is successful. But it's doing something that I can't comprehend.

In my column mappings, I've defined the following column:

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

This is a varchar in the spreadsheet, just 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).

I entered a record while I was testing and changed the value from a number to the word FOO. I then executed my import.

My validation procedure accurately detects an issue with the column, but it's not because the value is not an integer; rather, it's because the column is null. I discovered this column had been imported with a null value when I examined the database. I believed that FOO should have been the value.

I find this to be a little strange.

If it makes a difference, I'm handing the OLEDBDataReader into my BulkCopy object as the source and using OLEDB to view the spreadsheet as a database table.

Does the OLEDB driver have the intelligence to understand that this is an invalid value for that column? I assumed that by default, everything would be a string or varchar. (We don't change the spreadsheet's column datatype in any way.)

EDIT

I've verified the dataReader as indicated, and oddly enough, the value in the datareader is null when I update the Amount field to have a string value. This is the first instance of such behavior in an Excel datareader that I can recall. Other string fields function properly.

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

Accepted Answer

By adding IMEX=1 to my OLEDB Connection string, I was able to solve the problem. The value is now shown in the datareader. (I suppose I have to do this to support columns with mixed value)

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