I want to map data in all sheet in file input (except sheet output)
File input ;

File Map data;

Map value column App with value column App in file Map data.
and pass value column ADR1_2 , CLTADDR03 , CLTADDR04 , CLTADDR05 , CLTPCODE
—> write it in file input.
Please guide me about it.
input.xlsx (12.8 KB)
map data.xlsx (9.0 KB)
Hi
I couldn’t get the query exactly
Can you elaborate a bit more on this
@fairymemay
@Palaniyappan
step as below.
- read column App in file input all sheet (except sheet Output)
- map value from step1 with filename Mapdata in column App.
- If found pass value in column ADR1_2 , CLTADDR03 , CLTADDR04 , CLTADDR05 , CLTPCODE it to sheet input.
1 Like
Hi
Fine hope the below steps would help you resolve this
-
Use a excel application scope and pass the file path as input
And inside the scope use a Get workbook sheets activity and get the output as list of sheetnames named with the variable list_sheetnames
Get Workbook Sheets
-
Now use a READ RANGE activity and pass the input as list_sheetnames(0).ToString in sheet property
And get the output as dt_consolidated
Then use a CLEAR DATATABLE ACTIVITY and mention dt_consolidated as input
-
Use a FOR EACH activity and pass list variable as input and change the type argument as string
-
Inside the loop use a read range activity and mention the sheet name as item.ToString and get the output as dt
-
Inside the same loop next to read range use a merge datatable activity where in source mention as dt and destination as dt_consolidated
Now this dt_consolidated will have all the values consolidated from input sheets
-
Then use a Separate Read range activity and pass the file path of master file and get the output as dt_master
-
Use a for each row activity and pass dt_master as input and change the variable name as row1
Inside the same loop use another FOR EACH ROW activity and pass dt_consolidated as input and change variable as row2
- In the inner loop use a IF activity with condition like this
row2(“App”).ToString.Contains(row1(“App”).ToString)
If true it goes to THEN block where use a assign activity like this
row1(“ADR1_2”) = row2(“ADR1_2”).ToString
And another assign activity inside the then block like this
row1(“CLTADDR03”) = row2(“CLTADDR03”).ToString
Similarly use assign activity for other column values as well in then block
- Then finally inside the inner for each row loop next to these assign activity use a BREAK activity inside the THEN BLOCK
So that it will come out the inner loop and continues with the next
Cheers @fairymemay
1 Like
@Palaniyappan oh, It long process.
1 Like
@Palaniyappan
Now my flow as below.
But I don’t clear in step 5.
Please guide me more about it.
Inside the loop no assign
Use read range activity and merge datatable activity
@fairymemay
@Palaniyappan I not understand in step 7.
- Use a for each row activity and pass dt_master as input and change the variable name as row1
Inside the same loop use another FOR EACH ROW activity and pass dt_consolidated as input and change variable as row2
How to change variable as row1 & row2?
Now my flow as below.
In each of the for each row we can see as CurrentRow
Change that to row1 and row2
@fairymemay