topic load all the sheets from XLSX in QlikView

load all the sheets from XLSX

thierrytt1102 — Wed, 09 Nov 2011 11:22:10 GMT

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

DROP TABLE tables;

load all the sheets from XLSX

SunilChauhan — Wed, 09 Nov 2011 11:30:39 GMT

please the link below

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

hope this help

load all the sheets from XLSX

thierrytt1102 — Wed, 09 Nov 2011 11:39:11 GMT

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

regards

load all the sheets from XLSX

Wed, 09 Nov 2011 12:19:06 GMT

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

load all the sheets from XLSX

thierrytt1102 — Wed, 09 Nov 2011 12:48:28 GMT

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!

load all the sheets from XLSX

Wed, 09 Nov 2011 13:09:23 GMT

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

Re: load all the sheets from XLSX

thierrytt1102 — Wed, 09 Nov 2011 13:21:09 GMT

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

thank you very much

Re: load all the sheets from XLSX

Wed, 09 Nov 2011 14:01:50 GMT

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

load all the sheets from XLSX

thierrytt1102 — Wed, 09 Nov 2011 14:11:31 GMT

Hi,

I'm using version 10.00.9061.7.

Thank you

load all the sheets from XLSX

Thu, 10 Nov 2011 06:17:43 GMT

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

load all the sheets from XLSX

thierrytt1102 — Thu, 10 Nov 2011 07:47:06 GMT

Hi,

I have office 2007 installed and I deleted some columns so you have a simple file. 14 isn't good anymore.

I'll check for office 2007 ODBC connection.

Thank you for your help

load all the sheets from XLSX

Thu, 10 Nov 2011 11:00:51 GMT

Hi, Thierrytt.

With these statements (and Office 2007 installed), seems to work. I deleted the sentence "CrossTable" just to prove that the load process works OK.

Basically, change the type of access (as indicated by Sajeevan) to "ooxml" and cut the last "$" in the name of the table (I don't now why, but the name of the sheet in "tables" is with an extra "$").

OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Data Source=pruebas;Extended Properties="DSN=pruebas;DBQ=C:\Temp\AuditPartners.xlsx;DefaultDir=C:\Temp;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;"];
tables:
SQLtables;
DISCONNECT;

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;

I hop this helps you.

load all the sheets from XLSX

thierrytt1102 — Thu, 10 Nov 2011 14:11:13 GMT

hi,

I made the DSN like yours, it seems to be connected but I got that error:

AuditBasic:

LOAD *

FROM C:\Temp\AuditPartners.xlsx (ooxml, embedded labels, header is 1 lines, table is [project1])

Can't go further.

Does that depends on the driver?

Regards and thank you

load all the sheets from XLSX

Thu, 10 Nov 2011 14:22:28 GMT

Hi.

Perhaps is for the path. I wrote the path for testing in my PC ("C:\Temp\AuditPartners.xlsx").

¿Have you changed the path according with your environment?

Regards,

load all the sheets from XLSX

thierrytt1102 — Thu, 10 Nov 2011 14:24:12 GMT

Yes I did, and the connection seems to be OK.

The Load statement seems to be the problem

load all the sheets from XLSX

Thu, 10 Nov 2011 15:06:24 GMT

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

Regards.

load all the sheets from XLSX

thierrytt1102 — Thu, 10 Nov 2011 15:24:29 GMT

Dear,

Thank you for your great help... it's the first time I see such a thing. But I don't understand what is your Drop.xlsx. What's does that contain?

I made a copy paste of the original xlsx and it works fine now.

Thank you very much !!!

load all the sheets from XLSX

Thu, 10 Nov 2011 15:34:16 GMT

Hi.

No matter the content of de Drop.xlsx; just the fact of opening another connection. ¡In fact, I have done the same of you (copy-paste-rename of your original file)!.

Regards,