Loop Excel sheets with IF statement

kmarsden — Wed, 08 Apr 2015 16:04:38 GMT

Hi,

I have the following script which loops through each sheet in an Excel workbook and loads into QlikView. I now wish to limit the sheets loaded based on the TABLE_NAME field (from SQLTABLES). For example, where TABLE_NAME is not like '*Print_Area' (or in other words where TABLE_NAME does not have a suffix of "Print_Area"). I'm assuming that an if/else statement before or after the loop could be used but I'm not sure of the syntax etc. Any help would be greatly appreciated. Thanks in advance.

ODBC CONNECT32 TO [Excel Files;DBQ=<path>.xlsx];

LET vFile = '<path>.xlsx';

ExcelSheetNames:

SQLTABLES;

DISCONNECT;

FOR i = 0 to NoOfRows('ExcelSheetNames')-1

LET vExcelSheets = PURGECHAR(PURGECHAR(PEEK('TABLE_NAME', i,'ExcelSheetNames'), CHR(39)), CHR(36));

LOAD *,

'$(vExcelSheets)' AS Sheet

FROM $(vFile)

(ooxml, no labels, table is [$(vExcelSheets)]);

Re: Loop Excel sheets with IF statement

hic — Wed, 08 Apr 2015 16:17:39 GMT

I would probably put an If-statement inside the loop:

For i = 0 ...

Let vExcelSheets = ... ;

If Index( vExcelSheets , 'Print_Area' ) = 0 Then // does not contain Print_Area

Load ...

End IF

Next i

/HIC

Re: Loop Excel sheets with IF statement

kmarsden — Thu, 09 Apr 2015 09:11:55 GMT

Great! Exactly what I was looking for. Thanks for your help Henric

topic Loop Excel sheets with IF statement in QlikView

Loop Excel sheets with IF statement

Re: Loop Excel sheets with IF statement

Re: Loop Excel sheets with IF statement