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

Help
,
1

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
2

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
3

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

1 Like
4

Hi @krishnan,

send screen shot and error message

Regards,
Arivu

1 Like
5

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
6

@krishnan

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

can you try this one

Regards,
Arivu

1 Like
7

tried still getting error.

1 Like
8

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

1 Like
9

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
10

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
11

@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
12

Hi @krishnan,

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

Regards,
Arivu

1 Like
13

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
14

@krishnan
try this

Regards
Mahesh

1 Like
15

Hi @krishnan,
try this

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

Regards,
Arivu

1 Like
16

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

1 Like
17

@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
18

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
19

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.

20

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?