Delete all the rows in sheet, below specific cell value in column2

Help Activities
, ,
1

I want to Delete all the rows in sheet, below specific cell value in column2

Column1 Column2 Column3 Column4 Column5 Column6
A A A A A A
Monday A A A A A
A M.123 A 1 A A
egg A A A A A
A A A A A A
A M.123.NB A A 1 A
A A w A A A
A A A A A A
A D-ABC.GHG.MN A A A A
B B B B B B
CC CC CC CC CC CC
A A A A A A
A A A A A A
P P P Pat P P
A A A A A A
A D-ABC.GHG.12.MN A A Ant A
A A A A A A
A Mango A A A A

Want to delete all rows below :D-ABC.GHG.MN
D-ABC is constant value rest can change

Expected Output Below:

Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8
A A A A A A A A
Monday A A A A A A A
A M.123 A 1 A A p A
egg A A A A A Apple A
A A A A A A A A
A M.123.NB A A 1 A A A
A A w A A A A A
A A A A A A A A
1 Like
2

Hi @S_Nitin

Try this in assign activity:

filteredDataTable = yourDataTable.AsEnumerable().TakeWhile(Function(row) Not row("Column2").Equals("D-ABC.GHG.MN")).CopyToDataTable()
3

Value will vary, only starting char will be same D-ABC will be same

4

@S_Nitin

Try this:

filteredDataTable = yourDataTable.AsEnumerable().TakeWhile(Function(row) Not row("Column2").StartsWith("D-ABC")).CopyToDataTable()
5

Hi

Welcome to UiPath forum

Try with this expression in assign activity

dt = dt.AsEnumerable().Where(Function(row) NOT row(“Column2”).ToString.ToUpper.StartsWith(“D-ABC”)).CopyToDataTable()

This query will remove all rows below the first row where Column2 starts with “D-ABC.”

Hope this helps

Cheers @S_Nitin

1 Like
6

Thank you for your reply but, this query throwing error. I need to delete all rows below D-ABC. If this value appears in column 2 at any row. This will always start with D-ABC and might appear many times below,.

7

Hi @S_Nitin ,

Could you let us know the Error Message received, so that we could analyse and understand the error part ?

8

Hi @Palaniyappan ,

this query deleting the row where column 2 has said value.
But I need to delete everything below it. I need to delete all rows below D-ABC. If this value appears in column 2 at any row. This will always start with D-ABC and might appear many times below.

Thanks for your inputs once again.

9

filteredDataTable = yourDataTable.AsEnumerable().TakeWhile(Function(row) Not row(“Column2”).Equals(“D-ABC.GHG.MN”)).CopyToDataTable()

and

filteredDataTable = yourDataTable.AsEnumerable().TakeWhile(Function(row) Not row(“Column2”).StartsWith(“D-ABC”)).CopyToDataTable()

above 2 queries are not deleting any row.

dt = dt.AsEnumerable().Where(Function(row) NOT row(“Column2”).ToString.ToUpper.StartsWith(“D-ABC”)).CopyToDataTable()

This query deleting only specific row .

10

@S_Nitin

Removingexcelrows.zip (3.1 KB)

check this

place the value which you want to find in the excel like D-ABC

cheers

11

@S_Nitin ,

Could you maybe check with a slight modification of the Expression to the below :

filteredDataTable = yourDataTable.AsEnumerable().TakeWhile(Function(row) Not row("Column2").ToString.Trim.StartsWith("D-ABC")).CopyToDataTable()
1 Like
12

Yeah, this query will only take the rows while encountering the first and store it in new datatable D-ABC @S_Nitin

filteredDataTable = yourDataTable.AsEnumerable().TakeWhile(Function(row) Not row("Column2").StartsWith("D-ABC")).CopyToDataTable()
1 Like
13

Hey @supriya117, this query works fine with some modifications as suggested by @supermanPunch.
Thank you both :slight_smile:

1 Like
14

filteredDataTable = yourDataTable.AsEnumerable().TakeWhile(Function(row) Not row(“Column2”).ToString.Trim.StartsWith(“D-ABC”)).CopyToDataTable()

this is working perfectly as requied :slight_smile:
Thanks !

1 Like
15

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