Compare Excel files to get matching rows based on a specific column

Help Activities
, ,
1

Hi I have a scenario where I have two excel files

DT1
No. Name Grade Status
1 aaa A p
2 bbb B F
3 ccc C F
4 ddd C F

DT2
No Name Enrolled
1 aaa NA
2 bbb NA
3 ggg NA

I need to get rows matching with only “name”
output should be

No Name
1 aaa
2 bbb

Thanks

2

HI,

There are some ways to achieve it. How about the following using JoinDataTable with InnerJoin and FilterDataTable?

image
image1472×821 48.2 KB

Sample20230421-4L.zip (7.9 KB)

Regards,

3

Thanks, whats the filter criteria?

For the column “Name”, row values are not in a same position

4

Hi,

Filter columns as the following (order).

image

Regards,

5

The column names are not same in both the data tables

6

Sorry, but what is your concern? Did you try the above sample? (Sample20230421-4l.zip)
Doesn’t it work for you?

7

@MEGHA_cs

Please follow the steps below

  1. Use a join datatable activity and join dt1 and dt2 on name column …make sure to do a inner join
  2. Use filter datatable activity and open filter wizard,switch to columns tab and mention the column names which you want to keep (Name and No)

Hope this helps

Cheers

8

Thanks,

But am not getting all the matching values

9

@MEGHA_cs

Did you use join only?

Can you show your workflow please

Cheers

10

No its not showing all the matching records , am getting only a few

11

@MEGHA_cs

Please try this in assign also make sure to use build datatable activity to build a datatable outdt with two columns no and name

Outdt = (From d1 In dt1.AsEnumerable
Join d2 In dt2.AsEnumerable
On d1("Name").ToString.Trim.ToLower Equals d2("Name").ToString.Trim.ToLower
Select outdt.Rows.Add (
{
d1("No").ToStirng, d1("Name").Tostring
})).copytodatatable

Cheers

12

why am getting this error?
image

13

@MEGHA_cs

Are there any matching rows in two tables?

Also did you happento have data in dt1 and dt2

The column names name are correct or have been changed to required name?

Can you show how you are using it

Cheers

14

Yes there are matching rows in two tables.

dt1 and dt2 contain data.

the column names are different in both the tables

15

@MEGHA_cs

Then in the formula did you change th column names to sync with what you have in two tables?

Can you show a screenshot please

Cheers

16

image
image711×279 38.8 KB

for this am getting this error
image

17

@MEGHA_cs

This is because the dtfinal should contain only one column now…you are taking only name…so please use build dataTable activity and assign to dtfinal have have one column in it

Cheers

18

If the names in one datatable have uppercase and another datatable have lowercase, it wont show those rows??
Because am not getting all the matching data

Thanks

19

@MEGHA_cs

Fo the same reason I included this function of tolower while comparing…

I see in your code you removed it…if you add back it would match those as well…

Normal join activity would not do that so added the same in thai formula

Cheers

20

Thanks,

it will match irrespective of case or only lowercase… because dt2 has names in both uppercase and lowercase but dt1 has names with only uppercase.