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

Hi All,

Would like to check whether is it possible to extract the account no values(excluding special characters that are not numeric or alphabets)?

Currently, i am working on a workflow to extract all the Accounts No if Column F is “Yes”, therefore if string equals to 1.14407922&1.14535051, i need the bot to extract the 2 account nos: 1.14407922 and 1.14535051 and then pass through a loop afterwards…

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

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

with regex we can try:
grafik

1 Like

HI @Justine

Check out the XAML file

ExcelDataFilter.xaml (10.4 KB)

Regards
Gokul

1 Like

@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

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

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

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

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

1 Like

Have you checked this XAML file? @Justine

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

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

1 Like

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

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

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)

Hi @Justine

You misunderstood what i have told

Please see the below screenshots

let acct1 be a Matchcollection only

cheers

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

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

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

1 Like