Extract and parse data from an email 09-10-2020 15:57 PM

[Django]

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

[s_dupuy]

--

[mstjohnsomc]

When I try to Import your pkg zip - I get the following error"

"Import flow created outside of a solution into this environment. Flows created in a solution can be imported under Solutions. "

Thoughts?

[mxlgbr]

Will try that. Thank you for the update @Django.

[Django]

Hi @mstjohnsomc, based on only your text it seems you are trying to import the Flow into a solution directly?

Check https://flow.microsoft.com/en-us/blog/import-export-bap-packages/ for how to import Flows and what limitations apply.

[iko_chen]

Hi Django

is it possible to extract the follow content of the email?

Notification	FLOC	FLOC Descr.	Short text	Long description	Work order
2200025222	RCS-C-000	RECEIVING,RAW MATERIALS, MIXING, OTHER	热熔胶房，罐区桥架内线缆检查	* 08.09.2020 01:53:54热熔胶房，罐区桥架内线缆检查	310083157
2400027317	RCS-C-000	RECEIVING,RAW MATERIALS, MIXING, OTHER	08:00-16:00 早班电气巡检		330003723

[Django]

Hi @iko_chen,

Extracting the data using my technique would not be very useful because my technique always tries to match a key with a value for every key you define. Looking at your data you have a table like structure with the key in the header of the table and a variable number of values in the cells of the table... there may be some possibilities because if your table is send in HTML it will have a consistent structure using table row <tr> and table cell <td> tags and when data is consistent, we can extract it 🤓

My advise would be to post your question in the Power Automate forum, post that link back again here and if your problem is not solved before I can have a look somewhere next week --> I will definitely try to come up with a possible solution in that post!

[iko_chen]

Hi @Django 

thank you very much.

[DVela227]

Hi @Django!

Thank you so much for your solution. I am trying to recreate it using your example and upload to extract data from Microsoft Bookings events since there is no flow connector yet. The data I am extracting from the email is the custom fields from Bookings. Unfortunately the structure of this data doesn't seem to be in a format where I can use an array. Any ideas on how I can extract the answers after "Answer-" for each custom question? 

Sample Data in Email: 

Custom Fields
----------------------
Question 1- Merchant ID
Answer- 123456
Question 2- Contact Name for Installation
Answer- Peterson
Question 3- Contact Phone Number for Installation
Answer- 800-123-4567
Question 4- Contact Email Address for Installation
Answer- testingtester@test.test
Question 5- Manager Responsible
Answer- Firstname Lastname
Question 6- Model Number
Answer- Peripheral
Question 7- Special Notes
Answer- helloWorld

Thank you in advance!

Dave

[Django]

Hi @DVela227,

It should be doable but requires a lot of substring / string logic which can be quite some work.

You can use the split() expression to split your string on every "Question"-part into multiple strings and then you can extract every Answer of every Question. 

Check out this post: https://powerusers.microsoft.com/t5/Building-Flows/extract-the-content-from-table-in-email-to-sharep... where we do the same based on the <td> part of HTML-tables. Hopefully you can use parts of my Flow Logic that I shared there 👍.

I must warn you though: this will be some more advanced flow stuff 🤓

[Birchenall]

I'm having an issue when I try to use the template, but then turn the 'When a new email arrives' into 'When a new email arrives in a shared mailbox'. It changes the first Compose-SenderEmail into an Apply to each with the Compose inside, and you can't change this.

Then when you run the test and it gets to the Apply to each KeyToFind and has the following error:

Action 'Compose_-_FullStringTextResultArray' failed

Unable to process template language expressions in action 'Compose_-_FullStringTextResultArray' inputs at line '1' and column '2883': 'The template language function 'lastIndexOf' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#lastindexof for usage details.'.

When I tried it as a normal email address it worked fine.

Do you have any suggestions for a solution?