topic Re: How to load this excel file in QlikView

How to load this excel file

Frank_Hartmann — Fri, 15 Nov 2019 08:14:23 GMT

Hi QV Community,

i have an excel file like attached. the data is available on more than one excel tab ( in my real data, there are 8 Tabs) and i need to extract the data from all tabs at once (maybe with a loop..) in order to get the desired result. Please have alook at the attached excel file including inputs and expecteted result.

every help is appreciated!

thx in advance

Re: How to load this excel file

Stoyan_Terziev — Fri, 15 Nov 2019 09:32:51 GMT

Hi Frank,

Interesting challenge.

Seems like you don't know how many and what is the name of each sheet in the excel.

In that case you need to do an ODBC connection to the excel file itself (I hope you can change the .xlsx to .xls since that's how I'm sure it works).

One you've got the connection you can do this in your script:

ODBC CONNECT32 TO [Excel QV Community]; SQLTABLES; SQLCOLUMNS;

Now you basically can get to the Table and Column load order which looks like this:

Here is the script you need (I don't have time to finish it now):

ODBC CONNECT32 TO [Excel QV Community]; SQLCOLUMNS; LET vSystemTableLoad = TableName(0); This: NoConcatenate LOAD COLUMN_NAME , PurgeChar(TABLE_NAME, '$') as TABLE_NAME , If(WildMatch(COLUMN_NAME,'*Cluster*'),COLUMN_NAME, Peek(Cluster, -1)) as Cluster RESIDENT '$(vSystemTableLoad)' WHERE WildMatch(TABLE_NAME, '*Output*')=0 ; DROP TABLE '$(v)'; AllSheets: LOAD Chr(39)&Concat(DISTINCT TABLE_NAME, chr(39)&','&chr(39))&chr(39) as AllSheets RESIDENT This ; LET vAllSheets = peek('AllSheets',-1,'AllSheets'); DROP TABLE AllSheets; FOR Each vSheet in $(vAllSheets) AllClusters: LOAD Chr(39)&Concat(DISTINCT Cluster, chr(39)&','&chr(39))&chr(39) as AllClusters RESIDENT This WHERE TABLE_NAME = '$(vSheet)' ; LET vAllClusters = peek('AllClusters',-1,'AllClusters'); DROP TABLE AllClusters; TRACE '$(vAllClusters)'; /* Another FOR EACH (this time by clusters) And create the table for each cluster and let it autoconcatenate here. */ Next vSheet;

Let me know if you need help further.

Kind regards,

S.T.

Re: How to load this excel file

Frank_Hartmann — Sat, 16 Nov 2019 21:58:00 GMT

thank you for your detailed response. but as long as i cant use ODBC for accessing the excel i managed it to extract the data by script!

Re: How to load this excel file

Stoyan_Terziev — Sun, 17 Nov 2019 10:02:41 GMT

Hi Frank,

The ODBC is the only way to tackle the issue of 'variable number of excel sheets with names you are unable to guess'.

I am happy you got the issue resolved!

Kind regards,

S.T