Load Multiple excel sheets using For loop

[jagan]

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.

[senarath]

Hi Jagan,

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

thanx

[#3]

plz share qvw and excel file

[jagan]

Hi,

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

Regadrs,

Jagan.

[richard_pearce6]

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

[#6]

Works fine, thank you!

[#7]

thank u jagan mohan

[#8]

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

thanx

[Anonymous]

Its very useful!! Thank You

[#10]

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

[jim_chan]

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.