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

Help Activities
, , , ,
1

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.

2

@xiaoliao
Welcome to the Forum

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

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

1 Like
3

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.

4

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

Just share the Excel, or sample data with us

About the join DataTable Activity:
grafik

5

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.

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

Join DataTable - as above:

Filter Datatable:
grafik

Variations:

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

Thank you for your guidance and it works. :slight_smile:

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.