Read every row, match datas from two excel files, add new column into one of the excel files

[xiaoliao (X)]

Hi all

Excel 1 file contains the following columns and its data.
Name, Designation, Department, Division

Excel 2 file contain the following columns and its data.
Name, Designation, Email, Institution, Department

Qns: How do I

• Read every row;
• Match the “name”, “designation” and “department” from Excel 2 with Excel 1;
• If the data matches, insert the “Email” data from Excel 2 into Excel 1 file accordingly under a new column of “Email” in Excel 1?

So, Excel 1 file will have a new column “Email” and the data.
Name, Designation, Department, Division, Email

Thank you.

[ppr (Peter)]

@xiaoliao
Welcome to the Forum

In this case we would recommend to start with Join DataTable Actvity

Story line of Join Data Table

Dear innovator’s, All we know that there is a Join Data table activity in UiPath.If we get any matching scenarios with unique value will approach join data table activity instead of filter/loop/array matching activity to find an exact match. Join Data Table: [image] Combines rows from two tables by using values common to each other, according to a Join rule, which is specified in the Join Type property. Sample Table: [image] INNER Join: The INNER JOIN keyword selects records that have m…

Pay special attention on the resulting Join DataTable, which can be used as the source for the updated / resulting DataTable

As another approach have afterwards a closer look to LINQ
[HowTo] LINQ (VB.Net) Learning Catalogue - Help / Something Else - UiPath Community Forum

[xiaoliao (X)]

Hi

Thank you for your advice.

I tried your suggested method, however, my sheet has both Excel 1 and Excel 2 data.
Name, Designation, Department, Division + Name, Designation, Email, Institution, Department

So, how do I…

• Read every row;
• Match the “name”, “designation” and “department” from Excel 2 with Excel 1;
• If the data matches, insert the “Email” data from Excel 2 into Excel 1 file accordingly under a new column of “Email” in Excel 1?

Thank you.

[ppr (Peter)]

its always good to make an initial topic / case description complete so we can faster help.

@xiaoliao

my sheet has both Excel 1 and Excel 2 data

Just share the Excel, or sample data with us

About the join DataTable Activity:

[xiaoliao (X)]

Hi,

Excel 1
Excel 1.xlsx (9.1 KB)

Excel 2
Excel 2.xlsx (10.2 KB)

Expected outcome/output for Excel 1
Excel 1 - Output.xlsx (9.3 KB)

Thank you.

[ppr (Peter)]

• read range - Excel1 out: dt1
• read range - Excel2 out: dt2

Join DataTable - as above:

@ppr

About the join DataTable Activity:

Filter Datatable:

Variations:

• instead of Filter DataTable - using dtJoin.DefaultView.ToTable(false, arrColSet)
• LINQ when trimming is needed for the Join

[xiaoliao (X)]

Thank you for your guidance and it works.