I have a excel sheet , where I am searching a name(Eg: AAA). The name might appear in multiple rows. I need to extract only those rows where AAA is present and copy to another datatable
Book1.xlsx (8.6 KB)
Image shows how the output should look like
Can you try FilterDataTable activity as the following?
But my input file is on similar basis , where i have more than 900 rows.
and my search item is not hardcoded. Like i will be looping a column from a datable and searching each value if it is found in another datatable.
So how do i do that using filter datatable wizard using column number rather than column name
you can achieve this using linq
(From d In dtData
Group d By k=d(0).toString.Trim Into grp=Group
Where grp.Count >1
Select grp.toList).SelectMany(Function (x) x).CopyToDatatable
Hope this helps
FilterDataTable activity is not very suitable for complex/dynamic filtering.
Can you share what you want to achieve as result? We might be able to suggest LINQ expression for 2 datatables.
In the above image, I will be looping through each value in the column and search if it matches in the excel file(book1) attached above. Then i have to extract the rows containing that value and copy to another datatable
Out put should be as shown below
here is a workflow of your sample excel
Sequence.xaml (5.6 KB)
Thank you for sharing. We can achieve the requirement using FilterDataTable, I think.
Can you try the following sample?
Sample20220407-2.zip (15.1 KB)
Can i give column number instead of column name in column field
Thanks it works. I just need one modification.
Suppose the first value that i am searching is AAA, then it should keep only AAA related rows. next if search value is CCC, it must keep CCC related rows
Can this be done in that
FYI, If you need only duplicated rows, please add If activity as the following.
Sample20220407-2v2.zip (15.3 KB)
i dint understand your context @shilpa_p can you brief me more
so basically u need only 1 st duplicate result
I have two excel files
from 1st excel file(data) , i will be looping each value in the column and checking that exists in excel file-2(book1)
if a match is found, say for 1st value AAA, then all related columns should be extracted.
the same applies for all values in excel file(data.xlsx).
So when i search and a match is found, in the output i must get only that value related rows. not others
These above images are reference of how output should look like
Yes i Got it @shilpa_p
Please find the attached code
Sequence.xaml (9.0 KB)
@shilpa_p Try with this query
Assign Result_DT = DT1.AsEnumerable().Where(Function(r1) DT2.AsEnumerable().Select(Function(r2) (r2(0).ToString) ) .Any(Function(x) x=(r1(0).ToString) )).CopyToDataTable