ACE.OLEDB.12的替代品，用于查询Excel数据表 [英] Alternative to ACE.OLEDB.12 for querying Excel data tables

问题描述

我当前正在使用ACE.OLEDB.12来查询Excel数据表，并且遇到了令人沮丧的提供程序限制.提供程序只能处理前256列和65536行，因此，如果我将任何数据表放置在该窗口之外，则引擎会返回错误消息找不到范围.

I am currently using ACE.OLEDB.12 for querying Excel data tables and I encountered a limitation with the provider that is frustrating. The provider can only address the first 256 columns and 65536 rows so if I position any of my data tables outside that window the engine comes back with an error message of range not found.

有人知道其他任何数据提供程序，这些数据提供程序将允许我使用ACE.OLEDB.12直接从数据表中查询数据，例如以下示例?

Does anyone know of any other data provider that will allow me to query data directly from data tables like the example below using ACE.OLEDB.12?

SELECT * FROM [Main Sheet$IG7:IU9]

推荐答案

我一直使用ADODB并遇到相同的问题.寻找无处不在的答案，甚至在SO上，都没有运气.

I use ADODB all the time and have the same issue. Looked for an answer everywhere, even on SO, with no luck.

仅 的解决方法:将数据移动到工作表的开头.如果您的数据以A1开头，例如Sheet1，只要您仅引用工作表(即SELECT * FROM [Sheet1$])，您的查询就会提取其他行和列.我尚未使用ACE.OLEDB对此进行过测试，但它确实可以与ADODB一起使用.

The only workaround I found: move your data to the beginning of the sheet. If your data starts at A1 in e.g. Sheet1, your query will pull in the additional rows and columns as long as you reference the sheet only, i.e. SELECT * FROM [Sheet1$]. I haven't tested this with ACE.OLEDB but it sure works with ADODB.

这篇关于ACE.OLEDB.12的替代品，用于查询Excel数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！