Map data in excel

Help Activities
, ,
#1

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

File Map data;
image

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.

image
image977×120 5.15 KB

Please guide me about it.
input.xlsx (12.8 KB)
map data.xlsx (9.0 KB)

#2

Hi

I couldn’t get the query exactly

Can you elaborate a bit more on this

@fairymemay

#3

@Palaniyappan
step as below.

  1. read column App in file input all sheet (except sheet Output)
  2. map value from step1 with filename Mapdata in column App.
  3. If found pass value in column ADR1_2 , CLTADDR03 , CLTADDR04 , CLTADDR05 , CLTPCODE it to sheet input.
1 Like
#4

Hi

Fine hope the below steps would help you resolve this

  1. 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

  2. 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

  1. Use a FOR EACH activity and pass list variable as input and change the type argument as string

  2. Inside the loop use a read range activity and mention the sheet name as item.ToString and get the output as dt

  3. 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

  1. Then use a Separate Read range activity and pass the file path of master file and get the output as dt_master

  2. 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

  1. 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

  1. 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
#6

@Palaniyappan oh, It long process.

1 Like
#7

Yeah it is
But will work

@fairymemay

#8

@Palaniyappan I will try.

#9

@Palaniyappan

Now my flow as below.
But I don’t clear in step 5.

Please guide me more about it.

image
image553×674 19.4 KB

#10

Inside the loop no assign

Use read range activity and merge datatable activity

@fairymemay

#11

@Palaniyappan Right?

image
image

1 Like
#12

Perfect @fairymemay

#16

@Palaniyappan I not understand in step 7.

  1. 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.

image
image1509×640 33.8 KB

#17

In each of the for each row we can see as CurrentRow
Change that to row1 and row2

@fairymemay