Parse data from a table in email

[ryan1087]

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.

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.

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!

[efialttes]

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

[v-litu-msft · Accepted solution]

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:

The run result, hope it can help you:

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.

[ryan1087]

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.

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.

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.

[ryan1087]

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]

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!

[ryan1087]

Hi @efialttes,

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

What I have so far

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.

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.

[efialttes]

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

[ryan1087]

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!

[efialttes]

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

...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!

[ryan1087]

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!

[efialttes · Accepted solution]

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

[ryan1087]

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?

[efialttes]

Already fixed. Thanx!

[ryan1087]

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!

[efialttes]

@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!

[carlosdajer]

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

[ryan1087]

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

[carlosdajer]

Sent. Thanks

[pats30]

Hi @ryan1087 Can you share the file flow?