MDias
(Malinda Dias)
August 27, 2019, 11:01am
1
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-
Read Range activity and store data into a datatable
Loop through the datatable
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}
If condition is false then use Remove Data Row Activity
Store data back to the excel.
#HappyRobotics
MDias
(Malinda Dias)
August 27, 2019, 12:02pm
3
@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}")
indra
(Indra)
August 27, 2019, 12:06pm
5
@MDias You can use for each row activity instead of do while
MDias
(Malinda Dias)
August 27, 2019, 12:19pm
6
@vikaskulhari and @indra Thanks for the reply.
Please find attached error
@MDias , add System.text.regularexpression in the import
amaresan
(Amaran)
August 27, 2019, 1:16pm
9
@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,“{4}\d{7}$”)).CopyToDataTable
it returns Table vaiable
MDias
(Malinda Dias)
August 28, 2019, 6:09am
10
@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.
samir
(Samir Mohite)
August 28, 2019, 7:18am
11
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.
“[a-zA-Z]{4}\d{7}” —> If you want any 4 letters followed by 7 numbers. (abcd1234567)
OR
“([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.
MDias
(Malinda Dias)
August 28, 2019, 7:44am
12
@samir
Thanks for the reply but i’m getting below error when trying open project file you sent.
amaresan
(Amaran)
August 28, 2019, 7:54am
13
@MDias
check & let me know
Book1.xlsx (11.2 KB) Main.xaml (7.6 KB) project.json (679 Bytes)
2 Likes
samir
(Samir Mohite)
August 28, 2019, 7:54am
14
hey @MDias , I’ve edited and replaced the zip file from my previous post. kindly check that out.
MDias
(Malinda Dias)
August 28, 2019, 8:12am
15
@samir Thanks but same error
MDias
(Malinda Dias)
September 1, 2019, 4:01pm
17
@samir
it should give 1st and 2 nd format output only 3rd one is wrong format.
samir
(Samir Mohite)
September 3, 2019, 9:32am
18
Dude @MDias ,
samir:
“[a-zA-Z]{4}\d{7}” —> If you want any 4 letters followed by 7 numbers. (abcd1234567)
OR
“([a-zA-Z]|\d){11}” —> If you want 4 letters and 7 numbers at any position. (a1b2c3d4567)
that’s what i’d said no. if you dont want that 3rd one then use pattern as —> “[a-zA-Z]{4}\d{7}”
NewIsMatch.zip (18.7 KB)
system
(system)
Closed
September 6, 2019, 9:47am
20
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.