Unanswered

HTML to Text - Parsing variable length data

(0) Share

Report

Posted on by Community Power Pla...

Hi All,

I have been trying to build a flow that collects the data from an e-mail and writes it into an excel using add a row into a table function.

Other fields are working just fine however, I need to split the last line into the two fields as follows:

Field1: xxxx

Field2: xxxx

Field3: xxxx

Amount to Pay: 4,344,772.60 in USD

I was using the expressions below for big amounts, but the problem is that amount is changing between <100 and 10 of millions.

It was working just fine until I receive small amounts. When amounts are too low like 66.70 in USD columns are shifting as expected and I see something like '66,70 in U' in Amount to Pay column and 'SD' in Currency column.

Amount to Pay:

substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Amount to Pay:'), 14),12)

Currency:

substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Amount to Pay:'), 31),3)

How can I adjust my flow so that it can properly parse the information that I need?

Many thanks.

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

PrasadAthalye on at

Like (1)

Report
Use Split function

For Amount to Pay use below expression

split(split('Amount to Pay: 4,344,772.60 in USD', ':')[1], 'in')[0]

For Currency, use following

split(split('Amount to Pay: 4,344,772.60 in USD', ':')[1], 'in')[1]

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi Prasad,
Thanks for your comment, but amount is changing every time.
For example, next time it may be ' 54.50 ' or ' 19,888,225.20'.

I need a solution that works regardless of how big or small the amount is.

Was this reply helpful? Yes No
PrasadAthalye on at

Like (1)

Report

Please implement the solution and test. It will work regardless. We are splitting on 'in' word. So it does not matter how big is amount. Text will be splitted into 2. One before in word and one after.

It is not using Substring rather its using split.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi Prasad,
Many thanks for taking the time solve my issue, it's much appreciated.
I implemented your code but unfortunately it didn't work.
Amount and Currency remained constant, no matter what I put in the e-mail.

I changed the amount to random numbers twice and changed the currency once to EUR, but still flow added same amount and same currency.

Was this reply helpful? Yes No
Verified answer

VictorIvanidze 13,073 on at

Like (1)

Report

Use these expressions:

Amount to Pay:
split(substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Amount to Pay:'),14)),' in ')[0]

Currency:
split(substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Amount to Pay:'),14)),' in ')[1]

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

many thanks for your support!

Was this reply helpful? Yes No
PrasadAthalye on at

Like (1)

Report

I am curious to know what you implemented... I gave you example considering a particular text. I guess you should have replaced actual hardcoded text to your expression body('Html_to_text'). Anyways, I am glad issue is resolved...

Was this reply helpful? Yes No