Is there a way to extract the account no values(excluding special characters that are not numeric or alphabets)?

Help Community
,
2

Hi @Justine ,

Could you give this sequence a try?

System.Text.RegularExpressions.Regex.Matches(CurrentRow(0).ToString,"(\d\.\d+)")

ExtractNumbersWithoutSpecialChars.xaml (10.7 KB)

Kind Regards,
Ashwin A.K

1 Like
3

HI @Justine

You can use this

System.Text.RegularExpressions.Regex.Matches(“1.12345678/2.35548674”,“\d+.\d+”)

This will give all matches you can loop or use index to access the results

cheers

1 Like
4

with regex we can try:
grafik

1 Like
5

HI @Justine

Check out the XAML file

ExcelDataFilter.xaml (10.4 KB)

Regards
Gokul

1 Like
6

@Gokul001 , @Anil_G @ashwin.ashok ,@ppr thanks for the help, the regex expression works perfectly! Much appreciated. is there a dynamic way to insert Assign activity? Because, there are cases whereby there will be more than 2 accounts

7

Hi @Justine

This regex will extract how many ever values come in that format.

You can use a loop to iterate through all the results

cheers

8

I’ve handled that as well, could you give it a try and let us know if it works out for you?

Kind Regards,
Ashwin A.K

9

Hi @ashwin.ashok , sorry somehow I am not able to open the file after downloading it…

10

Noted @Anil_G , will take a look later. Again, appreciate the help

1 Like
11

Have you checked this XAML file? @Justine

12

Hi @Gokul001 , your XAML works perfectly but I would need to insert more than 2 variables to cater for the number of accounts no. There are chances whereby there will be more than 2 accounts provided.

Hence, i would need something more dynamic

13

hi @Anil_G , i tried the following:

System.Text.RegularExpressions.Regex.Matches(“1.12345678/2.35548674”,“\d+.\d+”)
[/quote]
and changed my variable to MatchCollection.

somehow this error popped up

RemoteException wrapping System.InvalidOperationException: Can not assign ‘System.Text.RegularExpressions.Regex.Matches(“1.12345678/2.35548674”,“\d+.\d+”)’ to ‘accts1’. —> RemoteException wrapping System.NotSupportedException: Expression Activity type 'VisualBasicValue1' requires compilation in order to run. Please ensure that the workflow has been compiled. at System.Activities.Expressions.CompiledExpressionInvoker.InvokeExpression(ActivityContext activityContext) at Microsoft.VisualBasic.Activities.VisualBasicValue1.Execute(CodeActivityContext context)
at System.Activities.CodeActivity1.InternalExecuteInResolutionContext(CodeActivityContext context) at System.Activities.Runtime.ActivityExecutor.ExecuteInResolutionContext[T](ActivityInstance parentInstance, Activity1 expressionActivity)
at System.Activities.InArgument1.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance activityInstance, ActivityExecutor executor) at System.Activities.RuntimeArgument.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance targetActivityInstance, ActivityExecutor executor, Object argumentValueOverride, Location resultLocation, Boolean skipFastPath) at System.Activities.ActivityInstance.InternalTryPopulateArgumentValueOrScheduleExpression(RuntimeArgument argument, Int32 nextArgumentIndex, ActivityExecutor executor, IDictionary2 argumentValueOverrides,
Location resultLocation,
Boolean isDynamicUpdate)
at System.Activities.ActivityInstance.ResolveArguments(ActivityExecutor executor,
IDictionary`2 argumentValueOverrides,
Location resultLocation,
Int32 startIndex)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor,
BookmarkManager bookmarkManager,
Location resultLocation)
— End of inner exception stack trace —
at System.Activities.Statements.Throw.Execute(CodeActivityContext context)
at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance,
ActivityExecutor executor,
BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor,
BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor,
BookmarkManager bookmarkManager,
Location resultLocation)

attaching my variables

Capture
Capture999×189 10.7 KB

1 Like
14

@Anil_G , do I need to use a for each matches to loop the results?

15

Hi @Justine

Please replace inverted comma(“) in your code. I guess you copy pasted from here

You can use a for loop on accts1 variable and make sure you change type argument to match.

Then inside use a log message and give currentitem.value which will contain the value extracted

Cheers

16

hi @Anil_G , an error pop up stating when i change acct1 variable to Match type, so in the end i have to change it to Match Collection.

i try writing a log message currentItem.value it says the value is not a member of MatchCollection.

i try changing it to currentItem.tostring it pop out this error message:

RemoteException wrapping System.InvalidCastException: Unable to cast object of type ‘System.Text.RegularExpressions.Match’ to type ‘System.Text.RegularExpressions.MatchCollection’.
at UiPath.Core.Activities.ForEachBase1.ContinueExecuting(NativeActivityContext context) at UiPath.Core.Activities.ForEachBase1.InternalExecute(NativeActivityContext context)
at UiPath.Core.Activities.ForEachBase`1.StartLoop(NativeActivityContext context)
at UiPath.Core.Activities.InterruptibleLoopBase.Execute(NativeActivityContext context)
at System.Activities.NativeActivity.InternalExecute(ActivityInstance instance,
ActivityExecutor executor,
BookmarkManager bookmarkManager)
at System.Activities.ActivityInstance.Execute(ActivityExecutor executor,
BookmarkManager bookmarkManager)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor,
BookmarkManager bookmarkManager,
Location resultLocation)

17

Hi @Justine

You misunderstood what i have told

Please see the below screenshots

let acct1 be a Matchcollection only

image
image1378×678 37.5 KB

cheers

18

hi @Anil_G , there is no errors but the account no are printing as follows after using the following regex expression: System.Text.RegularExpressions.Regex.Matches(“1.12345678/2.35548674”,“\d+.\d+”)

1.12345678
2.35548674

instead of printing the account no shown in the excel such as :
1.14407922
1.14535051
1.15523965
1.15523978

19

Hi @Justine

“1.12345678/2.35548674” this is the string that is being passed in the assign if you want to split what is there in excel then read the excel into datatable…run a for each loop and pass currentrow(“Youraccountnumbercolumnname”).Tostring

then it will get the account numbers

eg: System.Text.RegularExpressions.Regex.Matches(currentrow(“Youraccountnumbercolumnname”).Tostring,“\d+.\d+”)

Note: Do this inside for each row in datatable loop.

Keep assign and the for each(already created) inside the for each row in datatable loop

cheers

1 Like
20

Hi @Anil_G , alright got it. I was too dumb to change that variable. Thanks again, everything works perfectly!

1 Like
21

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