Comparison of columns between two excel file using For Each row activity

I need to compare two files based on one column and output to excel file if match is found.can anyone give me the logic how to do it using For each row activity.
My current logic as as follows:
for each row firstable
body: assign the value of the firsttable column value to one variable1

 for each row secondtable
body : assign the value of the secondttable column  value to one variable2
  then do compare variable1 = variable 2

can you give me the complete logic to achieve this asap

1 Like

Hi @krishnan,

Assume the datatable as dt1 and dt2.

Using for each row activity Loop it dt1

In side for each row body check if condition like

Dt2.select("column1=row(“column1”).tostring). length>0

If success the first table column value match into second table.

Regards,
Arivu

1 Like

i tried this but I get compilation error disallows implicit conversion to Boolean to string.can you help on it.

1 Like

Hi @krishnan,

send screen shot and error message

Regards,
Arivu

1 Like

I have 2 datatables
firstdt
column1 column2
1 ABC
2 DEF

seconddt
column1 column2
01 DEF
02 ABC
Now I wanted to compare firstdt column2 with seconddt. column 2 and have the count of match and mismatch.

can you give me the complete logic for this ?

1 Like

@krishnan

Dt2.select("column1=row("column1").ToString").length>0

can you try this one

Regards,
Arivu

1 Like

tried still getting error.

1 Like

Send screenshot of your for each activity and what error you are getting?

1 Like

Sorry while typing it went something wrongly please use below code to check if condition

Dt2.select("column1='"+row("column1").ToString+"'").length>0

Regards,
Arivu

1 Like

hi can you give me the full code for the above.
The value of row(column1) will be 15 characters.(need to pick the 8 characters from 3rd postion)
the value of dt2.column1 will be 8 characters.
I want to compareas follows:.
d2.column1 rrow(column1)
can u help on it?

1 Like

@krishnan
use this Query

(From p in dt1.AsEnumerable
where ( From q in dt2.As Enumerable()
where (Convert.ToString(q.Item("column1)).Substring(2,8)).Equals((Convert.ToString(p.Item(“Column1”)))
Select q).ToArray.Count>0
Select p).ToArray.Count

It will give you the matched rows count of dt1 and if you want unmatched rows use dt1.Rows.Count-Count of matched rows

Regards
Mahesh

2 Likes

Hi @krishnan,

Dt2.select("column1='"+row("column1").ToString.SubString(2,8)+"'").length>0

Regards,
Arivu

1 Like

dt2.column = 15 charaters
dt1 column = 8 characters
comparison should be done as follows
dt1 column = dt2.column(8 characters should be picked here

can you give the query for this?

1 Like

@krishnan
try this

Regards
Mahesh

1 Like

Hi @krishnan,
try this

Dt2.select("column1 like '___"+row("column1").ToString+"%'").length>0

Regards,
Arivu

1 Like

this does not work.Can you give me a simple query for this?

1 Like

@krishnan
did you try this

From p in dt1.AsEnumerable
where ( From q in dt2.As Enumerable()
where (Convert.ToString(q.Item("column1)).Substring(2,8)).Equals((Convert.ToString(p.Item(“Column1”)))
Select q).ToArray.Count>0
Select p).ToArray.Count

It will give you the matched rows count of dt1 and if you want unmatched rows use dt1.Rows.Count-Count of matched rows

Regards,
Mahesh

2 Likes

Hi @krishnan,

@MAHESH1

Use this code

From p in dt1.AsEnumerable
where ( From q in dt2.As Enumerable()
where (Convert.ToString(q.Item("column1)).Substring(2,11)).Equals((Convert.ToString(p.Item(“Column1”)))
Select q).ToArray.Count>0
Select p).ToArray.Count

Regards,
Arivu

1 Like

Hi Everyone,
I need to compare two column like (column1 is A, B, C, and column2 is B, D, E, C, A) when column1 “A” it will search if there is A in the column2.

Use the below code to get only the matched data from the Column

From p in dt1.AsEnumerable where ( From q in dt2.As Enumerable() where (Convert.ToString(q.Item("column1))).Equals((Convert.ToString(p.Item(“Column2”))) Select q).ToArray.Count>0 Select p)

can you tell me how u want the result?