Load an Excel file with variable worksheet name

[datanibbler]

Hi,

how do I load a table from an Excel_file if I know the file_name, but not the sheet_name - it is usually "owssvr", but it might be "owssvr (1)" - I don't know the reason. I'm trying to keep the manual effort to a minimum so that the person fetching those files - there are six, my code runs as a loop over them all - doesn't have to manually rename that.

Thanks a lot!

Best regards,

DataNibbler

[Gysbert_Wassenaar · Accepted solution]

Is simply loading all the sheets from the excel file an option?

load all the sheets from XLSX

If not, you could check the sheet name and decide on the basis of the sheet name to load it or not.

[datanibbler]

Hi Gysbert,

yes, I guess that would be an option since the files are exported by someone here on site and so every file has only one sheet.

Alternatively, how could I check the sheet_name? That's what I was just trying to do, but I don't know any function that would do this.

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Hi,

that sheet shows quite a complicated solution with a Connect_string and stuff - I have just noticed it can be done much easier: By just omitting the table_specification in the LOAD_statement, QlikView just loads whatever is there.

Best regards,

DataNibbler

[Gysbert_Wassenaar]

Well, in the example I linked first a table named tables is created and loaded with the data returned by the SQLtables command. Next a for-next loop is iterating through this table. In that loop you can check the sheet name you get from the tables  table with a wildmatch:

LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));

if(wildmatch($(sheetName), 'owssvr*') then

    ...load the data

endif