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


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.

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 ^[\w]{12}$. Then use a “remove data row” activity to remove the rows that don’t match your criteria.TwelveTest.xaml (10.7 KB)

1 Like

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

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

@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

Check out this post for a solution for this.

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.

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

TwelveTest.xaml (11.6 KB)

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

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

Main.xaml (411.2 KB)

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.

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
YourOriginDatatableVar.AsEnumetable.Where(function (row) row(yourcolnameOrIndex).ToString.Length <> 12).CopyToDataTable()

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