How to apply filter based on row value digit count

[Raja.G (Mr.RPABot)]

Hi Team,

I want filter one “yyy” column if found 7 digit number start with "002"or "003 or “004” and if 5 digit start with “2” or “3” or “4” we should add in valid sheet else invalid sheet.

Please help me anyone for this.

I have attachment sample for your reference
digit.xlsx (10.7 KB)

Regards,
Raja G

[ppr (Peter Preuss)]

@Raja.G

I want filter one “yyy” column if found 7 digit number start with "002"or "003 or “004” and if 5 digit start with “2” or “3” or “4” we should add in valid sheet else invalid sheet.

Regex Match for the condition check:

(^00[234]\d{4}$)|(^[234]\d{4}$)

[CheatSheet] - System.Text.RegularExpressions | RegEx - News / Tutorials - UiPath Community Forum

Linq (Where operator) for the filtering, using regex.ismatch
[HowTo] LINQ (VB.Net) Learning Catalogue - Help / Something Else - UiPath Community Forum

[Raja.G (Mr.RPABot)]

Hi Team,

Please anyone help me any other approach to process this.

Regards,
Raja G

[ppr (Peter Preuss)]

@Raja.G

other approach to process this

what was tried from your side so far?
which difficulties did you face?

Variables:

dtData is the result of reading in the excel (e.g. read range)

dtFiltered =

(From d In dtData.AsEnumerable
Let v = d(1).toString.trim
Let chk = Regex.IsMatch(v,"(^00[234]\d{4}$)|(^[234]\d{4}$)")
Where chk
Select r = d).CopyToDataTable

feel free to use the columnname d(“YourColName”) instead of the index d(1)

[Sudeen_Raj_Shetty]

Hi @Raja.G
You can use 2 datatables are:

dt_Valid=dt_Input.AsEnumerable().Where(Function(row) System.Text.RegularExpressions.Regex.IsMatch(row(“yyy”).ToString(),
“(00[234]\d{4}$)|([234]\d{4}$)”)).CopyToDataTable()

dt_Invalid=dt_Input.AsEnumerable().Where(Function(row) Not System.Text.RegularExpressions.Regex.IsMatch(row(“yyy”).ToString(),
“(00[234]\d{4}$)|([234]\d{4}$)”)).CopyToDataTable()

And then use them to write in different sheets.

Hoping this will be of use
Thank you

[Achiranga (Achiranga Gunasekara)]

hi yes as @Sudeen_Raj_Shetty meantion you can used linqs .If LINQ is unfamiliar
Main.xaml (22.5 KB)
to you,you can use traditional Loop Approach.i have upload a sample workflow .

[Gowtham_K115 (Gowtham Krishnan)]

Hi @Raja.G

Try the below approach,

How to apply filter based on row value digit count.xaml (22.8 KB)

Output Excel : digit.xlsx (10.8 KB)

Regards,
Gowtham k

[sharazkm32 (Sharaz K M)]

Hi @Raja.G ,

Best and easiest solution would be to use Linq queries only as mentioned by @ppr and @Sudeen_Raj_Shetty

If you are new to this concept - I am giving you the step-by-step details and few minor points to be considered while using the regular expressions

Only 6 steps are involved for this

1. Open the excel file where you need to apply the filter
2. Read the data table and store to a variable (dtTable)
3. Assign new data table variable (example dtvalid) with below regular expression as mentioned above. Make sure you have imported System.Text.RegularExpression in your project imports

dtvalid =
(From d In dtTable.AsEnumerable
Let v=d(1).ToString.Trim
Let chk =System.Text.RegularExpressions.Regex.IsMatch(v, “(^00[234]\d{4}$) | (^[1]\d{4}$)”)
Where chk
Select r=d).CopyToDataTable

4. Assign new data table variable (example dtinvalid) with below regular expression as mentioned above. Only difference to make is in where condition. Where chk=false

dtinvalid =
(From d In dtTable.AsEnumerable
Let v=d(1).ToString.Trim
Let chk =System.Text.RegularExpressions.Regex.IsMatch(v, “(^00[234]\d{4}$) | (^[2]\d{4}$)”)
Where chk=false
Select r=d).CopyToDataTable

5. Write data table to required sheets

---

1. 234 ↩︎

2. 234 ↩︎