How to standardize data with mapping tables in UiPath?

[JoyceP (JP)]

I have a csv file that contains all the data that needs to be data entered. However, some of the data needs to be standardized with a mapping table which is in another Excel with multiple worksheets, each containing one mapping table. How to standardize the data with the mapping table in UiPath?

[Dinesh_Guptil]

Hi @JoyceP

Can You share the expected output

[JoyceP (JP)]

Yes, here is the expected result

And here are the files for your references
mapping_data.txt (278 Bytes)
mapping_map.xlsx (15.1 KB)

[ppr (Peter Preuss)]

One of many options

• Working with Lookup DataTable Activity and translate the values (e.g. Country) into the Codes
• Doing this translations within a for each

Alternate Variations:

• LINQ
• LookUp Dictionary

[Dinesh_Guptil]

Hi @JoyceP

Please check the attached Xaml File
input.xlsx (6.5 KB)
Out.xlsx (6.5 KB)

Test.zip (12.5 KB)

[JoyceP (JP)]

Thank you so much! It works perfect!

[JoyceP (JP)]

If you don’t mind, can I ask an extra question? If there are many columns in the input table, not just 5, how do I effectively include all the other columns, but only exclude the ones that need to be mapped?

[Dinesh_Guptil]

@JoyceP

Can Make it done by using invoke code

[JoyceP (JP)]

@Dinesh_Guptil

invoke code

this cannot be done by modifying Final_DT = DT.AsEnumerable.Select(Function(r) DT.Clone.LoadDataRow({r(“Name”).ToString,r(“Gender”).ToString,r(“Age”).ToString,Dict_Country(r(“Country”).ToString).ToString,Dict_Sub(r(“Subject”).ToString).ToString},false)).copytodatatable? Or can I use assign activity to include all the columns in the input table and append the mapped columns to it.

[Dinesh_Guptil]

@JoyceP

It Can Be done but if the columns increases then invoke code is the best choice

[JoyceP (JP)]

Thank you for your reply!