Remove row from a particular string from data Table

Help Studio
, ,
1

Hi All,

How to remove a particular string from a datatable. Its coming in any row while downloading the file from system.

Can some pls guide me quickly.

Thanks
Taruna

2

Hi @taruna.mehra ,

Could you provide us with an Example data ? It would be helpful if you could provide Screenshots/files.

3

Hi,

Its my office work so can not share the data.
If you are downloading an excel file and then you see a string which is coming in any row so we can remove that from the data table.

4

Hi @taruna.mehra

  1. Use **Output DataTable ** activity and pass the DataTable as input and get the output with a variable of type string named Output_String

  2. Use a Assign activity

Output_String= Output_String.ToString.Replace(“YourString”,””)
  1. Again use Generate DataTable activity and pass the string variable as input and get the output variable as DataTable

Regards
Gokul

5

Hi, If i have to remove that complete row wherever that string is coming?

6

Hi @taruna.mehra

How many columns you are having in the excel/

7

1531 and sometimes, 700 its dynamic

8

@taruna.mehra ,

We could use the Below Linq to remove rows that contains the String in any of it’s columns.

DT.AsEnumerable.Where(Function(x)Not(x.ItemArray.Any(Function(y)y.ToString.Contains("YourString")))).CopyToDataTable

Also, take a look at the below post to Handle Errors on Direct CopyToDatatable

9

This a column count or row count ?

Regards
Gokul

10

Sorry yes you are right, columns are 10 or 11

but string is coming in any row

11

Hi @taruna.mehra

Have a look on the XAML file

RemoveParticularData.xaml (7.3 KB)

Regards
Gokul

12

Actually we have columns till “W” or column names are dynamic so then also i will have to write all the columns

Attaching my xaml which i tried. Pls let me know why its not working?
Main.xaml (9.0 KB)

13

Hi, Thanks but i dont want to use linq method.

14

@taruna.mehra ,

If you are not getting any Exceptions while running the xaml, You could perform a Debug and Check the Count of Rows of the datatable before and After the Removal of Rows. You could also Check by using a Log Message or Write Line Activity.

The above is just to Check if the Filter Expression is working correctly or not.

Now, the main issue what I think you are facing is the update in the Excel Sheet itself.
From the current xaml we can see that you are not Writing the Updated datatable back to the excel using Write Range Activity.

You would need to use Write Range Activity after the removal. So that you have the Filtered rows as the output. Also, If you want to write the data back to the same sheet it would require you to clear the rows in the Sheet first and then Write the Updated datatable.

For now, Just check with the minor addition of Write Range Activity.
image

Let us know what is the outcome.

15

Hi Pls send me this xaml

16

@taruna.mehra ,

Check the updated xaml below :
Main (1).xaml (10.0 KB)

17

The issue is still there

image
image1366×768 64.8 KB

18

@taruna.mehra ,

It seems that the Select Filter on the Datatable is not working as expected since there is no Column Hello in the Datatable.

As we still do not know what is the exact condition to apply here, assuming that you want to remove all rows which contain the Particular String in any of the Columns, we can shift to performing a Check in the For Loop and Removing the Rows in the Reverse manner.

Check the updated workflow below :
Main (1).xaml (11.3 KB)

We Reverse the Indexes and perform the Check for that indexed row in the Datatable and If Match we remove the Datarow using RemoveAt Method which requires an index/integer value as a parameter.

image
image787×441 13 KB

Let us know if you are still facing issues.

19

use filter datatable for remove entire row. in filter filter datatable there will be option keep or remove based on condition there enable remove instead of keep

thank you

20

Hi, this is not the column. This is string which is coming in any row.