Get all payment matches in section of invoice regex

[Destiny_Malone]

EDITED:

I need all currency in the “Payment & Adjustment” section only.

DO NOT CREATE REGEX THAT ACCOUNTS FOR NEGATIVE NUMBERS, PLEASE. I do not the $ symbol, just the currency.

-17.10
100.00
7.09
20.44
-32.10
-54.00
50.00
50.00
50.00
103.44
30.00

EX:
blah blah

Payments & Adjustments
Description Amount Total
Mar 22 Payment -$17.10
Mar 22 Payment Reversal $100.00
Apr 01 Adjustment $ 7.09
Apr 10 Adjustment $20.44
Late Fee Credit -$32.10
Credit - Care -$54.00
NONRETURN_FEE $50.00
NONRETURN_FEE $50.00
NONRETURN_FEE $50.00
Service $103.44
CK 160.00 RETURNED; REASON $30.00
Total Payments & Adjustments -$117.10

Service Summary
Service Amount Total
Internet Service $106.40
Total Current Charges $106.40

Details
Qty Amount Total
Charges (Apr 01 - Apr 30)
Internet - Gigabit $85.00
Rental $10.00
Rent - blah ($5 ea) 2 $10.00
Total Charges (Apr 01 - Apr 30) $105.00
Taxes and Fees
State Sales Tax $1.40
Total Taxes and Fees $1.40
Total for Service $106.40

Page 3 of 4

[raja.arslankhan (Raja Muhammad Arslan khan)]

@Destiny_Malone do you want to extract negative sign as well

[argin.lerit]

@Destiny_Malone - Check out the attached XAML and see if this works for you:

Charges.xaml (8,4 KB)

The RegEx used:

collMatches = System.Text.RegularExpressions.Regex.Matches(strInput, "((-)*.\d+.\d+)")

Then I dropped the last item in the Match Collection with:

enumMatches = collMatches.Take(collMatches.Count - 1)

Where enumMatches is of type:

Also removed the $ before using the values:

Hope this helps!

[Destiny_Malone]

No. Going to add these values to a Data Table and later convert to a double for positive or cdbl if it is a negative.

[Destiny_Malone]

It is an invoice so there will be multiple currency values throughout the PDF page. I was hoping there was a way to just get the strings in this section alone. (Begins with, read ahead/behind, get values in between, etc.)

[argin.lerit]

@Destiny_Malone - Try this instead:

System.Text.RegularExpressions.Regex.Matches(strInput, "(?!Description Amount Total|\n)(\d+\.\d+)(?=Total Payments & Adjustments|\n)")

Charges.xaml (8,0 KB)

[Destiny_Malone]

This would get every currency on the page. Is there a way to get up until but not including Total Payments?

[argin.lerit]

Are the keywords Description Amount Total present in other places in the page? @Destiny_Malone

EDIT: Okay, I see what you mean:

[Destiny_Malone]

Use this example:

blah blah

Payments & Adjustments
Description Amount Total
Mar 22 Payment -$17.10
Mar 22 Payment Reversal $100.00
Apr 01 Adjustment $ 7.09
Apr 10 Adjustment $20.44
Late Fee Credit -$32.10
Credit - Care -$54.00
NONRETURN_FEE $50.00
NONRETURN_FEE $50.00
NONRETURN_FEE $50.00
Service $103.44
CK 160.00 RETURNED; REASON $30.00
Total Payments & Adjustments -$117.10

Service Summary
Service Amount Total
Internet Service $106.40
Total Current Charges $106.40

Details
Qty Amount Total
Charges (Apr 01 - Apr 30)
Internet - Gigabit $85.00
Rental $10.00
Rent - blah ($5 ea) 2 $10.00
Total Charges (Apr 01 - Apr 30) $105.00
Taxes and Fees
State Sales Tax $1.40
Total Taxes and Fees $1.40
Total for Service $106.40

Page 3 of 4

[postwick (Paul Ostwick)]

@Destiny_Malone

Going to add these values to a Data Table and later convert to a double for positive or cdbl if it is a negative.

This doesn’t make any sense. CDbl is how you convert to a double. If your datatable column is double, you’ll need the - sign for the negative numbers.

[Destiny_Malone]

Reading from PDF invoice as strings.
Data Table column is a string being parsed and converted to a double.
If string has “-”, it is being converted to CDbl to add “-”.
I’ve done this for page 1 & 2 so far.
Currently trying to figure out an easy way to get the data needed from page 3-4.

[postwick (Paul Ostwick)]

@Destiny_Malone

If string has “-”, it is being converted to CDbl to add “-”.

CDbl isn’t something you convert to. It’s a method that you use to convert a string to a double. String and double are datatypes, types of values/variables. Using CDbl to convert a string to double doesn’t add “-” unless the “-” is on the front of the string already. A double (ie number with decimal places) can be positive or negative.

[Destiny_Malone]

Thank you.

[Steven_McKeering]

Do you have what you need @Destiny_Malone?

If so, please mark the correct post as a solution for others to find

Cheers

[Destiny_Malone]

I do not have a solution yet.

[Destiny_Malone]

I need the currency strings under the “Payments & Adjustments” section ONLY. I’d like a collection that can be added to a DataTable with columns “Payment/Adjustment, Total Payments & Adjustments, and Account Number”. I have the values for the last two columns. I do not want to make a column for every potential payment, fee, or adjustment but I would like the dt to add each value from collection in for loop.

I will edit the post to give an example of what the data looks like to go with the question being asked.

DT_RESULTS EX:

Payment/Adjustment, Total Payments & Adjustments, Account Number
71.00, 73.00, 999999999
1.00, 73.00, 999999999
1.00, 73.00, 999999999
52.00, 52.00, 989898989
0.01, 0.02, 787878787
0.01, 0.02, 787878787

[Steven_McKeering]

Hello

Please do edit and maybe bold the values you need collected.

Once done, you will have a solution in no time

[Destiny_Malone]

The post has been edited for what values are needed in the section I’d like. Note there are multiple sections with currency values as well as charges with a currency amount inside that should be ignored through Regex. Spacing/negative/$ symbols should all be accounted for and ignored as well with Regex.

[Steven_McKeering]

Hi @Destiny_Malone

You can achieve it like this:
Say your input text is called ‘str_InputText’

Step 1: Get ‘Payments & Adjustments’ text separate
Get all the values separated from the main body of text:

Left Assign:
str_PayAdjustments

Right Assign:
System.Text.RegularExpressons.Regex.Match(str_InputText,(?<=Payments & Adjustments)[\s\S]+(?=Total Payments & Adjustments)).ToString

Preview the first regex pattern here

Step 2: Get the values you have bolded
Now you can safely collect the values at the end of each line.

Insert a Matches activity:
Input: str_PayAdjustments (the variable from step 1)
Pattern: [\d\,\.]+(?=[\n\r]+|$)
RegexOption: Select/enable Multiline.

Result: create the output variable ‘ien_Matches’

Preview the second Regex pattern here

Then use a For each to process each of the results into your datatable.

Hopefully this helps

Cheers

Steve

[Destiny_Malone]

Getting this error when outputing value for regexMatch2 using match1.tostring