topic Re: Import multiple sheets in excel in QlikView

Import multiple sheets in excel

Wed, 03 Jul 2013 06:24:35 GMT

I have an excel that creates a new sheet every week with data.

So for the year 2013, I have every week a sheet with the name 2013-01, 2013-02 and so on.

So the first difficulty is how to generate multiple sheets in Qlikview without creating a new load script for every sheet.

The second problem is that I'm working with a crosstable with the date:

ie underneath:

CrossTable(Date, Amount, 2)

Load

Product,

Supplier,

[24/06/2013],

[25/06/2013],

[26/06/2013],

[27/06/2013],

[28/06/2013]

From

[..\File.xls]

(biff, embedded labels, header is 1 lines, table is [2013-26]

The problem here is that the dates also change every week.

How can I implement these excels for the entire year of 2013?

Re: Import multiple sheets in excel

kaushiknsolanki — Wed, 03 Jul 2013 06:27:00 GMT

Hi,

Have a look at the below links.

http://community.qlik.com/thread/61585

http://community.qlik.com/message/229163

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

http://qlikviewnotes.blogspot.com/2008/09/loading-multiple-excel-sheets.html

Regards,

Kaushik Solanki

Re: Import multiple sheets in excel

Anonymous — Wed, 03 Jul 2013 08:16:10 GMT

Check the below code

for

each file in FileList('R:\Harsha\qlikview\*.xlsx');
ODBC CONNECT TO [Excel Files;DBQ=$(file)];
tables:
SQLtables;
DISCONNECT;
FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
Table:
Load *
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);
NEXT i
Drop table tables;
Next
file

Thanks

Harsha

Re: Import multiple sheets in excel

Thu, 21 Nov 2013 00:03:35 GMT

Hi Sriharsha,

This code is really great, but I get this error when I try to run. Any ideas?

Re: Import multiple sheets in excel

Thu, 06 Feb 2014 13:42:28 GMT

Try replacing CONNECT with CONNECT32.I think there is a driver issue when using the x64 version.