Extract text from Body of an email in Flow

[mark-carlisle]

I want to extract information from the body of an email and populate fields in a SharePoint. An extract of the email is follows;

Details

------------------------------------------------------------

Account Number 99999999

Contact Name Mr A N Other

Email example@domain.com

Telephone 0123456789

Incident Reference XXXXX-XXXXX

I'm using the expression;

substring(body('Html_to_text'),lastIndexOf(body('Html_to_text'),'Account Number '),8)

Which is just populating the SharePoint field with 'Account ' whereas I actually want it to populate with '99999999'. I've tried a few different things to try and add on a value to the lastindexof value but I'm running into various errors.

Any suggestions would be appreciated.

Thanks

[mark-carlisle · Accepted solution]

Found the solution. Sure I tried this previously but working today.

substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Account Number '), 15),8)

[Sam1]

I have email content as below

Resolution Description : Test test test............................................................... can be 500 words

Department : IT Devlopement Team 

File Path: abcd\dba\ddd\fff\\\gggh\hhhh

Each of the content needs to be mapped against lsit column.

I tried with below formula but its also getting the department information as well but i just need Resolution Description

substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Resolution Description'), 19),500)

[mark-carlisle]

I'm assuming here so forgive me if I'm wrong, your Resolution Description can be a variable length, that is it could be a maximum of 500 words?

If thats the case I used the following to account for variable lengths of data;

substring(triggerBody()?['Body'],add(indexOf(triggerBody()?['Body'],'Account Number'),30),sub(indexOf(triggerBody()?['Body'],'Contact Name'),add(indexOf(triggerBody()?['Body'],'Account Number'),30)))

 So based off the above if you use;

substring(triggerBody()?['Body'],add(indexOf(triggerBody()?['Body'],'Resolution Description'),25),sub(indexOf(triggerBody()?['Body'],'Department'),add(indexOf(triggerBody()?['Body'],'Resolution Description'),25)))

The output of the above would be 'Test test test............................................................... can be 500 words' based off your example.

[Anonymous]

Hi,

I'm having a similar problem, I am am trying to write an expression that will extract, from the email body, the date given after 'Start Date' (in this example, 28th December) and add that date it to the 'Start Time' Field in 'Create event (V2)', and do the same for End Date. Even if I could do that, would the date format be compatible or would I need to add a further expression to convert into an acceptable date format (and if so, what might that look like?)

The following leave request has been Approved by your line manager.

Employee: xxxx xxxxxxxx

Leave type: ANNUAL LEAVE

Start date: 28-DEC-17

End date: 28-DEC-17

Any suggestions/help would be greatly appreciated.

Many thanks

Pete

[mark-carlisle]

This should work, obviously replace Start with End for the End Date.

substring(triggerBody()?['Body'],add(indexOf(triggerBody()?['Body'],'Start Date'),30),9)

In terms of the date format being complatible, not used the Create event (V2) action myself so I'd just give it a go. If it does not work you could try using the formatDateTime fuction to convert it to an acceptable format, you'll find more information on that and the schema in general here.

[Anonymous]

Hi Mark,

Thanks very much for your help - much appreciated!

Unfortunately the flow is still failing - the error details are:

The string was not recognized as a valid DateTime. There is an unknown word starting at index 0. clientRequestId: 6e0beff4-c7ad-4817-ab13-e833cd0b3324

I guess that means I need to add the formatDateTime function, but not quite sure where in the expression I should add it - does it matter?

Thanks

Pete

[mark-carlisle]

Can you screenshot the failed Flow with what it is outputting so we can see what it is trying you use as the date time?

[Anonymous]

Hi Mark,

Screen shot below. Does that give you enough info?  Many thanks again for your help.

 

[mark-carlisle]

Sorry was the inputs above that that I needed. I've done this myself though, although I had to run the Body through a Conversion action first because inputs were not showing a date but some of the html within the Body of the email. Structure of the Flow is below, Start and End Date expressions also follow.

substring(body('Html_to_text'),add(indexOf(body('Html_to_text'),'Start Date'),12),9)

substring(body('Html_to_text'),add(indexOf(body('Html_to_text'),'End Date'),10),9)

[Sam1]

I just copy pasted the formula but it didnt work for me .I am getting below error.

Invalid text value. A text field contains invalid data. Please check the value and try again.  

I am keeping entire email content as Html_to_text.

I am thinking if any condition can be applied to read the data and exclude as sson as there is new line. appriciate for any solution.

I am completely new to flow.

[Sam1]

Hi ,

I further observed below lines of formula is working

substring(triggerBody()?['Body'],add(indexOf(triggerBody()?['Body'],'Account Number'),30),sub(indexOf(triggerBody()?['Body'],'Contact Name'),add(indexOf(triggerBody()?['Body'],'Account Number'),30)))

 But how do i get the value from Html_to_text instead of  ['Body'].Please help .I am close to the solution.

[mark-carlisle]

You would need to replace all instances of triggerBody()?['Body'] with body('Html_to_text') assuming your conversion action is titled Html_to_text.

[Sam1]

It worked. Thanks a lot.

I need one help. How to check if the output has null value and update space for null value for  below formulas.

substring(body('Html_to_text'),add(indexOf(body('Html_to_text'),'Alarm Display Id'),20),sub(indexOf(body('Html_to_text'),'Alarm Description'),add(indexOf(body('Html_to_text'),'Alarm Display Id'),20)))

substring(body('Html_to_text'),add(lastIndexOf(body('Html_to_text'),'Severity'), 10),6)

[mark-carlisle]

You could try wrapping the previous expression with this

if(empty(replace with expression),value if true, value if false)

Not tested it but should work. More information on the scehma can be found here https://docs.microsoft.com/en-us/azure/logic-apps/logic-apps-workflow-definition-language.

[Courtenay]

Hi there,

Just for future reference, Parserr, a conenctor available on Microsoft Flow extracts text from emails, PDF, Excel etc

[mark-carlisle]

Yeah I did look at that, unfortunately due to the volume of email this would require a financial commitment that the company would be unwilling to agree to for what we are using it for. So a zero cost solution was required. Would have made things much easier though!

[itkylam]

I tried to format the date without time in Flow:

EXPRESSION Can format it. However WITHOUT THE OPTION of DATE in the Excel and Flow

I used Dynamic Content, the date from excel can found but NO Format option there.

How can I format it?

[Anonymous]

This is pure gold!!!!! You just saved my day THX

/Frank