This will get you the common rows from both Datatables in a new datatable
dtCommomElements = dt1.AsEnumerable().Where(Function(row) dt2.AsEnumerable().Any(Function(x) x(“Person Name”).ToString=row(“Person Name”).ToString)).CopyToDatatable
And then you can then delete these rows from the original two datatable using Linq
dt1=dt1.AsEnumerable().Where(Function(row) NOT dtCommonElements.AsEnumerable().Any(Function(x) x(“Person Name”).ToString=row(“Person Name”).ToString)).CopyToDatatable;
dt2=dt2.AsEnumerable().Where(Function(row) NOT dtCommonElements.AsEnumerable().Any(Function(x) x(“Person Name”).ToString=row(“Person Name”).ToString)).CopyToDatatable;
Hope this helps!
1 Like
hi @rahulsharma
thank you again for your response,
i dont know how this works, but when i tried i got an Error, Should i put it inside for each row?
or Did I Missed something?
dtDuplicates = dtEffDateError.AsEnumerable().Where(Function(row) dtReport.AsEnumerable().Any(Function(x) x(“Person Number”).ToString=row(“Person Number”).ToString)).CopyToDatatable
dtEffDateError = dtEffDateError.AsEnumerable().Where(Function(row) NOT dtDuplicates.AsEnumerable().Any(Function(x) x(“Person Number”).ToString=row(“Person Number”).ToString)).CopyToDatatable
dtReport = dtReport.AsEnumerable().Where(Function(row) NOT dtDuplicates.AsEnumerable().Any(Function(x) x(“Person Number”).ToString=row(“Person Number”).ToString)).CopyToDatatable
no. no for loop required.
The error states there are no rows in the source.
Make sure the input datatable are not null. Do check that…
Also For each can also be implemented if you wish to go for that approach.
Use a Join Datatable, with the column name as Person Name
This will give you common rows in both input datatable, output will be a datatable dtCommomElements
then use a for each row loop for this dtCommonElements, inside it put 2 filter datatable activity, one for each of your input datatable. Then in the filter wizard, just put the condition column “Person Name” value = row.toString
this will remove the common elements from both the datatable.
hi, I think i describe it wrong?
Note: is is a Dynamic data, It always change @rahulsharma
I wanted to delete the ROW that Contains person number that match from Datatable1 to Datatable 2.
Or if Person number from datatable 2 is Match in datatable 1.
Please see my datatable below.
Here is my data table 1, it contains person number "101010"
Data Table 2
Now, my expected result is to Delete the Row from Datatable 1 and datatable2 that contains “Person Number” 101010
i tried another method Solution from another Topic, but if did not work on me.
it did not delete any row from my data table
Yah the expression is fine but we cannot use REMOVE DATAROW ACTIVITY inside thr for each row loop as we are currently iterating through each row in the datatable and meanwhile we won’t bale to delete the rows in it and that why Ymir’s was showing that error.
So we need to get the row index now using add to collections activity and use a separate for each loop activity and pass the variable used in the add to collections- as input here in for each loop activity and then use remove datarow activi…
hi @rahulsharma can you show sample workflow on this? please.
rahulsharma
(Rahul Sharma)
October 22, 2021, 12:53pm
#8
sorry for the late response.
Sure mate, i can show you sample workflow.
Could you please send that file? That eill help you to understand the workflow better
it is fine. That should not affect the logic. are you getting any error?
rahulsharma
(Rahul Sharma)
October 22, 2021, 1:47pm
#10
This should help.
We get the elements which are common in those two datatable and then remove them from original record. I pasted the values, run this and the filtered values will be in the same excel in two different and new sheets
FilterForum.zip (12.2 KB)
hi @rahulsharma thank you for this,
But there is a thing that i worried about. there is a lot of Workflows activity using and reading that sheet “Encoded < 14 Days” and “Failed” before and after removing duplicate row.
is it possible that i can remove it directly from that sheet without create an new excel sheet?
rahulsharma
(Rahul Sharma)
October 22, 2021, 2:35pm
#12
file is accessed once for reading and once for writing.
yes you can, just mention the same sheet name and the range as “A1” this will replace the existing data there
hi it, i think it did not work
i mention the same sheet name and the range as “A1”
rahulsharma
(Rahul Sharma)
October 22, 2021, 4:25pm
#14
to clear the content you can either use the Excel Application scope and then use delete range, mention the max range you can possibly give to make sure the entire sheet is deleted.
that will require excel installed on your system.
OR
you can also delete that sheet and write with the same name, that’ll create a new sheet of same name
FilterForum.zip (22.2 KB)
Hope this helps!
1 Like
c0Ld
(Luís Silva)
October 22, 2021, 8:27pm
#15
Hi,
I had a similar issue and I resolved by making a list of IDs and run for each ‘data table’.
I hope that could be helpful.
hi @c0Ld can you share a sample workflow pls?
hi @rahulsharma Delete Range activity is not working on my UIPATH studio version 2018.4.1
rahulsharma
(Rahul Sharma)
October 23, 2021, 6:06pm
#18
you can use a VB script also to delete it.
Attached the script in the code in new sequence, you can use that.
Make sure you do the necessary changes required for running the macro. You need to update the below setting in Excel Settings
Steps:
File:
FilterForum.zip (23.8 KB)
Hope this should help you resolve your query. If so, mark a post as solution to close the thread
2 Likes
hi @rahulsharma your VB Script is working already, but it writes same data that i need to delete from the table.
hi @rahulsharma Thank you so much for your help and patience its working Now:)
1 Like
system
(system)
closed
October 27, 2021, 2:53am
#21
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.