Compare 2 excel files and extract the required data

Help Activities
, , , ,
1

Hi Team,
I have 2 input files. File1 and File2.
In both files one column is common that is group number.
1st task is : In file1 group number is exists in file 2 column name (group number) .
If group number is exist in file2 need to extract the account number, 1st name and Last name.
Let me explain in details.
For example
In file1 have columns like this Group number, Group id etc.
File2 column like Group number, account number, 1st name and Last name etc.

If file1 group number exists in file2 then extract the account number, 1st name and last name data.

Kindly suggest with linq query.

2

Hello @Ramudu

You should use a Lookup Datatable Activity to check whether or not the Group Number exists in file 2.
Use the output value for Index to determine if the Group Number exists.

If int_GrpNrIndex > -1

If the value is higher than -1 then it means that the number is present in the column.
Then you can use the index to grab the desired values.

Assign str_FirstName = dt_File2.Rows(int_GprNrIndex)("First Name").ToString

Assign str_LastName = dt_File2.Rows(int_GprNrIndex)("Last Name").ToString

etc…

Best regards
Soren

3

Hi @Ramudu

Can you try below

This image is a workflow sequence showing the steps to read data from two Excel workbooks, add columns, merge data using a custom code, and write the resulting data back to an Excel file. (Captioned by AI)
This image is a workflow sequence showing the steps to read data from two Excel workbooks, add columns, merge data using a custom code, and write the resulting data back to an Excel file. (Captioned by AI)818×1140 61.9 KB

The image depicts a UI window for invoking code within an application, showing code arguments 'DT1' and 'DT2' with their respective directions, types, and values. (Captioned by AI)
The image depicts a UI window for invoking code within an application, showing code arguments 'DT1' and 'DT2' with their respective directions, types, and values. (Captioned by AI)964×524 21.9 KB

Code:

For Each row1 As DataRow In dt1.Rows
    Dim groupNumber As String = row1("Group Number").ToString()
    Dim foundRow As DataRow = dt2.AsEnumerable().FirstOrDefault(Function(r) r("Group Number").ToString() = groupNumber)
    If foundRow IsNot Nothing Then
        row1("First Name") = foundRow("First Name")
        row1("Last Name") = foundRow("Last Name")
    End If
Next

Input:

DT1
A spreadsheet lists group numbers in column A and corresponding group IDs in column B. (Captioned by AI)

DT2

An Excel spreadsheet lists various individuals with columns for group number, account number, first name, and last name. (Captioned by AI)
An Excel spreadsheet lists various individuals with columns for group number, account number, first name, and last name. (Captioned by AI)621×542 11.1 KB

Output:

DT1
This image is a screenshot of a spreadsheet listing group numbers, group IDs, first names, and last names in a tabular format. (Captioned by AI)

Sequence15.xaml (15.3 KB)

Regards,

4

Hi @Ramudu ,

You can use the following Linq

  (From row1 In dtFile1.AsEnumerable()
                 Join row2 In dtFile2.AsEnumerable()
                 On row1("Group number").ToString() Equals row2("Group number").ToString()
                 Select dtResult.LoadDataRow(New Object() {
                       row1("Group number"),
                       row2("account number"),
                       row2("1st name"),
                       row2("Last name")
              }, False)).CopyToDataTable()
5

Hello,

If group number is exist in fiile2 extract the req data like first name and last name.
Write the extracted data in file1.

For next iteration checkig for 2nd group number if data number exists extract the data and write in file1. If not write the payable account in new column like generic name.

6

Hi @Ramudu ,
The requirement is not clear