Qlik Community

QlikView Documents

Documents for QlikView related information.

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

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
senarath
Contributor 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
MVP & Luminary
MVP & Luminary

Hi,

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

Regadrs,

Jagan.

0 Likes
Luminary
Luminary

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
Valued Contributor 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
Contributor III

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
Revision #:
1 of 1
Last update:
‎2013-07-30 03:58 AM
Updated by: