Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
iswarya
Partner - Creator
Partner - Creator

dynamically loading excel with many sheet

I have only one excel. It contains 12 sheets where the sheet names are months. Starting from april to feb. how to load dynamically where sheet name should be a dimension for filter.

6 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

Do you know the sheet names? If they are known names and contain the same data for each month, then you can loop over the sheets and concatenate the data loaded from each sheet. If the number of sheets may vary, then you can use ErrorMode = 0 to skip over the errors from the missing sheets.

>>sheet name should be a dimension for filter

I am not quite sure what you mean.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
iswarya
Partner - Creator
Partner - Creator
Author

PFA. I need the script to load multiple excel tab dynamically

Vegar
MVP
MVP

As @jonathandienst mentioned, you can adjust the ErrorMode and run all potential expected tabs.

for each tab in 'January', 'February', 'April','May', 'June',  'August'
	ErrorMode =0; //Ignore the failure and continue script execution at the next script statement.
	//Autoconcatenate
	Data:
	LOAD ID, 
	     Name, 
	     A, 
	     B, 
	     C, 
	     D, 
	     E, 
	     F
	FROM
		Book1.xlsx
		(ooxml, embedded labels, table is $(tab));
	if ScriptError = 'File Not Found' THEN
		TRACE $(tab) not found;
	ELSE 
		TRACE $(tab) where found;
	endif
	ErrorMode =1; //The script execution will halt and the user will be prompted for action.
next
Vegar
MVP
MVP

An alternative way for dynamicly load all tabs in a excel is to create an ODBC Connection to your Excel file and fetch all tab names from that connection using the following query.

[Excel tabs]:
Load *;
sqltables;

 

iswarya
Partner - Creator
Partner - Creator
Author

i dont want using odbc connection. I just want to load one excel with multiple sheets

Vegar
MVP
MVP

I understan 😊

Without the ODBC connector you will not be able to dynamicly fetch the tab names, but as long you have an idea of which tab names you are looking for the first solution using the FOR EACH ... NEXT solution will work fine.

 

- Vegar