Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Frank_Hartmann
Master II
Master II

How to load this excel file

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

1 Solution

Accepted Solutions
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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).image.png

 

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:

image.png

 

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.

View solution in original post

3 Replies
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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).image.png

 

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:

image.png

 

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.

Frank_Hartmann
Master II
Master II
Author

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!

Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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