Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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.