cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YMN
Frequent Visitor

HTML to Text - Parsing variable length data

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

 

YMN_1-1624954584917.png

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.

 

 

 

 

 



1 ACCEPTED SOLUTION

Accepted Solutions
VictorIvanidze
Memorable Member
Memorable Member

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]

 

 

View solution in original post

7 REPLIES 7
PrasadAthalye
Super User
Super User

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]

 

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community

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. 

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.

 

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community

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. 

 

YMN_0-1625036015588.png

 

VictorIvanidze
Memorable Member
Memorable Member

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]

 

 

View solution in original post

YMN
Frequent Visitor

many thanks for your support! 

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...

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,408)