Convert amount in the right format with different input format

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 :slight_smile:

Thanks in advance.

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

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

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.

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

grafik

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

Extreme Check
grafik

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

Prototypes:
Add namespaces:
grafik

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
grafik

Variables:

Flow with Testseries:
grafik

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:
grafik

This is one of many options focussing on extensibility and control

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

4 Likes

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 :pray:

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.

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