2

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.

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]
shA.t
  • 16,580
  • 5
  • 54
  • 111
Dimitris
  • 2,030
  • 3
  • 27
  • 45
  • Its strange that you have datasets of this size in Excel. Do you have the option to load data in a different format (i.e. text delimited or CSV). Also note that XLSX is simply a zipped up XML file so you could try and get the data yourself. – Nick.Mc Sep 19 '14 at 08:50
  • I have quite a few tables that I am using with a lot of columns and rows each one of them. I could read the data using VBA but I prefer the SQL route much cleaner. – Dimitris Sep 19 '14 at 08:54
  • I'm curious as to what kind of system stores that volume of data in an Excel file? Is Excel just a way to transfer data around or are you actually storing that much data in it? Excel has lots of other problems if being used as a transfer format. Text is much cleaner. – Nick.Mc Sep 19 '14 at 08:56
  • We do a lot of data pre-processing for call centres so inevitably we use a lot of data. There are client restrictions and limitations in terms of using a database so we build importation and preprocessing wizards in Excel. – Dimitris Sep 19 '14 at 09:02
  • I can only suggest you save the Excel file as CSV and import that instead. – Nick.Mc Sep 19 '14 at 09:04

2 Answers2

1

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

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.

Community
  • 1
  • 1
xificurC
  • 1,168
  • 1
  • 9
  • 17
  • I know, tell me about it. I 've been struggling to find a solution to this problem for a while. It's a disgrace that Microsoft hasn't fixed this issue and we are still struggling. I cannot put the data in the beginning of the sheet because the sheet is full of tables. Columns I can handle with multiple sheets but rows I cannot. – Dimitris Sep 19 '14 at 09:59
  • Well, the last resort is to copy your data in a new sheet and query that. This will hit performance though as your data is probably quite big. – xificurC Sep 19 '14 at 10:01
  • Performance wise its not too bad. The queries run really fast actually. The problem is the number of rows which might exceed 65536. If that happens the OLEDB provider says it cannot find the table anymore because it addresses only the first 256x65536, basically the old Excel limits. – Dimitris Sep 19 '14 at 10:06
  • That's not what I meant by performance hit. I meant if your data doesn't start at `A1` you can copy your data in a new sheet so it starts at `A1`, query that and then delete the temporary sheet. – xificurC Sep 19 '14 at 14:17
0

You can use ClosedXML to query XLSX files. Of course, it doesn't offer any SQL functionality (instead, it behaves similar to VBA), but has the advantage that neither Excel nor the AccessDatabaseEngine need to be installed on the target machine. Maybe it's worth a look.

Golvellius
  • 1,928
  • 2
  • 13
  • 16
  • I think if I go down the route of using code to query my data then I might as well use VBA. SQL is a lot more powerful for data manipulations. – Dimitris Sep 19 '14 at 09:57
  • Sorry, I totally overlooked the VBA tag. If you are working from within Excel anyway, my answer doesn't serve any use. – Golvellius Sep 19 '14 at 10:34