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

Parse data from a table in email

We receive html emails from an external source and within that is a table, the left side of the table contains the subject and the right is the content. What I require is for it to look up the left side column and output the content to the right of this, the subjects in the left hand side never change, only the content in the right side of the table changes.

 

Screenshot 2020-03-26 at 15.52.15.png

 

Following a tutorial (https://365basics.com/microsoft-flow-parse-email-and-extract-information/), I have managed to get the HTML into basic text and from there extract the subject in the left, but not the content on the right.

 

Screenshot 2020-03-26 at 16.02.05.png

 

For example, I want it to extract the data in the first line of the table as '24/07/2020' the second line 'Lease' and the third line to output the name 'Joe Bloggs'

 

Is there a way to use the filter to find the subject and then extract the content data on the following line (or right side of the table)?

 

I don't want to use parserr or any other third-party website to do this as it would require a cost, if it can be done for free then that's always welcomed!

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-litu-msft
Community Support
Community Support

Hi @ryan1087,

 

So, you want to convert the array into JSON for the next process, right?

Maybe you could refer to the following steps:

 

You could store this info into an array, then use the index of the array to structure a new object:

Annotation 2020-03-27 143709.png

The run result, hope it can help you:

Annotation 2020-03-27 143509.png

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi!

Let's assume your nr of rows is 6. THis means you need to extract 12 elements.

So, my suggestion is

1.- Store the 'Filter array' output in one variable called 'myEmailArray', type array.

2.- Initialize a variable, name 'myTableArray', type array, value

 

 

[]

 

 

3.- Initialize a variable, name 'j', type integer, value

 

0

 

4.-Add an Apply to each, assign as its input varriable 'myEmailArray'. Inside it add a Condition action block, with one single rule:

On the left side of the condition rule, assign the following expression:

 

 

item()

 

 

On the right side of the condition rule, assign the following text:

 

 

Proposed Change Date

 

 

Operator: is equal to

Keep the false branch empty.

Now, on the true branch, add a 'Set variable' action block, value 'myTableArray', assign the following value:

 

 

take(skip(variables('myEmailArray'),variables('j')),12)

 

 

...where 12 is the nr of elements you need to extract since I am assuming 6 rows. So if you expect 10 rows, you need to use 20 instead.

Now, still inside the Apply to Each, but out of the Condition, add a 'Increment variable' action block, name j, value

 

1

 

 

Hope this helps

 

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

19 REPLIES 19
efialttes
Super User III
Super User III

Hi!

Hot topic: how to parse data from a table in a email. Three questions received today in the forum 😶

 

So, Did you considered AI builder? If for some reason you discarded such option, the approach is to try to extract the HTML table with hTML tags, convert it into xml by means of xml()... and use xpath() to grab the info.

This second approach is a pain in the ass and there is no way to guarantee success. Having said that, it you still want to explore it, you needto get your email body in HTML format and start to anayze it.... in order to get the right patterns.

Hope this helps

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



v-litu-msft
Community Support
Community Support

Hi @ryan1087,

 

So, you want to convert the array into JSON for the next process, right?

Maybe you could refer to the following steps:

 

You could store this info into an array, then use the index of the array to structure a new object:

Annotation 2020-03-27 143709.png

The run result, hope it can help you:

Annotation 2020-03-27 143509.png

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Hi @v-litu-msft,

 

Thanks a lot for replying, it's much appreciated.

 

Apologies in advance, I am new to Power Automate but in a sense, yes that's what I want. I need it to either filter/search the keywords in the table example and then extract the contents to the right of each column, or to extract the table itself, from there I can use your proposed method and collect the data required from the array by referencing each line from the table. With each email, the leading introduction outside the table changes but the the number of lines in the table never changes, just the content in the right.

 

An example of the table is below, I have blocked out the content for data protection reasons.

 

Screenshot 2020-03-27 at 18.31.59.png

 

Below is my current flow, the 'Get DATE line' are filters, these are working as you can see in final compose but it's only filtering the subject of the table, these never change, what I require is the content to the right of it. If I could either extract the table, I can then export every other line, or if it will search the words in the line and then extract the data corresponding to that line.

 

my_flow.jpg

 

Hopefully this explains it, if there's a way to extract the whole table in one segment, then reference it in another, I can then extract every other line from there using your proposed method - I think this would be the easiest way. If you have an alternative then please let me know.

Thanks for the reply but unfortunately the companies office 365 plan doesn't include this, additionally once the data in the table is extracted, it will placed into an online excel spreadsheet so I'm not sure if AI Builder is the best platform for this?

efialttes
Super User III
Super User III

Hi!

AS far as I understood the methods proposed yo far in this thread are not valid, right?

If so, one more question: what did you achieved to do so far?

So I see in your screenshot an action block called 'Get TENANT line' whose input looks like:

 

"Proposed Change Date",
"24/07/2020",
"Time of agreement",
"Lease",
"Ingoing Tenant Name",
"Joe Bloggs"

 

Is this an array you already managed to extract from the email? If so, I think there is a reasonable way to extract the info you need from such array structure by transforming it into an object so you can apply "Parse JSON" and finally assign it properties values into a Sharepoint list item

{
  "Proposed Change Date": "24/07/2020",
  "Time of agreement": "Lease",
  "Ingoing Tenant Name": "Joe Bloggs"
}

Please let us know if you still need help, if you managed to design your flow to build such array strure, and if my proposal is fine for you

Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Hi @efialttes,

 

Thanks again for your help, it's much appreciated.

 

What I have so far

 

Screenshot 2020-03-28 at 16.50.44.png

 

The proposed methods haven't quite worked yet because the content in the right column of the table in the email is dynamic and always changes, the content in the left column of the table never changes therefore I can't reference anything to the right because it changes each and every time.

 

What I have done essentially so far is retrieve the sent email, convert from HTML to Text, compress and simplify the text so the content of the table is on its own single line.

 

Screenshot 2020-03-28 at 17.08.49.png

 

What you're suggesting it correct, the input to the 'Get TENANT line' comes form the the output of the 'filter array', the 'Get TENANT line' is just filtering it down. What I'm struggling with is how to get the 'filter array' output into an Object which I can then Parse JSON to get the strings for the different categories in the table. If you can please advise to this then I should hopefully be able to do it.

Hi again!

I think you completed already the hardest part!

Now, let's take the following example:

 

 

[
"Proposed Change Date",
"24/07/2020",
"Time of agreement",
"Lease",
"Ingoing Tenant Name",
"Joe Bloggs"
]

 

 

Probably you have more elements in your array, but assming the nr is even, my proposal should work no matter nr.

 

I would suggest to add a 'Initialize variable' action block, name 'myInputArray', type Array, assign as its value the array you got whose structure is similar o the example above.

We also need to add a 'Initialize variable'  action block, name 'myOutputObject', type Object, assign as its value:

 

{}

 

 

We also need to add a 'Initialize variable' name 'i', type integer, value

 

1

 

 

Now we need to add an to Apply to each, assign as its input variable 'myInputArray'. Inside the 'Apply to each' add a Condition action block with just one rule to evaluate:

On the left side of the rule add the following expression:

 

mod(variables('i'),2)

 

 

On the right side add:

 

1

 

 

Operator:

 

is equal to

 

 

Now leave empty the false branch

ON the true branch, add a Compose action block, let's call it 'Compose myAuxObject', assign as its value variable 'myOutputObject'

Also on the true branch, add a 'Set Variable' action block, name 'myOutputObject', assign as its value the following expression:

 

setProperty(outputs('Compose_myAuxObject'),item(),variables('myInputArray')[variables('i')])

 

 

Now out of the True branch, but still inside the Apply to each, add a 'INcrement variable' action block, name 'i', value 1

 

Finally, outside after the Apply to Each, add a dummy Compose action block, assign as its value 'myOutputObject' variable.

Reexecute the flow, inspect dummy Compose values

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Hi @efialttes,

 

Thanks a lot again for your detailed response. I have tried what you suggested and although it works, it does't work in the way intended. I think the main reason for this is because I don't currently have a block with is outputting just the table contents, I am trying to reference the 'filter array' and where this is a compressed version of the whole email, it's struggling to determine what content to output correctly.

 

I can PM you the contents of the 'filter array' output or send an export of the flow I have currently if you would rather do it that way?

 

Thanks again for your help on this, it's greatly appreciated!

Hi!

" I don't currently have a block with is outputting just the table contents"

After your last post, I've spent some minutes reading the whole thread...

1.- If the nr of rows in your table is always the same @v-litu-msft solution is much more simple and efficient than mine

2.- You do not have an array with Subjects and responses yet?

Sorry for the misundertanding, when I saw this screnshot I thought you already got it

Flow_ryan.png

...but seems you almost got it! So, please confirm the following assumptions:

1.- Does your table always starts with subject 'Proposed Change Date'? If so, I think there is an easy way to remove the array elements before the first table row.

2.- Does your table ends always with a specific subject? If so, what is it?

 

BTW, wich expression did you use to get all this stuff splited?

On you screenshots I only can see

split(trim(uriComponentToString(replace(uriCo

Thanx!

 

 
 


Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Hi @efialttes,

 

No problem at all, I appreciate the help.

 

1. The number of rows in the table is always the same.

2. I don't have an array with just the subjects and responses yet, no.

 

Apologies, that screenshot was just a summary of the 'filter array', that array contains the whole email body but in a condensed version. 

 

1. Yes, it always starts with the 'Proposed Change Date' subject.

2. It always ends with the 'General Manager' subject, see the table screenshot in reply #4 if you need it for reference.

 

The whole expression used to split is:

split(trim(uriComponentToString(replace(uriComponent(body('Html_to_text')),'%0A','~~'))),'~~')

 

Thanks!

Hi!

Let's assume your nr of rows is 6. THis means you need to extract 12 elements.

So, my suggestion is

1.- Store the 'Filter array' output in one variable called 'myEmailArray', type array.

2.- Initialize a variable, name 'myTableArray', type array, value

 

 

[]

 

 

3.- Initialize a variable, name 'j', type integer, value

 

0

 

4.-Add an Apply to each, assign as its input varriable 'myEmailArray'. Inside it add a Condition action block, with one single rule:

On the left side of the condition rule, assign the following expression:

 

 

item()

 

 

On the right side of the condition rule, assign the following text:

 

 

Proposed Change Date

 

 

Operator: is equal to

Keep the false branch empty.

Now, on the true branch, add a 'Set variable' action block, value 'myTableArray', assign the following value:

 

 

take(skip(variables('myEmailArray'),variables('j')),12)

 

 

...where 12 is the nr of elements you need to extract since I am assuming 6 rows. So if you expect 10 rows, you need to use 20 instead.

Now, still inside the Apply to Each, but out of the Condition, add a 'Increment variable' action block, name j, value

 

1

 

 

Hope this helps

 

 

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



View solution in original post

Hi, thanks again for your reply, I'll try it tomorrow. Just quickly, it appears you possibly forgot to enter a value for step 2, what is this?

Already fixed. Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Hi @efialttes,

 

I followed your instructions and managed to get it work finally, using a dummy Compose module it output the whole table contents only as expected. From there I converted every other line into their string blocks which I then used to populate an excel spreadsheet.

 

Thanks a lot for your help on this, it really helped!

@ryan1087 

Thanx for your kindness, and also to take some time to mark this topic as solved! THis way other members of the community facing similar challenges can find faster a valid approach.

I've also marked @v-litu-msft as Solution since in many scenarios as yours it's a more efficient approach than mine.

You guys make this community great!

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Can you share the file Flow? Im looking for something similar

Sure thing, PM me and I'll send you the ZIP export.

Sent. Thanks
pats30
Regular Visitor

Hi @ryan1087 Can you share the file flow? 

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power Automate User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (76,535)