Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

Import multiple sheets in excel

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?

4 Replies

Re: Import multiple sheets in excel

sriharsha96
Contributor II

Re: Import multiple sheets in excel

Hi

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

Not applicable

Re: Import multiple sheets in excel

Hi Sriharsha,

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

Capture.PNG.png

Not applicable

Re: Import multiple sheets in excel

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