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