Delete Rows not contains 4 letters and 7 digits from a Datatable

Hi All,

I’m new to Ui Path and i have below question.

I want to delete the entire row if not matching below format.

This row details in the specific column(PO Number) should contain 4 letters and 7 digits( Numbers) if anything contains other than that format should delete that entire row.

Thanks in advance for your support.

Welcome to the community @MDias.

There are a several ways but you can use this approach-

  1. Read Range activity and store data into a datatable
  2. Loop through the datatable
  3. Use If condition inside the loop and the whether the format is as desired or not. You can regex or string manipulation to retrieve the format. You may use this regex [a-zA-Z]{4}\d{7}
  4. If condition is false then use Remove Data Row Activity
  5. Store data back to the excel.

#HappyRobotics

@vikaskulhari Thanks for the reply.

I can use read range and store data into a DT1
Then i can use do While and can you advise what should I mention in the if condition.
If
DT1=Datatable
ctp_Loop=Row
“PO number”=Column
Correct patten =ABCD1234567

Thanks,

@MDias, use for each loop, not the while loop. and inside if condition put -
regex.match(row("PO number"), "[a-zA-Z]{4}\d{7}")

@MDias You can use for each row activity instead of do while

@vikaskulhari and @indra Thanks for the reply.
Please find attached error

@vikaskulhari

  1. you need to import System.Text.RegularExpressions

  1. Add variable regex with s type System.Text.RegularExpressions.Regex

@MDias, add System.text.regularexpression in the import

@MDias

Use Assign Activity in that use below code

No Need For each

Dt.AsEnumerable().Where(Function(x) System.Text.RegularExpressions.Regex.IsMatch(x(“Po number”).ToString.Trim,“[1]{4}\d{7}$”)).CopyToDataTable

it returns Table vaiable


  1. a-zA-Z ↩︎

@amaresan Thanks for the reply.
I’m new to Uipath so really appreciate if you can send a sample workflow so i can follow it.

@vikaskulhari and @indra even i added as you showed getting the same error.

hello @MDias,

I’ll suggest you to use IsMatch method of RegEX, which is use in getting Boolean value (True OR False) with specific pattern.

  1. “[a-zA-Z]{4}\d{7}” —> If you want any 4 letters followed by 7 numbers. (abcd1234567)

OR

  1. “([a-zA-Z]|\d){11}” —> If you want 4 letters and 7 numbers at any position. (a1b2c3d4567)

So use this pattern in IsMatch on “PO Number” to get boolean values like this —>

bool1 = (System.Text.RegularExpressions.Regex.IsMatch(row(“PO Number”).tostring, “([a-zA-Z]|\d){11}”))

and then use that boolean value to delete row from datatable using Remove Data Row activity by giving it datatable variable and rowindex.

Here’s the sample —> NewIsMatch.zip (18.3 KB)
I hope it’ll help you. :+1:

@samir
Thanks for the reply but i’m getting below error when trying open project file you sent.

@MDias

check & let me know
Book1.xlsx (11.2 KB) Main.xaml (7.6 KB) project.json (679 Bytes)

2 Likes

hey @MDias, I’ve edited and replaced the zip file from my previous post. kindly check that out.

@samir Thanks but same error :frowning:

@MDias

have you check this.?

@samir
it should give 1st and 2 nd format output only 3rd one is wrong format.
image

Dude @MDias,

that’s what i’d said no. if you dont want that 3rd one then use pattern as —> “[a-zA-Z]{4}\d{7}”
image

NewIsMatch.zip (18.7 KB)

Thanks Samir

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