SqlBulkCopy from Spreadsheet

asp.net c# sqlbulkcopy


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.)


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.

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)

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