Extract and parse data from an email

Django · ‎05-19-2020

Title: Data extraction and parsing from an email

Description: This Flow triggers on an email received to check its body on a dynamic number of keys to find its corresponding value. The trick is in an Apply to each loop based on an array you can manage from within the flow. The flow will find the keys and values you want to extract and parse.

Detailed Instructions: It’s as easy as importing the zipped flow attached, add your connection for Outlook Office 365 and it will trigger on your own inbox emails. I recommend additional filters (like a specific unique phrase in the subject or only a specific mailbox folder) so the flow will only trigger on emails that it should trigger on. Keep in mind that the flow expects a specific key to find and you can change the key in the array step of the flow. You can even change the key-value separator as explained in the video.

Questions: Please feel free to ask anything right here as a reply in this post.

watch?v=e_8hQibvbYQ

MikhailPeru · ‎06-12-2020

Hi Django,

Firstly, thanks a lot for sharing. I'd like to ask you just one additional question.

If I have a table in the email (not attached but written in the email), can I also find a way to extract the values?

Thanks so much, greetings from Perú

Mikhail

Django · ‎06-22-2020

Hi @MikhailPeru ,

The concept in an HTML table is a bit different becaus table cells are between the tags <td> and </td>.

This concept enforces the business rule that the key and the value are between the same type of separator: ||

The concept of a table should also be possible but then you would have to add additional logic:

My concept: ||key||||value||

HTML concept: <td>key</td><td>value</td>

My recommendation: parse the html to a string and replace every <td> and </td> with || and then my concept should be applicable:

My concept: ||key||||value||

HTML concept: <td>key</td><td>value</td>

Replaced <td> and </td> within HTML string with ||: ||key||||value||

ntahal_eagleone · ‎07-28-2020

Hi @Django thanks for sharing this parsing method using objects and array.

You are using one separator, How do you include another symbol

Like value is after = symbol and ends before .

My sample text below. I need to separate 8 keys and values

<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta content="text/html; charset=utf-8">
</head>
<body>
Node Name=9981, Angeles (TCID: **-ALZ-****) 
Node Type=Network 
Node IP=***.17.**.*1 
Node Utilization=20 ms 
Node Tier=Tier2 
Node Site=Branch Site 
Node Status=Up 
Node Alert Date/Time=2020-07-28T18:08:05 
Node Application=Network</body></html>

Django · ‎07-30-2020

Hi @ntahal_eagleone ,

My answer will be a lot like my answer to @MikhailPeru on 22JUN.

My concept is keeping the format of the data structure simple to:

1-avoid to much complex logic in the flow so we can create one Apply to each loop that will get all values of every key

2-focus on the flexibility in the key possibilities to find the matching values

So my first recommendation is to change your datastructure so you can apply my concept.

If this is not possible, you could check to parse your HTML to string and then replace every , every = and the first <body> with || to see if my concept can be applied:

My concept: ||key||||value||

HTML concept: <body>key=value or key=value

Replaced ,= and <body> within string with ||: ||key||||value||

bizysiby · ‎07-31-2020

Django Hi,

Hope you are doing good...

I am totally new to Flows - been upskilling since a week...

> Been a "functional" Business analyst last 13 years. But due to a new project, am skilling in this technology

> Executed quite a few exercises within MS Flows

I used this flow - imported and tried to run after setting up connectors. But the flow doesnt execute...

Maybe I missed some settings/configurations...

Could you advise?

Thanks in advance

Sibs

Django · ‎08-03-2020

Hi @bizysiby,

Does the flow not run at all or does it fail?

In this Flow Template, there is the first trigger action to start on new emails received with additional filters about the subject to contain specific text:

Did you remove all additional filters and test to see if the emails received to the specified inbox are triggering the Flow?

bizysiby · ‎08-03-2020

Hi Django,

Cool thanks for this 🙂

Very silly of me to miss that out.

The flow executed,

Sibs

mxlgbr · ‎08-11-2020

Hi @Django,

thank you for sharing the content, very cool!

I find myself in the same situation as @ntahal_eagleone. With this in mind it would be great to get more of your thoughts regarding the suggestion to parse the HTML to string and then replace every , every = and the first <body> with ||. How could this be done with Automate?

Thank you!

Django · ‎08-12-2020

Hi @mxlgbr ,

The Substitute expression of Power Automate would be the way to go: https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#r...

Something like:

replace(replace(replace('<body>Node Name=9981, Angeles (TCID: **-ALZ-****)<br>Node Type=Network<br>','<body>','||'),'=','||||'),'<br>','||||')

would result in

||Node Name||||9981, Angeles (TCID: **-ALZ-****)||||Node Type||||Network||||

A big assumption here is that any other part of the rest of your email does not contain the strings being replaced themselves (nowhere else should be the string combination of <body> OR = or   or they will get replaced also) so again --> you should tackle this problem at the source and make sure that your output is consistent like:

||Node Name||||Name of the Node||
||Node Type||||Type of the Node||