有关 excel 文件的 OleDB 连接字符串的帮助
我遇到的问题是数据适配器仅查看每列中的第一行来确定数据类型。就我而言，第一列“SKU”是前 500 行的数字，然后我碰巧拥有混合数字和字母的 SKU。因此，最终发生的情况是 SKU 列中的行留空，但我仍然获得每个列行的其他信息。
我相信是连接字符串控制了它，并且根据我当前的设置，它应该可以工作，但事实并非如此。
连接字符串：
conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Nick\Desktop\Pricing2.xlsx" + @";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";
ImportMixedTypes=Text;TypeGuessRows=0
应该是重要的关键字，查看 0 行并仅使用文本作为所有内容的值类型。
我对此施加的“创可贴”是使电子表格中的第一行由字母和数字混合而成，并在我的查询中专门将该行排除在外。
The problem i'm having is that the data adapter is looking at only the first row in each column to determine the data type. In my case the first column "SKU" is numbers for the first 500 rows then I happen to have SKU's which are mixed numbers and letters. So what ends up happening is rows in the SKU column are left blank, but I still get the other information for each column row.
I believe it is the connection string that controls that and with my current settings it should work, however it is not.
Connection String:
conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Nick\Desktop\Pricing2.xlsx" + @";Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";
ImportMixedTypes=Text;TypeGuessRows=0
Should be the important keywords, look at 0 rows and just use text as the value types for everything.
The "bandaid" I have put on this is to make the first row in the spreadsheet a mixture of letters and numbers and specifically leave that row out in my query.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
遗憾的是，您无法从连接字符串中设置
ImportMixedTypes或TypeGuessRows，因为这些设置是在注册表中定义的。对于 ACE OleDb 驱动程序，它们存储在注册表中。因此，您可以将连接字符串简化为：
将
TypeGuessRows设置为0并将ImportMixedTypes设置为Text注册表，您应该得到您期望的行为。但是，如果您发现导入性能不太理想，则可以考虑使用适当大的数字（例如 1000）而不是零。Unfortunately, you can't set
ImportMixedTypesorTypeGuessRowsfrom the connection string since those settings are defined in the registry. For the ACE OleDb driver, they're stored atin the registry. So, you can simplify your connection string to:
Once you set
TypeGuessRowsto0andImportMixedTypestoTextin the registry, you should get the behavior you are expecting. You might, however, consider using a suitably large number like 1000 instead of zero if you find import performance to be less than ideal.