Can we filter two digit random numeric values from a data table

Help
1

Hi,

Trying to filter a two digit random numeric value from a datatable

tried using like

dbBaseFile.Select(“[Company] = ‘5311’ or [Company] = ‘5301’ and [CustID] like ‘[0-9][0-9]’”

not working

also tried including regex.

Is there any possible way

Regards
Abhinav kaushal

2

As far as I know it’s not possible to use regex in Select. It’s also not easy/possible to use Linq in UiPath, which would be probably the best choice using C#.

In UiPath I would probably iterate with for each and check each row with “Is Match” activity.

2 Likes
3

You could try something like this

regex = new Regex(“[0-9][0-9]”)

dt2 = dt.AsEnumerable().Where(Function(x) regex.IsMatch(x.Field(Of String)(“CustID”))).CopyToDataTable

2 Likes
4

I would even go for something like this.

dt2 = dt.AsEnumerable().Where(Function(x) regex.IsMatch(x(“CustId”).ToString)).CopyToDataTable

Also looks like the pattern you provide is checking if it is not two digits.

i would go for something like this

“\b[0-9]{2}\b” or even “\b[\d]{2}\b”

the \b means boundaries and will check if it’s exactly two digits

Cheers

Note that i didnt the the linq query but i have been using similar things

2 Likes
5

Correct and updated.

6

Hi Guys,

Thanks for the help. This helps a lot.

Also I have to delete the filtered records. Is there any way other than For each row.

IF there is any other way please let me know.

Thanks and Regards

7

Hello,

If you would like to remove all the row matching the logic you asked for previously you would just need to revert the boolean inside the Linq lambde expression like this.

dt2 = dt.AsEnumerable().Where(Function(x) regex.IsMatch(x(“CustId”).ToString) = False).CopyToDataTable

Note that here it is copied to a second datatable but it is perfectly fine to assign it to “dt”, which will overwrite the previous one, in case you would not need it.

dt2 will now contains only the rows you want.

Cheers.

3 Likes
8

Hi,

Thanks this helps a lot

Also i am trying other possibilities also but i am unable to use it with and condition and is throwing error

dbBaseFile.AsEnumerable().Where(Function(z) z(Function(x) x(regex.IsMatch(x(“CustId”).ToString)) and Function(y) (y(“Company”).toString = “5301” or y(“Company”).toString = “5311”))=False).CopyToDataTable

Error:Error1

9

Hello,

You can use a chain of Lamba expression that way.

dbBaseFile.AsEnumerable().Where(Function(y) y(“Company”).toString = “5301” Or y(“Company”).toString = “5311”).Where(Function(x) regex.IsMatch(x(“CustId”).ToString) = False).CopyToDataTable

If you would like to do it without chaining, you can do it only lamba that way

dt.AsEnumerable().Where(Function(x) (x(“Company”).toString = “5311” Or x(“Company”).toString = “5301”) AndAlso regex.IsMatch(x(“CustId”).ToString) = False).CopyToDataTable

Last example which was not mention yet, if you want a real One liner, you can also inline the regex, but it is not that pretty if you have a big datatable because it will instance the Regex for each iteration.

Anyway, assuming that you have thousand rows, we are talking about max few milliseconds

dt.AsEnumerable().Where(Function(x) (x(“Company”).toString = “5311” Or x(“Company”).toString = “5301”) AndAlso New System.Text.RegularExpressions.Regex(“\b[0-9]{2}\b”).IsMatch(x(“CustId”).ToString) = False).CopyToDataTable

Cheers

2 Likes
10

Hi

Thanks for the suggestion. Thanks for being so patient in answering all my queries. learnt a lot of new things.

Also I have one last query for future reference:

In this query I will get all the values that are true for the condition, if I want to remove the rows selected in the current query from my datatable what can be the process.

Also is their any link where i can explore lambda exp and linq learn a few other tricks.

Regards
Abhinav kaushal

11

Hi,

Thanks for the feedback, much appreciated.

Regarding Lambas documentation and links, i don’t have anything specific, it is considered as an advanced topic of .Net and require a lot of practice. In addition, they are difficult to be understood by a lot of people, specially in the RPA World.

I would simply make google search with the keywords (“Linq”,“VB.Net”,“Lambda expressions”).

For Selecting extact oposite of the previous lamba you need to add this.

Assign dbBaseFile = dbBaseFile.AsEnumerable().Where(Function(x) ((x(“Company”).toString = “5311” Or x(“Company”).toString = “5301”) AndAlso New System.Text.RegularExpressions.Regex(“\b[0-9]{2}\b”).IsMatch(x(“CustId”).ToString) = False)=False).CopyToDataTable

If it is not resolving your case you can still attach a workflow and give example of how your initial and filtered DT would look like.

You would also have .RemoveAll available List(of DataRow) but it would not be in one line anymore :

You need to :

  • Create a List of Datarow out of your datatable (Assign dataRowList = dt.Select().ToList)
  • create an integer variable which will return the rows removed.
  • Assign intRemovedCount = dataRowList.RemoveAll(Function(x) (x(“Company”).toString = “5311” Or x(“Company”).toString = “5301”) AndAlso New System.Text.RegularExpressions.Regex(“\b[0-9]{2}\b”).IsMatch(x(“CustId”).ToString) = False)
  • Reasign Dt = DataRowList.CopyToDataTable

Cheers

1 Like