Solved: load all the sheets from XLSX - Qlik Community

thierrytt1102 · ‎2011-11-09

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)]);

Report Inappropriate Content · ‎2011-11-10

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.

View solution in original post

SunilChauhan · ‎2011-11-09

please the link below

http://community.qlik.com/message/108384#108384

hope this help

Sunil Chauhan

thierrytt1102 · ‎2011-11-09

Hi, it actualy helps for a XLS, not an XLSX.

regards

Report Inappropriate Content · ‎2011-11-09

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

thierrytt1102 · ‎2011-11-09

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!

Report Inappropriate Content · ‎2011-11-09

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

thierrytt1102 · ‎2011-11-09

Here is my file, I tried to change the extension but nothing happens 😕

thank you very much

Report Inappropriate Content · ‎2011-11-09

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

thierrytt1102 · ‎2011-11-09

Hi,

I'm using version 10.00.9061.7.

Thank you

Report Inappropriate Content · ‎2011-11-10

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