C# OLEDB 连接字符串中的 IMEX 是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10102149/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is IMEX within OLEDB connection strings?
提问by Dimitar Tsonev
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=localhost;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=localhost;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"
What is the purpose of IMEX=2in the above connection string?
IMEX=2上述连接字符串的目的是什么？
采纳答案by Steve
From ConnectionStrings
来自连接字符串
"If you want to read the column headers into the result set (using HDR=NO even though there is a header) and the column data is numeric, use IMEX=1 to avoid crash.
To always use IMEX=1 is a safer way to retrieve data for mixed data columns. .."
“如果要将列标题读入结果集中（即使有标题，也使用 HDR=NO）并且列数据是数字，请使用 IMEX=1 以避免崩溃。
始终使用 IMEX=1 是一种更安全的方式来检索混合数据列的数据。..”
Please note that the IMEX value can be very important when you need to write back data to the Excel. A fast search on Internet on IMEX found numerous articles about problems with various IMEX values
请注意，当您需要将数据写回 Excel 时，IMEX 值可能非常重要。在互联网上快速搜索 IMEX 发现了大量关于各种 IMEX 值问题的文章
回答by Vishal Kotak
When you are reading an excel file into a data table, the data table reads the column values and after about 8-10 records, it will assign data type to a column. For example if the column values are
11.0
22.0
33.0
44.0
55.0
66.0
77.0
88.0
99.0
abc
So, the data table won't have the value abc as the column has been assigned the data type "Double". To avoid this, and to read complete data IMEX = 1 is used.
Please, comment for further queries.
当您将excel文件读入数据表时，数据表读取列值，大约8-10条记录后，它将为列分配数据类型。例如，如果列值为
11.0
22.0
33.0
44.0
55.0
66.0
77.0
88.0
99.0
abc
因此，数据表将不具有值 abc，因为该列已分配数据类型“Double”。为避免这种情况并读取完整数据，使用 IMEX = 1。
请评论以进一步查询。
回答by Raky
Please use a generalised function in a Module...
请在模块中使用通用函数...
Public Function DeleteBlankRowsfromDataset(ByRef Dtset As DataSet) As Boolean
Try
Dtset.Tables(0).AsEnumerable().Where(Function(row) row.ItemArray.All(Function(field) field Is Nothing Or field Is DBNull.Value Or field.Equals(""))).ToList().ForEach(Sub(row) row.Delete())
Dtset.Tables(0).AcceptChanges()
DeleteBlankRowsfromDataset = True
Catch ex As Exception
MsgBox("Deleting Blank Records in Dataset Failed")
DeleteBlankRowsfromDataset = False
End Try
End Function
回答by Magnus
There is a potential problem when reading Excel files with an OleDbConnection.
使用 OleDbConnection 读取 Excel 文件时存在潜在问题。
If you use
如果你使用
"Extended Properties='Excel 8.0;HDR=NO;IMEX=3;'"
for a column like the following where the first 8 rows have 4 (or more) numeric values, then the type is considered to be numeric and the string values are read as null.
对于如下所示的列，其中前 8 行有 4 个（或更多）数值，则类型被视为数值，字符串值读取为空。
Notice that the header is not used as a header here (HDR=NO) so the "zipcode" row is the first row. (These zip codes are from Sweden in case you don't recognize their format.)
请注意，此处未将标题用作标题 (HDR=NO)，因此“邮政编码”行是第一行。（这些邮政编码来自瑞典，以防您不认识它们的格式。）
1) zipcode
2) 125 45
3) 115 50
4) 18735
5) 11335
6) 13940
7) 181 55
8) 11759
9) 176 74
10) 137 38
But if you data looks like this, where only 3 are numeric of the first 8 rows
但是，如果您的数据看起来像这样，其中前 8 行中只有 3 个是数字
1) zipcode
2) 125 45
3) 115 50
4) 18735
5) 11335
6) 139 40 <-- This one changed so that it is a string
7) 181 55
8) 11759
9) 176 74
10) 137 38
then it works, it reads it all, as strings.
然后它工作，它读取所有，作为字符串。
So the first case is a problem. But there is a solution.
所以第一种情况是有问题的。但是有一个解决方案。
Let's say you use
假设你使用
"Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'"
where we have changed IMEX to 1 and HDR to YES, then it will read the data as strings in both cases above. But let's say we have data like this
在我们将 IMEX 更改为 1 并将 HDR 更改为 YES 的情况下，它将在上述两种情况下将数据作为字符串读取。但是假设我们有这样的数据
1) zipcode
2) 12545
3) 11550
4) 18735
5) 11335
6) 13940
7) 18155
8) 11759
9) 17674
10) 137 38
then all of the first 8 data rows are numeric and then it again fails, even though we have IMEX=1.
然后所有前 8 个数据行都是数字，然后它再次失败，即使我们有 IMEX=1。
You can solve this problem in the following way. Change the connection string to this
您可以通过以下方式解决此问题。将连接字符串更改为此
"Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'"
Notice that we kept IMEX=1 but changed back HDR to NO.
请注意，我们保持 IMEX=1 但将 HDR 改回 NO。
Now row 1 is not treated as a header anymore and "zipcode" is read as data and since it is clearly a string, all rows are read as strings (that's how IMEX=1 works).
现在第 1 行不再被视为标题，并且“邮政编码”被读取为数据，并且由于它显然是一个字符串，因此所有行都被读取为字符串（这就是 IMEX=1 的工作方式）。
There are a couple of drawbacks with this method. Both can be solved:
这种方法有几个缺点。两者都可以解决：
1) You can't refer to the column by its name (zipcode) but need to use for example F7 depending on where the column is located.
1) 您不能通过名称（邮政编码）引用列，但需要根据列所在的位置使用例如 F7。
You can solve this by figuring out where the zipcode column is located (can be done programmatically) and change the SQL text accordingly by changing "zipcode" to for example "F7".
您可以通过确定邮政编码列的位置（可以通过编程方式完成）并通过将“邮政编码”更改为例如“F7”来相应地更改 SQL 文本来解决此问题。
2) The value "zipcode" will appear in your data.
2) 值“邮政编码”将出现在您的数据中。
This can be solved by having F7<>'zipcode' in your where clause. One might think that this would counteract the fact that we included zipcode (being a string) to make sure all rows are treated as strings. After having tested this it turns out though that the where clause trick that excludes "zipcode" does not have such a counteracting effect.
这可以通过在 where 子句中使用 F7<>'zipcode' 来解决。有人可能认为这会抵消我们包含邮政编码（作为字符串）以确保所有行都被视为字符串的事实。对此进行测试后，结果证明排除“邮政编码”的 where 子句技巧并没有这样的抵消效果。
