Convert amount in the right format with different input format

[Suldarie]

Hi everyone,

I have an automation project with data extracted from invoices. Those PDF invoices come from different countries. So when my robot retrieves the amount from these invoices, sometimes the format can be (and this is an example) 10,000.00 or 10.000,00 or even 10 000.00 etc.

The thing is that my data will go into an app where the format has to be 10000.00

I don’t know how I can manage the fact that I have so many format in input in order to obtain one only format in output.

.Replace is not great as sometimes my decimal separator is “,” and sometimes “.”

I hope someone will be able to help me

Thanks in advance.

[ppr (Peter)]

in such scenario we detect the format with the help of regex, let calculate the needed locals and then parse it with the corresponding CultureInfo.

When you need more help for this then please share the maximum on variations which you have

@Suldarie

.Replace is not great

Absolutely agreed on this, as doing such approach can lead to bad side effects and hidden damage

[Suldarie]

Thank you for your reply.

So far I have these different formats (this is just an example with 10000 but we can aso have 100 or 100000, etc.):

10000.00
10,000.00
10 000.00
10.000,00

Hopefully I won’t have more later when I include new kind of invoices.

[ppr (Peter)]

The Idea:
Use regex to detect the format
With the help of Regex Groupname we calculate the CultureInfo

we do see 10 000.00 as uncatched (currently no matching Culture Info is known)

Extreme Check

But (we can cross safe if needed) when removing space we can simplify

Prototypes:
Add namespaces:

Get Matching Group Name (unoptimized ProtoType)

if no or more Group names were found the Single() method throws an exception

use the calculated info and parse it with the corresponding CultureInfo

Variables:

Flow with Testseries:

myMatch = myRegex.Match(item.replace(" ",""))
myGroupName = myRegex.GetGroupNames.Skip(1).Where(Function (x) myMatch.Groups(x).Success).Single
myDouble = Double.Parse(item.Replace(" ",""), dictCultureLK(myGroupName))

Logs:

This is one of many options focussing on extensibility and control

Find starter help here:
DoubleParsing_MultiNationalFormats.xaml (9.2 KB)

[Suldarie]

Waouh,

Thank you so Much for all the work.

I’m gonna try this tomorrow and Will let you know if it is Working from my Side.

Thank you again

[Suldarie]

It seems that it does the job.

I have tested with a lot of different numbers.

Thanks again for your time and your valuable help.