Check if string from datatable1 contains in datatable2

Help
1

Hi Everyone,

I have two excel files. In first excel i have a sample list of companies which i want to check in the second excel file. If row matches any string in second excel file keep that row otherwise remove.

Any help would be appreciated.
Here is my code: File_Check.xaml (8.1 KB)

2

Hi @zeshanm9,

If possible can you share those two excel.

3

Excel_Files.zip (69.1 KB)
@shivagowdavarad there are the excel files

4

Hi @zeshanm9,

After 2 read range, let’s say,
Dt1 —> is your main datatable.
Dt2 —> is datatable where, you search for matching value.
use following linq code in assign activity

MatchedDT =

Dt1.AsEnumerable().Where(Function(row)  Dt2.AsEnumerable().
Select(Function(f) f("CompColumnDt2").ToString).Any(Function(x) x.Contains(row("CompColumnDt1").ToString))).CopyToDataTable()

MatchedDT —> will hold records of Dt1 which have matching value in Dt2.

Note - Make sure you put your column names in code.
you can check the MatchedDT by using write range.

1 Like
5

Hi @samir,

Thank you for the solution. If my column don’t have any name so can i use “Column0” or not?
I tried your solution but i am not getting the required result. Can you please check my code and excel files and send me the code after your update if required.

6

@samir Sequence1.zip (108.5 KB)
please find the code and excel files with output. I am getting all the results in the output but i want only the matched ones

7

@zeshanm9,

After seeing your files get the exact idea, here’s the code,

Dt1.AsEnumerable().Where(Function(row)  Dt2.AsEnumerable().
Select(Function(f) f("CompNameDt2").ToString).Any(Function(x) row("CompNameDt1").ToString.Contains(x))).CopyToDataTable()

here’s my sample workflow for your better understanding —> Main.xaml (9.3 KB)

Note - In your files type is llc but in data there’s LLC, so clearly llc & LLC doesn’t match, So you’ve to handle this in your example.

8

@samir sorry i was not able to understand the example you shared. I need to read from excel files not to build it. And if any value does not matched then it should not write those value but now it is writing all the values. Can you please check my code i have shared above also attached hereSequence1.zip (108.5 KB) “Sequence1.zip” and advise.

9

@zeshanm9,

After read range its gonna end up in datatable only.

Dt1- image Dt2 - image
Output —> image
here you can see that value with LLC & INC are there in output because its there in Dt2, but Compy 123, Pvt LMT doesn’t match the condition, why, its because there’s lmt which doesn’t match LMT
getting…??

I have checked your files buddy there’s value inc. in one file and INC. in another… so it’s not gonna match…
If you provide your expected output file then it’ll be easy for everyone here to suggest.

10

Sure. This is my current output.

current_output
current_output1090Ă—151 5.05 KB

and my expected output is(in this case i make llc to LLC in the excel sheet so the output should be like this) :

expected_output
expected_output1126Ă—166 3.92 KB

11

@zeshanm9
I can confirm some issues as I prototyped the same from your older, forked, duplicated Topic ( Compare two Excel Sheets using Linq )
and got also some rows into the result which should be blocked. The initial analysis did not find out the issue. I started to remove empty columns for the filtering, but didn’t solved it. Some unkown, hidden parts are doing a filter confusion.

But we will sort it out

12

@zeshanm9,
Here’s the workflow —> matchComp.zip (85.4 KB)
See I’ve added LLC & INC in master file…
image

Here’s the output —>

image
image1001Ă—108 6.17 KB

by using this only I’ve get the expected output.

13

Thank you @samir it is working now. Appreciated.

14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.