Extract data from html table in email body

[ManishSolanki]

Use Case

I have found that there are many posts related to extract data from html table in the email body. This is the common scenario when organization tries to automate the process to increase personal productivity of users. So, I thought of sharing a solution to accomplish this task.

Scenario

Let's take an example of getting information from partners. The users send a template to business partners in the form of html table to respond. Partners replied on the mail with the required information. Using Power Automate cloud flow, we will extract the data or required information from the business and stores those values in the excel sheet saved in SharePoint document library.

Input Source (Email)

We will extract the values for Price/PC, Currency, MOQ & Lead-time (weeks) from the email received from business partners.

Target Excel File

The target master excel file sheet has been saved in SharePoint document library. The excel sheet contains a table which has unique column 'UniqueID'. Based on the unique column, values extracted from email body will be updated in the excel file.

Solution

There are many solutions to extract the data from email like convert html to text and parsing the string, AI builder etc. But here, I will make use of fx expressions to achieve this.

1. Start by creating a new Automated cloud flow.

2. Enter the flow name & select the trigger "When an email arrives (V3)". Press Create button to proceed.

3. Expand trigger action by right click it. When an email arrives provides various filter options to choose from and accordingly the flow will trigger. You can filter based on sender, recipient(s), subject, with or without attachment(s) etc. Here, we will filter the incoming email on subject "RQF Automated" so that the flow will be triggered on a specific email.

4. Add Compose action and write an expression to extract html table from the email body

concat('<table ',first(split(last(split(triggerOutputs()?['body/body'],'<table ')),'</table>')),'</table>')

5. Run the flow once to get to know which tags to target for extracting the data. Depends on the column's formatting, html tags may vary so it recommended to know beforehand. Analyze the output of compose action and note the tags that contains header of data values. 

<table width=\"100%\" style=\"box-sizing:border-box; border-collapse:collapse; border-spacing:0px; font-family:SegoeUI,Lato,&quot;Helvetica Neue&quot;,Helvetica,Arial,sans-serif; font-size:15px; font-weight:300; background-color:rgb(255,255,255)\"><tbody style=\"box-sizing:border-box\"><tr style=\"box-sizing:border-box; border-bottom:1px solid rgb(234,234,234)\"><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p style=\"box-sizing:border-box; margin:0px\"><strong class=\"x_ContentPasted0\" style=\"box-sizing:border-box; font-weight:bold\">Item Code</strong></p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p style=\"box-sizing:border-box; margin:0px\"><strong class=\"x_ContentPasted0\" style=\"box-sizing:border-box; font-weight:bold\">Supplier</strong></p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p style=\"box-sizing:border-box; margin:0px\"><strong class=\"x_ContentPasted0\" style=\"box-sizing:border-box; font-weight:bold\">QTY</strong></p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p style=\"box-sizing:border-box; margin:0px\"><strong class=\"x_ContentPasted0\" style=\"box-sizing:border-box; font-weight:bold\">UniqueID</strong></p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p style=\"box-sizing:border-box; margin:0px\"><strong class=\"x_ContentPasted0\" style=\"box-sizing:border-box; font-weight:bold\">Price/PC</strong></p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p style=\"box-sizing:border-box; margin:0px\"><strong class=\"x_ContentPasted0\" style=\"box-sizing:border-box; font-weight:bold\">Currency</strong></p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p style=\"box-sizing:border-box; margin:0px\"><strong class=\"x_ContentPasted0\" style=\"box-sizing:border-box; font-weight:bold\">MOQ</strong></p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p style=\"box-sizing:border-box; margin:0px\"><strong class=\"x_ContentPasted0\" style=\"box-sizing:border-box; font-weight:bold\">Lead-time (weeks )</strong></p></td></tr><tr style=\"box-sizing:border-box; border-bottom:1px solid rgb(234,234,234)\"><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">ABCDEF</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">XYZ</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">1000</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">ABCDEFXYZ</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">1</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">USD</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">10</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">2</p></td></tr><tr style=\"box-sizing:border-box; border-bottom:1px solid rgb(234,234,234)\"><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">EFGH</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">XYZ</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">2000</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">EFGHXYZ</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">2</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">USD</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">20</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">4</p></td></tr><tr style=\"box-sizing:border-box; border-bottom:1px solid rgb(234,234,234)\"><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">HIGL</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">XYZ</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">1000</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">HIGLXYZ</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">3</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">USD</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">20</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">6</p></td></tr><tr style=\"box-sizing:border-box; border-bottom:1px solid rgb(234,234,234)\"><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">MNOP</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">XYZ</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">2000</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">MNOPXYZ</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">4</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">USD</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">40</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">8</p></td></tr><tr style=\"box-sizing:border-box; border-bottom:1px solid rgb(234,234,234)\"><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">QRST</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">ZMR</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">5000</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">QRSTZMR</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">5</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">USD</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">50</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">10</p></td></tr><tr style=\"box-sizing:border-box\"><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">UVWZ</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">ZMR</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">5000</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">UVWZZMR</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">6</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">USD</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">60</p></td><td style=\"box-sizing:border-box; min-width:40px; line-height:initial\"><p class=\"x_ContentPasted0\" style=\"box-sizing:border-box; margin:0px\">15</p></td></tr></tbody></table>

Here, we will target the rows with data columns and find the route or path (table > tbody > tr > td > p). Using this path in XPATH function, we will retrieve the data values.

6. Add another compose action, to get the values from html table using expression. In the expression, we will convert the string to xml & then apply XPATH to get values. We also convert into rows and each rows contains corresponding all column values using chunk function.

chunk(xpath(xml(outputs('Compose')),'//table//tbody//tr//td//p//text()'),8)

Pls note that as we have total 8 columns in the html table so we will use 8 in chunk function. The chunk function creates element for each row (including row) and each row contains an array of 8 elements (columns)

7. Next, add "Apply to each" action to iterate each row. As output array of compose 2 also contains header row, so we will use expression to start iterating from second element & skips the header row:

skip(outputs('Compose_2'),1)

Add "Update a row" action inside apply to each block, to update the row in excel based on the unique column.

Expression used for each column:

Column Name	Expression
Key Value	item()?[3]
Price/PC	item()?[4]
Currency	item()?[5]
MOQ	item()?[6]
Lead-time (weeks )	item()?[7]

As index starts from zero (0), so to get the value of first column (Item Code) you need to use item()?[0]. You could get the value of each row in the iteration using the indexer.

Output

When an email arrives with subject "RFQ Automated", the flow will trigger. It extracts the values from table present in the email body and update the data in the excel file

Conclusion

So, we can now say that using fx expression we can automate the process by extracting the data from the table in the email body. This is the power of expressions in power platform. Using same concept, we could extract the data from any source as we could easily transforms JSON object to XML using expression and further apply filter using XAPTH as per the business requirement.

[trice602]

Very nice!  I am looking forward to walking through this in detail soon! Tom

[AlexEncodian]

Great solution @ManishSolanki 

FYI - Encodian has a "Parse HTML Table" action for those that just want to do this with a mouse click. It extracts HTML table data into JSON. You can also pass through an entire HTML file (e.g. website extract) and it will identify the tables.

[DarshanGanesh]

Hello @ManishSolanki 

Complete flow was really very good, but still i could not understand how you wrote expressions such as item()?['']... Bytheway on what basis those numbers are nested in item expressions.!

[ManishSolanki]

Hi @DarshanGanesh 

First of all, thanks for your appreciation.

The idea is to store all the values of the columns (from left to right) for every data row in a simple array. In the example, there are 6 rows & 8 columns, so first create a simple array of 6x8=48 elements. Then within array, I have applied chunk() function to create nested arrays of each 8 elements. The value 8 has been decided based on the total columns of the table. So, when we iterate the outer or main array, we basically iterate each row & each row or element further contains nested array of 8 elements (columns). To get the first column value within the iteration, I had to use expression item()?[0] and so on.

I hope you have got some context on how I had added expressions to get the columns value. Rather, I would suggest you create a flow using the above steps & inspect the value of all actions after the flow run.

Pls let me know if you still have any doubts, I am happy to share more details on it 🙂

Thanks

[DarshanGanesh]

Hi @ManishSolanki ,

I tried your expression method,
There i had an error message popped: The 'inputs.parameters' of workflow operation 'Update_a_row' of type 'OpenApiConnection' is not valid. Error details: The resolved string values for the following parameters are invalid, they may not be null or empty: 'id'

Any idea on above error.

Thanks.

[ManishSolanki]

It looks like, the flow updates a row with blank or empty Id. Please share the screenshot(s) of the flow along with input excel file & email body. You could directly message me. I would help you in fixing the issue.

Thanks

[paflo]

Hi,

I am going around in circles. I have used get email for the body of an email that contains an HTML table, but no matter what expression I use, I get an "Unable to process template language " error. It is either that the parameter expects a string and is getting a Null or some alternate of that. Any ideas guys?

[ManishSolanki]

Hi @paflo 

It looks like, flow could not get the body text of the email. Pls get the body of get email action from the dynamic content as the first parameter of split function in compose action.

Thanks

[paflo]

@ManishSolanki if you mean like the example in the image, I am still getting the same error.

The output from get email step looks fine to me.

[ManishSolanki]

Hi @paflo 

Thanks for sharing the screenshot.

As you have used 'Get emails' action to get the body of the email so please use items('Apply_to_each')?['body'] in the split function. The same expression will be used wherever email body html text is being referred:

Also, in compose action you don't need to append the body parameter with split function. It should only have an expression with split function as shown in the above screenshot.

Thanks

[paflo]

Hey,

Thanks foe the support, I am new enough to power automate and ChatGpt can only help so much 🙂

I managed to get the output I am looking for eventually using your tips, but I can get it to write the date to Excel. Any idea of the bext method to now write the output into an excel?

[ManishSolanki]

Hi @paflo 

I am glad that you are able to get the values from email body. To save those values in an excel file, pls follow the below steps:

1. Create a table in the excel sheet and save it on either onedrive or SharePoint site. In this example, I have taken 3 columns in the table 'Table2' & stores the excel in SharePoint site:

2. Next, add "Apply to each" action just after the 'Compose 2' action & pass the output of compose 2 in the "From" parameter:

3. Inside "Apply to each" block, add "Add a row into a table" action for adding rows in the excel sheet. You need to write an expression to set the value of each column as shown below:

Expression used for:

Col1 - item()?[0]
Col2 - item()?[1]
Col3 - item()?[2]
...
Coln - item()?[n-1]

Thanks

[Ashokkumar450]

@ManishSolanki 

I am trying create an item in SharePoint list for each row, created the workflow but still i see an error.

Thanks in advance!

[ManishSolanki]

Hi @Ashokkumar450 

In the blog, flow is extracting all the data from the html table using XML & XPATH approach. The approach you are using is different which involves parsing string, so this is the reason for getting error in expression. Pls use the same approach as mentioned in the blog.

Thanks

[Ashokkumar450]

@ManishSolanki 

Below is the error when i tried with same approach.

[ManishSolanki]

Hi @Ashokkumar450 

It seems like email body is returning as blank or empty from the trigger. Pls check if incoming email body contains the html table to extract values.

Thanks

[Ashokkumar450]

@ManishSolanki 

Below is the email body and as said i have removed the converter connection.

[Ashokkumar450]

@ManishSolanki 

Below is the email body as they are excel rows and columns i have used converter connection initially and removed later.

[PatriciaHogen1]

Hi @ManishSolanki,

It works perfectly, thank you for the clear explaination!

I am facing another scenario as sometimes we receive a table like this:

In Sharepoint I need to search for A en combine all 3 values into 1 string and update Sharepoint.

Would it be possible to add this scenario in the flow you explained? If yes, do you also know how to accomplish this? Thanks!

[ManishSolanki]

Hi @PatriciaHogen1 

Pls give me some time to figure out the solution. I will get back to you by Sunday.

Thanks

[ManishSolanki]

Hi @PatriciaHogen1 

Here is the solution you are looking for. 

In the below example, I have taken a table with 2 columns in the email from which the flow will extract the values:

Cloud Flow:

1. Firstly, set up the trigger "When an email arrives", add filter on subject, sender etc. as per your need. Here, I have applied filter on the subject "email with multiline column", pls modify it as per your need. When we applied filter on subject the flow will execute only for the emails that satisfies the condition:

2. Next, add compose action to extract the html table from the email body. We are using expression to perform this task. Expression needs to be entered in the expression window as shown below:

replace(concat('<table ',first(split(last(split(triggerOutputs()?['body/body'],'<table ')),'</table>')),'</table>'),'<br>',decodeUriComponent('%0A'))

3. Add "Select" action to create an array of objects, where each element of the array represents a row. The object contains the properties or column values from the html table. The idea is to iterate each row & extract column values from each row using expressions:

Expression used for "From" parameter:

range(2,sub(int(xpath(xml(outputs('Compose')),'count(//table//tbody//tr)')),1))

Expression used for Map "key" & "value":

key	value
join(xpath(xml(outputs('Compose')),'//table//tbody//tr[1]//td[1]//div//text()'),' ')	join(xpath(xml(outputs('Compose')),concat('//table//tbody//tr[',item(),']//td[1]//div//text()')),' ')
join(xpath(xml(outputs('Compose')),'//table//tbody//tr[1]//td[2]//div//text()'),' ')	join(xpath(xml(outputs('Compose')),concat('//table//tbody//tr[',item(),']//td[2]//div//text()')),' ')

You could iterate the result of select action to create a list item in SharePoint.

Output of select action:

 I hope this will help you in building the required flow.

Please let me know if you face any problems in creating list item in SharePoint or extracting column values.

Thanks

[PatriciaHogen1]

Hi @ManishSolanki , thanks a lot for your help! Much appreciated!

The compose is adjusted as per your details. The same for the select action, but when I re-use your formula's, I get this error for the select action:

InvalidTemplate. The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{ "@{join(xpath(xml(outputs('Compose')),'//table//tbody//tr[1]//td[1]//div//text()'),' ')}": "@ join(xpath(xml(outputs('Compose')),concat('//table//tbody//tr[',item(),']//td[1]//div//text()')),' ')", "@{join(xpath(xml(outputs('Compose')),'//table//tbody//tr[1]//td[2]//div//text()'),' ')}": "@ join(xpath(xml(outputs('Compose')),concat('//table//tbody//tr[',item(),']//td[2]//div//text()')),' ')" }' failed: 'Unable to evaluate template language expression '@{join(xpath(xml(outputs('Compose')),'//table//tbody//tr[1]//td[2]//div//text()'),' ')}' as JSON property name: the property with name '' already exists.'.

What did I do wrongly here?

[ManishSolanki]

Hi @PatriciaHogen1 

I would request you to share the screenshot(s) of the actions of the flow in edit mode.

Thanks

[PatriciaHogen1]

Hi @ManishSolanki ,

Here you go:

Key 1: 

Value1:

Key2:

Value2:

[ManishSolanki]

Hi @PatriciaHogen1 

It seems the table returned by compose action have data nested inside different html tags i.e. table->tbody->tr->td->p->span. The expression which I have shared are used for table having data inside table->tbody->tr->td->div. We need to alter the query of xpath function to extact the data from this different structure:

key	value
join(xpath(xml(outputs('Compose')),'//table//tbody//tr[1]//td[1]//p//span//text()'),' ')	join(xpath(xml(outputs('Compose')), concat('//table//tbody//tr[', item(), ']//td[1]//p//span//text()')), ' ')
join(xpath(xml(outputs('Compose')),'//table//tbody//tr[1]//td[2]//p//span//text()'),' ')	join(xpath(xml(outputs('Compose')), concat('//table//tbody//tr[', item(), ']//td[2]//p//span//text()')), ' ')

I hope that altering expression would resolve the issue.

Thanks

[PatriciaHogen1]

Brilliant, thanks a lot for your help @ManishSolanki !!!

[Tester08]

Hi @ManishSolanki 

I tried your expression method
There i had an error message popped: The 'inputs.parameters' of workflow operation 'Update_a_row' of type 'OpenApiConnection' is not valid. Error details: The resolved string values for the following parameters are invalid, they may not be null or empty: 'id'

 

[Tester08]

 

[ManishSolanki]

Hi @Tester08 

Pls check the output of compose 2 action, it should have data rows. If possible, pls direct message me the output of compose action.

Thanks 

[Tester08]

Here is the screen shot of the same 

 

[ManishSolanki]

Hi @Tester08 

Thanks. Could you pls share the screenshot for update excel action in edit mode?

Thanks

[Tester08]

This is how it looks 

 

[ManishSolanki]

Hi @Tester08 

Pls check if any 'employee id' value is blank or empty string in the output array of compose 2 action. It seems that for one or more iteration, the value for key column 'employee id' is returning as empty string. If flow did not update any row, then check the first element of compose 2 action else check the failed iteration.

Thanks

[Tester08]

@ManishSolanki table has only one row in the email body and it looks something like this I'm not sure wat is wrong 

And my excel in one drive looks like it 

 

 

[ManishSolanki]

Hi @Tester08 

The "Update a row" action works for updating the values of column(s) in existing records. As I could not see any records, so the flow failed.

If you are looking for adding rows in a excel sheet, then I would suggest using "Add a row into a table" action:

Use the following expressions for each column:

Employee id - item()[0]

Start date - item()[1]

End date - item()[2]

Pls modify the flow & retest it.

Thanks

[Tester08]

@ManishSolanki tried the above approach but table values are not getting captured in Excel, please see the output of excel

 

[ajay_baby]

Hi @ManishSolanki 

I tried your expression, but I miss out in compose 2. I am not getting any outputs. Herewith attached the screenshots. pl add your valuable recommendations.

Email body:

[pkn3081_rbm1]

@ManishSolanki 

Hi - Super love your flow solution here and explanation style!   Thank you for your inspiration here! ❤️  I am pretty new to this stuff, and am looking for help troubleshooting Step 5.   In return for anyone's help, I have a suggestion for modifying this flow to work with adding new rows.   Thanks in advance.

PRIMARY ISSUE:  I am having issues with step 5 and a <b></b> break line. 

SECONDARY ISSUE:  Also need to skip over the header row and I can't figure that out either.

ERROR THROWN AT COMPOSE2: chunk(xpath(xml(outputs('Compose')),'//table//tbody//tr//td//p//text()'),7)
ERROR MESSAGE: Unable to process template language expressions in action 'Compose_2' inputs at line '0' and column '0': 'The template language function 'xml' parameter is not valid. The provided value cannot be converted to XML: 'The 'br' start tag on line 1 position 1015 does not match the end tag of 'p'. Line 1, position 1020.'. Please see https://aka.ms/logicexpressions#xml for usage details.'.

TROUBLESHOOTING...  I went back to prior step and caught the output of the COMPOSE 1 step.  I removed the sensitive data and here is the html table structure.  But I don't understand how to modify the chunk expression to work with this table.  
 
<table class="MsoNormalTable" border="1" cellpadding="0"><tbody><tr>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal" align="center" style="text-align:center"><b>_HEADER_COMLUMN_1</b></p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal" align="center" style="text-align:center"><b>_HEADER_COMLUMN_2</b></p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal" align="center" style="text-align:center"><b>_HEADER_COMLUMN_3</b></p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal" align="center" style="text-align:center"><b>_HEADER_COMLUMN_4</b></p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal" align="center" style="text-align:center"><b>_HEADER_COMLUMN_5</b></p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal" align="center" style="text-align:center"><b>_HEADER_COMLUMN_6</b></p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal" align="center" style="text-align:center"><b>_HEADER_COMLUMN_7</b></p>
<p class="MsoNormal"><br><br></p></td></tr>
<tr><td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal">_R1C1_</p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal">_R1C2_</p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal">_R1C3_</p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal">_R1C4_</p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal">_R1C5_</p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal">_R1C6_</p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal">_R1C7_</p></td></tr>
<tr><td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal">_R1C1_</p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal">_R2C2_</p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal">_R2C3_</p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal">_R2C4_</p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal">_R2C5_</td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal">_R2C6_</p></td>
<td style="padding:.75pt .75pt .75pt .75pt"><p class="MsoNormal">_R2C7_</p></td></tr></tbody></table>

IN RETURN FOR HELP1:  A modification I am using is to switch step 7 from "Update a row" Excel action to "Add a row" Excel action, then use the expression GUID() to assign a unique id to a column called KEY or GUID, and repeat for each row which I can use later for "Update a row" Excel action.
GUID INSPIRATION SOURCE: (How to Generate a GUID in Power Automate ). 
IN RETURN FOR HELP2:  Underrated Power Automate feature... Each flow step has a notes field.   As a best practice, I annotate steps like citations in the notes field with links to my inspiration sources, along with my initials and a time date so I have breadcrumbs on how I "Frankensteined" new solutions together.  Then when I use them for coaching my coworkers or I share them, they have notes therein.

[ManishSolanki]

Hi @pkn3081_rbm1 

Pls use the below expression in compose action to handle <br> tags:

replace(replace(replace(concat('<table ',first(split(last(split(triggerOutputs()?['body/body'],'<table ')),'</table>')),'</table>'),'<br>',''),'&nbsp',''),decodeUriComponent('%0A'),'')

Regarding adding row in excel, I will publish in another blog. But for a time being, you could use "Add rows in a table" action to do the same. For key column, I would suggest choosing the columns from the html table in email body. But you could also set it as GUID field by using guid() as an expression. You could follow the steps shared to @paflo for creating an excel file in one of the replies.

Thanks

[ChukwudiUmez28]

Hi Manish @ManishSolanki ,

Thank you for providing this example on how to build a workflow that extracts html table from the body of an email. Thanks once again for helping me to build my workflow and providing a suitable solution to populate the table in Excel. You are amazing 🙂 

Best Regards,

Chukwudi.

[nathan-sobel]

@ManishSolanki 

I am new to power automate but your instructions are very helpful. I have it working so far accept the email we recieve has two tables but the compose is grabing from the second and we need to extract only the first table in the email. How can this be done. 

[ManishSolanki]

Hi @nathan-sobel 

Awesome, you find it useful 👍

Pls direct message me the html code, I will give a try. You could share with sample data if it sensitive.

Thanks

[LoadUpAndRide]

@ManishSolanki 

I have an email that comes in that i have triggered. However your concat command is expecting a string and it gives me an error. I pasted what my email looks like below to help give some context. 

concat('<table ',first(split(last(split(triggerOutputs()?['body/body'],'<table ')),'</table>')),'</table>')

 Here is what I am trying to do

1) My Headers are always the same. 13 columns. SR# and SNOW# also contain a #. That may cause an issue. 

2) My Rows are dynamic. They depend on the number of tickets in the email

3) There are two tables in the email. I only want to use the first table. 

4) There may be highlighted info on one of the rows depending on the if the Location is a top 500 location so formatting may mess with finding rows and columns. Maybe not

This is the error i get: 

InvalidTemplate

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

Open Severity 1 SRs

The 3 open tickets are sorted by oldest Due Date. Highlighted rows indicate a top 500 location (BK or FS). All resolved severity one ticket information is scheduled to be sent at 10:30pm eastern time daily.

Location

SR#

SNOW#

Due

ETA

Summary

Sub-Status

Technician

Problem_Description

Zone

Team

BK_Rank

FS_Rank

ABC11111

1-11111111

INC11111111

01/23/2024 17:41:55

01/23/2024 14:00:00

Ipsum faucibus vitae aliquet nec ullamcorper sit.

On Site

Hogan, Hulk

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ipsum faucibus vitae aliquet nec ullamcorper sit. Vestibulum lectus mauris ultrices eros in cursus turpis massa tincidunt. Sed augue lacus viverra vitae congue eu consequat ac felis. In nibh mauris cursus mattis molestie a iaculis at erat.

3

4

9036

9721

ABC22222

1-2222222222

INC222222222

01/23/2024 17:59:56

01/23/2024 15:00:00

Blandit turpis cursus in hac habitasse platea dictumst quisque.

On Site

Bond, James

Blandit turpis cursus in hac habitasse platea dictumst quisque. Id ornare arcu odio ut. Mollis aliquam ut porttitor leo a diam. Neque ornare aenean euismod elementum nisi quis eleifend quam. Quam lacus suspendisse faucibus interdum. Ullamcorper velit sed ullamcorper morbi tincidunt ornare.

1

5

93

1578

ABC33333

1-333333333

INC3333333

01/23/2024 19:07:48

Consectetur adipiscing elit pellentesque habitant.

En Route

Mouse, Mickey

Store Router Consectetur adipiscing elit pellentesque habitant. Purus faucibus ornare suspendisse sed. Ut tellus elementum sagittis vitae et. Habitant morbi tristique senectus et netus. Duis ut diam quam nulla. Cras pulvinar mattis nunc sed blandit libero volutpat sed cras.

6

5

7903

8075

Location Details for Severity 1 SRs

The location details below are sorted by Location Name. Highlighted rows a indicate top 500 location (BK or FS).

Location_(w/Zeplin)	Address(w/Map)	Telephone	BK_Rank	FS_Rank	FTS_Contacts	BK_Contacts	FS_Contacts	Related_Site_Info
ABC11111	123 AnyWhere St Any Town, TX 12345	(555) 555-1212	93	1578	PermTech: Name Lead: Name Sup Mgr: Name Lead Manager: Name	text  email@email.com text email@email.com text email@email.com	text  email@email.com text email@email.com text email@email.com	24 Hrs: # of Racks: Location(s):
ABC22222	123 AnyWhere St Any Town, TX 12345	(555) 555-1212	7903	8075	PermTech: Name Lead: Name Sup Mgr: Name Lead Manager: Name	text  email@email.com text email@email.com text email@email.com	text  email@email.com text email@email.com text email@email.com	24 Hrs: # of Racks: Location(s):
ABC33333	123 AnyWhere St Any Town, TX 12345	(555) 555-1212	9036	9721	PermTech: Name Lead: Name Sup Mgr: Name Lead Manager: Name	text  email@email.com text email@email.com text email@email.com		24 Hrs: N # of Racks: Location(s):

This message is automatically generated on an hourly basis from 6am - 10pm  eastern time. If you have problems or questions, please email the Group

[ManishSolanki]

Hi @LoadUpAndRide 

Pls check if "Split on" switch is toggled on and output array is selected from the drop down in trigger:

Navigate to (...)->Settings:

[sven7904]

Hi @ManishSolanki,

I have a similar case, only with two table sections in the email.

When i run this WF like this, I only get the bottom table data. How do I get to the top/first?

Thanks

[ManishSolanki]

Hi @sven7904 

You need to create an expression that picks the first table. In this blog, I demonstrated steps for a single html table. You can share me the email html (with sample date) either here or via direct message, I will try creating an expression using that html table and will share the expression with you.

Thank you!