How to load this excel file

[Frank_Hartmann]

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

[Stoyan_Terziev · Accepted solution]

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.

[Frank_Hartmann]

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]

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