dynamically loading excel with many sheet

[iswarya]

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.

[jonathandienst]

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.

[iswarya]

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

[Vegar]

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]

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]

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

[Vegar]

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