Answered

Extracting Data from Standarised Emails

(0) Share

Report

Posted on by WCEDsystems

This is a standarised acknowledgement email that I've currently created a flow to extract the key data from:

"

Notification (111R-11111111-01) is acknowledged.

Licence No.: XXXXXX
Licence Class: XXX
Licence Name: XXXX XXX XXX
Licence Expiry Date: 24/09/2028
A.B.N.: 11111111111
Site Details: 11 Xxxxx Street Xxxx XXX 1111, 11, Xxxx Street, Xxxx, XXX, 1111
Start Date of Work: 13/11/2023
Finish Date of Work: 18/12/2023

Click Here to view the notification details.

XXXXXX XXX may acknowledge requests to waive the 5 day notification period in exceptional circumstances where xxxx or xxxx xxxxxx x xxxxx xxxxx and xxxxx xxxx xx xxx xxxxxx or xxxxxx. xx xxxxxx xxxxxxx to confirm your reasons for requesting a waiver for xxxxxx xxxx xxxxxxx xxxx xxxx xx xxxxxxxx xxxxxxxxx xxxxxxxxxxxxxxxx xxxxxx xxxxxxxxx xx xxxxxxxx xxxxxx xxxxxxxxx. You will need to provide a separate email to xxxxxx@xxxxxx.com requesting the waiver with the reasoning and supporting documentation. You must xxx xxxx xxxx xxxx xxx xxxxxx has been accepted by XxxxxXxx XXX and advised by email.

Kind Regards

Xxxxxx xxx Xxxxxxx Xxxx
XxxxXxxx XXX
1111 111 111

I've created a flow that captures the notification ID and the dates by Using Html To Text and then creates an item in sharepoint. It seems though that it is unstable (sometimes it works, sometimes it doesn't). Would you be able to recommend a better solution? To grab the data I need from this I used compose with split:

split(body('Html_to_text'),outputs('EnterKey'))

split(split(outputs('SplitBodyText')[0], '(')[1], ')')[0]

split(outputs('SplitBodyText')[7], 'Site Details: ')[1]

split(outputs('Site_Address'), ',')[0]

split(outputs('SplitBodyText')[8], 'Start Date of Work: ')[1]

split(outputs('SplitBodyText')[9], 'Finish Date of Work: ')[1]

Is there a better way to do this? The flow is sometimes unstable - I've tried to correct it, it didn't work and then when I reset it to the original expressions it worked. Is this a bug? Is there a better way to do this?

Screen 1.png

Screen 3.png

Screen 2.png

All responses (2)

Answers (2)

Verified answer

grantjenkins 11,059 Super User 2025 Season 1 on at

Like (0)

Report
Re: Extracting Data from Standarised Emails

I would probably use a Filter array to clean up any empty rows of data prior to using the expressions.

For the Filter array, I'm using the following expressions.

//Body - split on new line split(outputs('Html_to_text')?['body'], decodeUriComponent('%0A')) //Condition trim(item()) //The raw condition would look like the following @not(equals(trim(item()), ''))

Then I would use the following expressions to extract the values.

//Notification first(split(last(split(body('Filter_array')?[0], '(')), ')')) //Licence No trim(last(split(body('Filter_array')?[1], ':'))) //Licence Class trim(last(split(body('Filter_array')?[2], ':'))) //Licence Name trim(last(split(body('Filter_array')?[3], ':'))) //Licence Expiry Date trim(last(split(body('Filter_array')?[4], ':'))) //ABN trim(last(split(body('Filter_array')?[5], ':'))) //Site Details trim(last(split(body('Filter_array')?[6], ':'))) //Start Date of Work trim(last(split(body('Filter_array')?[7], ':'))) //Finish Date of Work trim(last(split(body('Filter_array')?[8], ':')))
Verified answer

v-xiaochen-msft on at

Like (0)

Report

Re: Extracting Data from Standarised Emails

Hi @WCEDsystems ,

According to your screenshot , the error code is this:

The reason for this error is because your value exceeds the length of this array. For example, if the array is [1,2,3] and you take Array[4], this error message will appear.

So the best way is to add '?' before your [n] like this:

split(outputs('SplitBodyText')?[8], 'Start Date of Work: ')?[1]

That way, if he doesn't find a value, he'll return Blank. We can also use the Coalesce() to return a default value if there is no value return.

Best Regards,

Wearsky