Solved loop with excel

Hello,

I facing issue while working on loop with excel metadata

I have below scenario.

I have multiple excel files with similar structure. I want to load it one mapping.

I tried below:
1: create mapping using by reversing one excel file. Mapping executed successfully.
2: Create process while include 2 action 1 is file wait and 2 is execute delivery.
3: both are link together using bind link.
4: execute delivery action include delivery of mapping which is created in step 1. File Name variable which I am passing to Excel metadata as Physical Name.
Variable defined as file_name ":{FILE_NAME}:"
Then I tried to execute process it is giving me error. I tested value set for file_name is <excel_file_name>.xlsx
where I need to set only excel_file_name which is my sheet name.

Let me know if any additional information required. Please this is urgent

Regards,
Prashant Sangle

Hi Prashant,

While working with Excel for looping the same mapping for multiple input files has a slight different than the same example for processing multiple flat files.

For Excel metadata, you can keep the URL as is (no need to put parameter).

When you expand the Excel Connection, you see your Excel Datastore, Click on that and you will see, Name/CatalogueName:-


Change these with the parameter.

But as per your post, one thing to note that :{FILE_NAME}: stores the complete filename, including extension. So when using it you have to remember that it contains the extension as well and not just the name.

So the difference here is that you need just the excel file name, whereas :{FILE_NAME}: will give you name with extension.

So inside the Process which you are going to put in Execute Delivery action, place a SQLToParameter action and write a convert statement and assign the parameter name (in my case "~/excel_name"):-
Select SUBSTRING('${~/p_excel_name}$',1,LEN('${~/p_excel_name}$')-5)

where p_excel_name is the parameter defined on Execute Delivery Action which store yuor :{FILE_NAME}:





Please let me know if that helps!

Thanks,

Vaibhav

You can avoid the SqlToParameters action (and the database connection) using a bit of rhino scripting in the "p_excel_name" parameter definition :

%e(rhino){
s = ":{FILE_NAME}:";
s.substring(0, s.length - 5);
}e(rhino)%

Hi,

Thanks for the solution.

Now I want to make my sheet name also dynamic which is similar to excel file name.

I have added new parameter with your script in execute delivery action and call that parameter in Sheet Name (Physical Name). but I am getting error, which I have attached. If I back trace it I found it source table script was generating properly.

Please suggest.

Regards,
Nikita

Hi, can you show how you have set the parameter on Execute Delivery and in the Excel metadata, exactly ?

Hi,

Bellow are the screen short for loop and Metadata

Loop:[attachment=


Parameter for catelog name=


parameter for Sheet Name


Regards
Nikita Barve

Hi All,

Thanks for help. Using Vaibhav solution, I am successfully able to create loop on excel metadata.

@risson - Your solution gave issue while making sheet name dyanamic in loop else it is working like charm.

Regards,
Prashant Sangle