How to remove rows that does not have a certain amount of integers?

Help
, ,
1

Hello,

How can I remove rows from a data table (excel) that doesn’t have 12 characters in the data cell? I’m trying to use the Filter Data Table activity and so far no luck with “Does Not contain” under operation and marked for a certain column with “xxxxxxxxxxxx” Thank you.

2

You should be able to read the cells you want to check using a “for each row” activity and then use a decision and a “is match” activity with the regex expression [1]{12}$. Then use a “remove data row” activity to remove the rows that don’t match your criteria.TwelveTest.xaml (10.7 KB)

  1. \w ↩︎

1 Like
3

You do not need to use regular expressions here. Instead, you can simply check the length of the string using: row("<column_name>").ToString.Length <> 12

Also, it’s not a good idea to delete items from a collection while you’re iterating through them. What you are suggesting would cause an error. Instead, you should probably create a new DataTable using the select method.

Here’s a working example: Test_NotTwelve.xaml (7.7 KB)

1 Like
4

Added an example xaml so you can check out both proposed solutions. Good luck!

5

@TomG thank you. i’ll test yours out as well. however it’s giving me an error of the version of the bot and Studio don’t match

6

Check out this post for a solution for this.

7

So I am getting errors on both solution. @dmccammond the process with the excel is giving an error and won’t go pass the first step. maybe because you built your data table and i’m using an excel?

@TomG I am getting an error on the last step. please see the picture.

removal%20row%20error
removal%20row%20error766×288 16 KB

8

Yup, I forgot the assign to initialize the collection. Updated xaml attached.

TwelveTest.xaml (11.6 KB)

9

it ran all the way through but it is still not removing the rows on the certain column with data cells less than 12 characters. That’s weird

10

Here is the XAML and picture of where in the process i’m trying to remove the rows.

error%20removal
error%20removal837×674 21.9 KB
Main.xaml (411.2 KB)

11

I don’t have an activity in my example where the data table with the rows removed is rewritten to a physical file. you’d need to add this to see the rows removed on a physical file.

12

@Sugaray
Removing rows that Not have 12 Characters length in a particular column can be done within an assign activity and following linq Statement.

Use assign Activity
To yourdatatablevar
Value:
YourOriginDatatableVar.AsEnumetable.Where(function (row) row(yourcolnameOrIndex).ToString.Length <> 12).CopyToDataTable()

13

Try with “contains” or “=” the value “xxxxxxxxxxxx” in Filter Data Table