Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

datanibbler
Esteemed Contributor

Load an Excel file with variable worksheet name

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

1 Solution

Accepted Solutions

Re: Load an Excel file with variable worksheet name

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.


talk is cheap, supply exceeds demand
4 Replies

Re: Load an Excel file with variable worksheet name

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.


talk is cheap, supply exceeds demand
datanibbler
Esteemed Contributor

Re: Load an Excel file with variable worksheet name

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
Esteemed Contributor

Re: Load an Excel file with variable worksheet name

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

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


talk is cheap, supply exceeds demand
Community Browser