Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dears,
The code below shows how to load all the sheets of a XLS file. Unfortunately, I can't make the same for XLSX files. Do you see where is the problem?
Thank you very much!
CONNECT TO [PROVIDER=MSDASQL; Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:\Prod\file.xls];
tables:
SQLtables;
DISCONNECT;
FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));
AuditBasic:
CrossTable(Datum, docnum, 14)
LOAD *
FROM C:\Prod\file.xls (biff, embedded labels, header is 1 lines, table is [$(sheetName)]);
NEXT
DROP TABLE tables;
Hi.
It seems that the sentence "DISCONNECT" don't "diconnect" all; if you put another CONNECT sentence to another data source, it work:
OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=pruebas;DBQ=C:\Temp\AuditPartners.xlsx;DefaultDir=C:\Performer\Temp;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;"];
tables:
SQLtables;
DISCONNECT;
OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Data Source=Drop;Extended
Properties="DSN=Drop;DBQ=C:\Temp\Drop.xlsx;DefaultDir=C:\Performer\Temp;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;"];
FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));
LET sheetName = Left('$(sheetName)',Len('$(sheetName)') -1);
AuditBasic:
LOAD *
FROM C:\Temp\AuditPartners.xlsx (ooxml, embedded labels, header is 1 lines, table is [$(sheetName)]);
NEXT
DROP TABLE tables;
Regards.
Hi, it actualy helps for a XLS, not an XLSX.
regards
Hi,
XLS is biff file format whereas XLSX is XML file format hence I think if you change biff to ooxml it should work.
Try it by changing like below
LOAD * FROM C:\Prod\file.xlsx (ooxml, embedded labels, header is 1 lines, table is [$(sheetName)]);
Regards,
Sajeevan
it sounds good but what about the connection?
CONNECT TO [PROVIDER=MSDASQL; Driver={Microsoft Excel Driver (*.xls)}; DBQ=C:\Prod\file.xls];
If I replace xls by xlsx, it doesn't work,
Regards and thx!
Hi,
I think you just needs to change the file.xls as file.xlsx. Can you please attach the xlsx file for me to try it out?
Thanks,
Sajeevan
Here is my file, I tried to change the extension but nothing happens 😕
thank you very much
Hi,
I am also getting error. It is not working as I expected. I will keep trying and let you know. Which version of QV are you using?
Thanks,
Sajeevan
Hi,
I'm using version 10.00.9061.7.
Thank you
Hi,
If I am saving the Excel file in office 2003 format, it is working. I don't have office 2007 hence can't debug it on XLSX file - it may require office 2007 ODBC connections. The number of qualifying fields (as per your crosstable) are not 14 in the Excel file you attached.
Do you have office 2007 installed on your PC?
Thanks,
Sajeevan