Loop Excel sheets with IF statement

[kmarsden]

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)]);

NEXT

[hic · Accepted solution]

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

[kmarsden]

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