Qlik Community

QlikView Documents

QlikView documentation and resources.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING

Load Multiple excel sheets using For loop

cancel
Showing results for 
Search instead for 
Did you mean: 
jagan
MVP
MVP

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
senarath
Creator III
Creator III

Hi Jagan,

This was very useful and it worked for my application as well.

thanx

0 Likes
Not applicable

plz share qvw and excel file

0 Likes
jagan
MVP
MVP

Hi,

I do not have sample excel and Qvw file, put some values in excel file and use the script.

Regadrs,

Jagan.

0 Likes
richard_pearce6
Luminary Alumni
Luminary Alumni

Hi Jagan,

I use a similar approach when loading QVD's saved with MMM-YY suffixes

http://community.qlik.com/docs/DOC-6668

Regards

Richard

QlikCentral.Com

0 Likes
Not applicable

Works fine, thank you!

0 Likes
Not applicable

thank u jagan mohan

0 Likes
Not applicable

This was very useful and it worked for my application as well.

thanx

0 Likes
qlikviewnovice
Specialist II
Specialist II

Its very useful!! Thank You

Not applicable

Hi Jagan,

     Thanks in advance for your sharing and it works well. I would like to ask a question is that is it possible to load multiple sheets if the sheet names are different. for exp, Sheet1 = 01012015, Sheet2 = 02012015, Sheet3= 03012015. Hope to understand this.

Thanks in advance,

Kyaw

0 Likes
jim_chan
Specialist
Specialist

hello jagan,

what if my sheet name is COHORT 2011 , following sheet names are COHORT 2012, COHORT 2013 ?

Your pattern is [Page $(index)] - so in my case, i cant just put [COHORT $(index)] - it will give me COHORT 1, COHORT 2 , and continue on.

I have to stick with COHORT 2011 - as per request by client.

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