Help me with regular expression to filter excel based on the words present on 1st column

Help Activities
, ,
1

Hi all,

Help me with regular expression to filter excel based on the words present on 1st column/.

I have to keep the rows if the row(0) contains/ equals - e.g. India, Europe, Shri Lanka, China, Nepal, nepal, europe, Shri lanka etc, there are 20 such words approx. and other alphanumeric words which might vary.

My question is - shall I simply put -all like below
e…g. dt.AsEnumerble.Where(Funcion(a)System.Text.RegularExpressions.Regex.IsMatch(a(0).ToString, “\b[India|india]\w+|\b[europe|Europe]\w+”)).CopyToDatatable

Please help me to build more accurate expression. as approx 20words are there to mention.

2

Hi @1a2bc346dgjk7544ebjkkbvf

You can try this way:

dt.AsEnumerble.Where(Funcion(a)System.Text.RegularExpressions.Regex.IsMatch(a(0).ToString,"\b([A-Za-z]+\s+[A-Za-z]+)|([A-Za-z])+\b")).CopyToDatatable

Hope it helps!!

3

@1a2bc346dgjk7544ebjkkbvf

arr_str={“India”,“China”}

use array of string and assign all the values to it

dt.asenumerable.where(function(r) arr_str.contains(r(0).tostring.trim)).copytodatatable

hope this helps

4

@1a2bc346dgjk7544ebjkkbvf

Use this Regex

^(?i)(India|Europe|Sri\s?Lanka|China|Nepal)$

Cheers…!

5

One of many options

CheckValues | String List =
new List(Of String) From {“INDIA”, EUROPE,…}

Kindly note: we put all values on UpperCases

Assign Activity
dtFiltered =

(From d in YourDTVar.AsEnumerable
Let c = d(0).ToString.ToUpper
Where CheckValues.Any(Function (x) c.Contains(x))
Select r = d).CopyToDataTable

Handling empty results:
:ambulance: :sos: [FirstAid] Handling of The source contains no DataRows exception - News / Tutorials - UiPath Community Forum

6

You can try this

dt.AsEnumerable().Where(Function(row) System.Text.RegularExpressions.Regex.IsMatch(row("YourColumn").ToString(), "India|Europe|Sri Lanka|China|Nepal", RegexOptions.IgnoreCase)).CopyToDataTable()

Sample : input : -

image

output : -

image

@1a2bc346dgjk7544ebjkkbvf

7

HI @Dilli_Reddy

myDT.AsEnumerable.Where(Function(a)System.Text.RegularExpressions.Regex.IsMatch(a(0).ToString,“\b[A-Za-z]{1}\b|[A-Za-z]+[0-9]+|\d{2,}”)).CopyToDatatable

I already have above expresion which are keeping alphanumeric values, and single aplhabet and some value ending with 2 letters

I want to add in same expression…

8

Hi @1a2bc346dgjk7544ebjkkbvf ,

The requirement was to check specific words like you have mentioned a defined list of approx 20.

But what you have used is more generic and it could match any words and not specific to the words in the defined list.

So we would ask you to analyse again and let us know what is actually required to be performed.

On the other hand, if a Concatenated Condition was required then after the regex match we could check if the value matched is within the defined list like below :

myDT.AsEnumerable.Where(Function(a)YourDefinedList.Any(Function(x)System.Text.RegularExpressions.Regex.Match(a(0).ToString,"\b[A-Za-z]{1}\b|[A-Za-z]+[0-9]+|\d{2,}").Value.ToLower.Contains(x.ToLower))).CopyToDatatable
1 Like
9

@supermanPunch
Happy birthday !!

Thanks for your reply, actually you helped me earlier with the mentioned expression and I tried to add in same, but since words are 20 I thought to check here. I can keep the words in array. tried adding array but not working. Those words can be caps , small letters too but yes I will have the word list in advance which I can add in array.

Sample input is attached.

TIA.
Sample.xlsx (8.9 KB)

10

we did here and harmonized the cases:

As an alternate we can also create the Regex Pattern String dynamic from the array / list with the values

1 Like
11

Hi @ppr , Yes it’s working :slight_smile: However; I have to combine it with few more conditions.
I have attached in sample.xlsx above.It will be great help if we could add all conditions in 1 expression.

Thanks a lot for your reply :slight_smile:

12

Excel is different from initial told requirement

As it is not a discrete list and also have pattern where pattern matches are in scope or out of scope.

13

@1a2bc346dgjk7544ebjkkbvf ,

Could you maybe check with the below Expression :

myDT.AsEnumerable.Where(Function(a)System.Text.RegularExpressions.Regex.IsMatch(a(0).ToString,"\b[A-Za-z]{1}\b|[A-Za-z]+[0-9]+|\d{2,}") OrElse YourDefinedList.Any(Function(x)a(0).ToString.Contains(x))).CopyToDataTable

Here, YourDefinedList is a variable of type List of String/Array of String.

Debug Panel :
image

Thank you for the wishes :slightly_smiling_face:

1 Like
14

@supermanPunch
Thanks a lot !
It’s working as expected :slight_smile:

1 Like
15

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