LOOKUP DATATABLE in comparing two data tables

Help Studio
, ,
1

I used 2 excel sheets with columns like “Employee Name”, “Designation” and “Status” in Sheet1 and “Employee Name” and “Updated designation” columns in Sheet2. Some Employee Name values in both sheets are same.
Here the scenario is bot has to read both excel sheets and in both sheets if the Employee Name had matched then bot has to write “Updated” in Status column in Sheet1. and if not matched then bot has to write “Not updated” in Status column in Sheet1

In this I took Lookup data table to compare both tables.
In lookup data table I am having confusion in Column Number field. What to give in the Column Number field.

1 Like
2

In Activities - Lookup Data Table it is named ColumnIndex. It is zero-based index of columns in the datatable.

Assuming your datatable has columns “Employee Name”, “Designation” and “Status” then “Employee Name” has index 0 , “Designation” has index 1 and so on.

Cheers

3

You don’t have to give anything in the column number field. You can designate the column by name. Also, I find the Lookup Datatable activity to be confusing and poorly explained in the tooltips. I recommend using simple LINQ expressions to do this instead.

4

Lets say you have dt1 and dt2. dt1 is sheet1 table and dt2 is sheet2 table. Then →
Use for each row in dt1 → inside this for each use lookup datatable.
Properties for lookup datatable should be as followed:
INPUT properties:
input - dt2
lookupvalue - row(“Employee Name”).Tostring
lookup column - give either column name or index so column name - “Employee Name” or index - this should come form dt2 not dt1 since in input we have mentioned dt2
target column - give either column name or index so column name - “Employee Name” or index - this should come form dt2 not dt1 since in input we have mentioned dt2

5

1 Read the bot the excel as Datatable1 and DataTable2
Using UiPath Excel activity read both the sheet and and get the both the data in to data table

2 Validate the column name
You need to validate that both the table has same column name and get the index value

int employeeNameIndex1 = dt1.Columns["Employee Name"].Ordinal;
int employeeNameIndex2 = dt2.Columns["Employee Name"].Ordinal

3 You need to loop to data table and update the status

foreach (DataRow row1 in dt1.Rows)
    {
        string employeeName = row1[employeeNameIndex1].ToString();

        // Find matching row in dt2 using LINQ (efficient for large datasets)
        DataRow match = dt2.AsEnumerable()
            .Where(row2 => row2.Field<string>(employeeNameIndex2) == employeeName)
            .FirstOrDefault();

        if (match != null)
        {
            row1["Status"] = "Updated"; // Update status if match found
        }
        else
        {
            row1["Status"] = "Not updated"; // Update status if no match
        }
    }

@Maheswar_Reddy I hope you got the idea how to solve the issue, and you let me know if you are getting any issue?