选自S preadsheet使用SqlBulkCopy [英] SqlBulkCopy from Spreadsheet
问题描述
我使用SqlBulkCopy的导入从A S preadsheet记录到一个SQL Server数据库。
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.
我在列映射定义的列：
bCopy.ColumnMappings.Add("Amount", "Amount");
在S preadsheet这是一个varchar，因为它是在我的工作表（我导入到一个工作表做一些验证之前，我将数据移动到我的直播表）。
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).
虽然我在测试中，我走进了一个记录，从数字FOO改变的值。然后我跑我的导入。
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.
我的验证例程正确地识别与列一个问题 - 但它是因为列为空，不会因为该值不是一个数字。当我检查了数据库，我发现这个领域进行了空值导入。我认为值应该是FOO。
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.
这令我有点古怪。
如果这有什么差别，我使用的OLEDB访问S preadsheet像一个数据库表并传递OLEDBDataReader作为我BulkCopy对象的源。
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.
是OLEDB驱动程序足够聪明，知道这是不是在该列的有效值？我以为一切都将是默认字符串/ VARCHAR。 （我们不做出在S preadsheet列数据类型的任何修改）
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)
修改
我检查DataReader的的建议 - 并且很奇怪，当我改变了金额字段有一个字符串值，DataReader的值为null。这是我第一次见过这种行为在Excel的DataReader。其他字符串字段工作得很好。
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.
推荐答案
我加入IMEX = 1到我的OLEDB连接字符串固定的问题。现在的值显示在DataReader的。 （我想我需要这样做是为了支持混合值列）。
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)
这篇关于选自S preadsheet使用SqlBulkCopy的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
