there are 2 columns that contain Y values within them. Bot needs to search for those rows where those 2 specific column contains Y and delete those specific rows. Attaching sample file for ref: the column names are "
Sample File.xlsx (19.2 KB)
Tax Has Employee Tax" and “Tax Has Employer Tax”
two ways
-
Use filter Excel activity and filter on columns required with Y
-
Use delete visible rows
-
Read data into datatable
-
Use filter datatable activity and give condition as “Tax Has Employee Tax” equals “Y” and “Tax Has Employer Tax” equals “Y”
-
Write the filtered data back to excel
cheers
Filter Data Table: Expression Activity type ‘VisualBasicValue`1’ requires compilation in order to run. Please ensure that the workflow has been compiled.:
getting this error in filter datatable activity
As per error looks like you copied the double quotes from here…please remove and re dd them
correct - "
wrong - “
If error persists please show a screenshot of how you are giving the values
cheers
Hi,
FYI, another approach using LINQ
arrDr = dt.AsEnumerable.Where(Function(r) not (r("Tax Has Employee Tax").ToString="Y" AndAlso r("Tax Has Employer Tax").ToString="Y")).ToArray()
Sample20230926-1aL.zip (17.2 KB)
Regards,
Dt = dt.AsEnumerable()
.Where(row => row.Field(“ColumnName1”) == “Y” && row.Field(“ColumnName2”) == “Y”)
.CopyToDataTable();
@ayushi_jain3 try this
Code is just executing, but not deleting the rows
This is not working either @Praveen_Mudhiraj
same error even after changing the quotes
Try with this code in assign activity
dt = dt.AsEnumerable().Where(Function(r) Not (r("Tax Has Employee Tax").ToString = "Y" AndAlso r("Tax Has Employer Tax").ToString = "Y")).CopyToDataTable()
Hope this helps
Cheers @ayushi_jain3
Nope, still not deleting. Also, it is removing the column value"Y" but not the rows somehow
So there were 2 rows i =n sample flle that had “Y” values. In one row only single column had “Y” value but in other row, both the columns had the “Y” value. So it just deleted the first row where single column had the value, but not the other row. If you go through the result.xlsx you shared, You’ll get the idea.
Thanks
Hmm no
If u just look at the expression it neglects the row which equals to value Y and get the rest of rows
dt = dt.AsEnumerable().Where(Function(row) NOT row(“Tax Has Employee Tax”).ToString.Equals("Y”) OR NOT row(“Tax Has Employer Tax”).ToString.Equals(“Y”)).CopyToDataTable()
So there are 3 things:
- It is not deleting the rows
- The rows where both the columns have Y in it, only one column deletes Y value and the other one is left.
- It is just removing Y from the columns , not the rows.
Attaching the input file, the result is in the result sheet and also attaching the code.
Request you to please take a look at it.
Thanks a lot in advance 
Sample File.xlsx (19.8 KB)
Main.xaml (11.1 KB)
dtinput.AsEnumerable().Where(Function(row) row.Field(Of String)(“Tax Has Employee Tax”) <> (“Y”) AndAlso row.Field(Of String)(“Tax Has Employer Tax”) <> (“Y”)).CopyToDataTable()
Can you try query it will remove the Y rows completely in the both columns
1 Like
Remove the double quotes and keep again at same place it will work
Let me know it’s working or not for you
This worked, Thanks @Praveen_Mudhiraj , I just have one added question here. everytime a new data comes and the workbook needs to be updated. what action should be taken in that case?
1 Like
Super then… Can you close this loop thn if work for you
you can use the append range work book activity to add the data to existing one or merge data table you can use
1 Like
append range workbook is just pasting data from next balnk column . I want it to be pasted by removing original data
Create a new workbook instead of appending
Because when you try to overwrite on top of the existing records it might leave the previous records as it is