Qlik Community

QlikView Documents

Documents for QlikView related information.

Load Multiple excel sheets using For loop

MVP & Luminary
MVP & Luminary

Load Multiple excel sheets using For loop

Hi All,

The script below loads the data into qlikview file for multiple sheets in a single load by using the Loop statements. 

LET vStartSheetNumber = 1;

LET vEndSheetNumber = 50;

LET vExcelFileName = 'Data';

// Generate Empty table

Data:

LOAD

          '' AS Data.Field1,

  '' AS Data.Field2

AutoGenerate(0);

FOR index = vStartSheetNumber TO vEndSheetNumber

     Concatenate(Data)

     LOAD

          *

     FROM [$(vExcelFileName).xlsx]

     (ooxml, embedded labels, header is 10 lines, table is [Page $(index)]);

NEXT

Note : in my excel file the sheet names are in the patter of Page 1, Page 2, ..... Page n.

Hope this helps others.

Regards,

Jagan.

Labels (1)
Comments
peterkjellberg
New Contributor

I am using this and it works very good when using LOAD * , i.e. loading all fields from all the sheets.

But my files do not have the same info on all sheets and the info on the sheets may vary.

I also only need some of the fields from the different files and would then like to use:

LOAD

     Field1,

    Field 2

   etc.

Here I get errors since it does not find the fields on some of the sheets.

Any idea on how to solve this?

0 Likes
MVP & Luminary
MVP & Luminary

Hi Peter,

Try like this

First load all tables using

Load

*

and then use DROP FIELDS to drop the fields which you do not required.

Regards,

Jagan.

0 Likes
ahaahaaha
Honored Contributor

Thanks for the useful information.

0 Likes
peterkjellberg
New Contributor

Thank you Jagan. This might be the solution I will end up using if I can't figure out a way to avoid having to load all of the fields.

There are two reasons I want to avoid loading all of the data from the files.

First off I only need a few of them in my application (~20 out of well over 100 fields).

Second reason is that the files are quite big and are starting to add up in numbers.

0 Likes
ishanbhatt
Contributor II

Hi Jagan,

Thank you for sharing this great trick. In My data, there are around 60 to 70 worksheets in one excel file and all names are in string format (ex..John, Mary, Yusuf). So, How can we load these sheets?

Many thanks in advanced.

0 Likes
arjunganisetti
New Contributor II

Thank you so much, it's very helpful

0 Likes
peterkjellberg
New Contributor

Hi

I have been trying to figure out a way to only load new files by using this script. After a lot of trial and error I came up with the script below. This works for all files except for the last, i.e. it loads through all the files without adding any data but it does load data from the last file even though it should not.

the MaxsystemTime used below is derived from a timestamp on one of the sheets in the excel files, i..e it does not exist on all sheets which stops me from using it in the WHERE clause (I think....)

NewData:

LOAD

  '' AS Data.Field1,

  '' AS Data.Field2

AutoGenerate(0); 

FOR index = vStartSheetNumber TO vEndSheetNumber

Set ErrorMode = 0;

Concatenate(NewData)

LOAD

          *

FROM C:\Temp\Qlik\Data*.xlsx

(ooxml, embedded labels, table is [Datasheet$(index)])

WHERE Filetime() > '$(MaxSystemTime)';

NEXT




Any ideas or alternate ways of only loading new data while loading from Mlutiple sheets?

0 Likes
qlikkumar
New Contributor

Hey,
What if i don't have ODBC connection ? How can i load all excel files present in a folder without ODBC connection ?

0 Likes
ysj
Contributor

thank you

0 Likes
zhujason858_cha
New Contributor II

This is super helpful thanks you! How would i edit the code so that each sheet is saved as a different table in QLik?

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2013-07-30 03:58 AM
Updated by: