Extracting text from tables from body of email and add new row into Excel Spreadsheet

[Anonymous]

Hi,

As title says, I am trying to extract data from two tables sent in the body of an email and add as a row to excel spreadsheet.

I have currently used the steps

1) When a new email arrives

2) HTML to text (body)

3) Add a row to table (Excel)

When I receive the emails, the tables come in the following format - a table on top and a table at the bottom.

Report from 2020-06-15 00:00:00 to 2020-06-21 23:59:59

User Id	Login
1	login email

Tasks

Item Id	Project Id
1	10
2	11
3	12

I would like to insert a row into a spreadsheet each time I receive an email like this for each row in the 'Tasks' table but also append the user id and login data at the top table to each row in the bottom table.

Is it possible for me to do this? Thank you!!

[abm]

Hi @Anonymous 

This is going to be quite tricky to get the data especially you have two tables. You may need to use lots of split expression to get each rows delimited by carriage return. Then again how you going to get item id and project id? That's another challenge.  If you could get the data as comma separated that will be much easier to extract the data (Item id, project id, etc)

Thaks

[Anonymous]

Thank you! So you are saying it isn't possible to get the data below each column header in each table without a delimiter? Unfortunately, we can't get the data as comma separated, as these are automatically generated emails from within the organization.

[abm]

Hi @Anonymous 

Thanks for your quick reply.

To get the data especially from a plain text you need some sort of rules or relations to find the data. Here all you have is a table. As I mentioned earlier you may need to use split expression to evaluate using the carriage return for each rows or you could try <tr> or <td> tags for using as a delimiter. You may end up doing lots of string manipulations to extract it. It may be possible but unfortunately there is is nothing out of the box from Power Automate to resolve it quickly.

Thanks 

[Anonymous]

Thank you for your quick reply! Do you have any sources I could look at? This is my first time using Microsoft Flow and am needed to automate and add these emails to a spreadsheet for internal purposes.

[abm]

Hi @Anonymous 

I could do a screen share? Please check your email?

Thanks

[abm]

Hi @Anonymous 

Have a look at some examples of how to use Split expression? There are quite a lot of threads in this community related to this. The easiest way is a screen share to explain this. Have a look at the below posts.

https://faustocapellan.com/2018/05/01/express-yourself-string-functions-in-microsoft-flow/

https://www.youtube.com/watch?v=_M7Jpwlp4Go

Thanks