Delete row in excel if it contains aprticular element

Help
, , , ,
1

Hi everyone,

I have a big excel file in which I need to filter the rows if it conatins a particular value. For Eg.-
In below image I need to remove all the rows having math in sub. column.
image

2

Anyone, Please it’s urgent…

3

Try this and do let me know if it’s works -

(From d In DT1.AsEnumerable
Where d("sub").ToString.Trim.Equals("math")
Select d).CopyToDataTable
4

having math could be interpretated as columnvalue.equals(“math”) or ColumnValue.Contains(“math”)

Based on @indrajit.shah well formulated statement you can do

(From d In DT1.AsEnumerable
Where Not d("sub").ToString.Trim.ToUpper.XXX("MATH")
Select d).CopyToDataTable

XXX = contains or equals

It does filter out / remove all rows containing/equals “math”

2 Likes
5

Hi @indrajit.shah
If I am not wrong, this looks like an SQL query how can I use it in UIPATH. and I want to delete the complete rows which have “MATH” in the “sub” column, trim will just remove math I guess.

6

I was thinking of looping all the rows and using regex match and delete those rows, it may take time but will do the job, so for this I tried “MATH$” in match regex, but it deleted all the rows.
Secondly, I was thinking that any better activity if you know please suggest to me

7

@Krishna_Sanghi
it LINQ not SQL
have a look here:

it can be used within an assign statement:
left side: yourResultDataTableVar (datatype: datatable)
right side: the statement from above

feel free to explore following as well:

2 Likes
9

Surprising that column name and filter value differs from your sample from above.
However if the statement is not working with your data then do following:

  • debug and analyze the flow (use breakpoints, step wise debugging).
  • Check the datatablevar before and after statement. Check the values inside the datatablevar Column (User status) if the values are Pend (and only Pend, equals is strict).
  • For analysis reason you can run it also with a contains statement and check if the result is different.
10

Hi @ppr
It worked I put equals initially, but after changing it to contains it worked…
Thank you

11

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