PDA

View Full Version : Is querying beyond 255 columns dead?



stanl
02-17-2013, 07:03 AM
I have reviewed the major threads as to using either the Ace Provider or Excel Driver to query Excel columns over 255 into a table or recordset. The latest in this issue was dated August 2012.

Just tried this with 2013 using the attached sample with 300 columns. Using an ADO connection with either

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & cXLS & ";Extended Properties=Excel 12.0;"

or

"Provider=MSDASQL;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & cXLS

with the SQL of "SELECT * from [Sheet1$A1:KN10];"

I only get 255 rows of data.

I understand Excel is NOT a database manager, but why build it up to 1 million rows and a thousand columns if it canot be imported i

Aflatoon
02-18-2013, 04:46 AM
The primary purpose of Excel is not importing data from elsewhere or exporting it, so the size of the 'Big Grid' is unrelated to that.

stanl
02-18-2013, 11:34 AM
The primary purpose of Excel is not importing data from elsewhere or exporting it, so the size of the 'Big Grid' is unrelated to that.

Figured it out. Have to use SQL Server (Express works fine) as Access is limited to 255 fields. 3-step process (1) formulate a CREATE TABLE sql from Excel Headers and data; (2) export Excel data as .csv; (3) Create table from sql and use Bulk Insert

If you are not part of the solution you are part of the problem