Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

thierrytt
New Contributor III

load all the sheets from XLSX

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; 


					
				
			
			
				
			
			
				
Tags (4)
1 Solution

Accepted Solutions
Not applicable

load all the sheets from XLSX

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.

17 Replies
chauhans85
Esteemed Contributor

load all the sheets from XLSX

please the link below

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

hope this help

thierrytt
New Contributor III

load all the sheets from XLSX

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

regards

Not applicable

load all the sheets from XLSX

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

thierrytt
New Contributor III

load all the sheets from XLSX

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!

Not applicable

load all the sheets from XLSX

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

thierrytt
New Contributor III

Re: load all the sheets from XLSX

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

thank you very much

Not applicable

Re: load all the sheets from XLSX

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

thierrytt
New Contributor III

load all the sheets from XLSX

Hi,

I'm using version 10.00.9061.7.

Thank you

Not applicable

load all the sheets from XLSX

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

Community Browser