Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Loop sheets and Excel workbooks

Hi All

I am trying to loop sheets and Excel workbooks. The data structure in all the sheets in all the workbooks is the same but each workbook has different number of sheets. I tried to use the below code and it works when excel workbooks has same number of sheets but doesn't work if the number of sheets in each workbook are not the same

Ex:  When Q_Excel1.xlsx has 4 sheets and Q_Excel2.xlsx has 5 sheets below code doesnt work but when Q_Excel1.xlsx has 4 sheets and Q_Excel2.xlsx has has 4 sheets it works.

ODBC CONNECT TO [Excel Files;DBQ=C:\Desktop\Excel1.xlsx];

  tables:

  SQLTables;

  DISCONNECT;

  let var=NoOfRows('tables');

Data:

LOAD

     A,

     B,

     C,

     D,

FROM

(ooxml, no labels, header is 17 lines, table is '$(sheetName)');

  FOR j = 0 to $(var)-1

let sheetName=subfield(peek('TABLE_NAME', j,'tables'),'$',1);

NEXT j

DROP Table tables;

Regards

Koushik

2 Replies

Re: Loop sheets and Excel workbooks

See this discussion: Loading from multiple Excel files and multiple sheets


talk is cheap, supply exceeds demand
Not applicable

Re: Loop sheets and Excel workbooks

Thanks Wassenaar.

I went through few threads in community and ended up with the solution.

Community Browser