topic Re: Load an Excel file with variable worksheet name in QlikView

Load an Excel file with variable worksheet name

datanibbler — Wed, 22 Oct 2014 08:43:47 GMT

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

Re: Load an Excel file with variable worksheet name

Gysbert_Wassenaar — Wed, 22 Oct 2014 08:51:22 GMT

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.

Re: Load an Excel file with variable worksheet name

datanibbler — Wed, 22 Oct 2014 09:12:46 GMT

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

Re: Load an Excel file with variable worksheet name

datanibbler — Wed, 22 Oct 2014 09:18:00 GMT

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

Re: Load an Excel file with variable worksheet name

Gysbert_Wassenaar — Wed, 22 Oct 2014 09:19:00 GMT

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