cancel
Showing results for 
Search instead for 
Did you mean: 
ManishSolanki

Extract data from html table in email body

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)

ManishSolanki_2-1696763315968.png

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.

ManishSolanki_1-1696763020797.png

 

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.

ManishSolanki_3-1696763546722.png

 

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

ManishSolanki_4-1696763667160.png

 

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.

ManishSolanki_5-1696763758234.png

 

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

ManishSolanki_6-1696763874526.png

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.

ManishSolanki_7-1696764142848.png

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:

ManishSolanki_0-1696764363440.png

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.

ManishSolanki_1-1696764495287.png

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

ManishSolanki_2-1696764809010.png

 

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.

Comments

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

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.

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

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

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.

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

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?

paflo_0-1699431946928.png

 

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

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

paflo_0-1699437455250.png

paflo_1-1699437538528.png

 

 

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:

ManishSolanki_1-1699444894590.png

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

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?

paflo_0-1699528729000.png

 

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:

ManishSolanki_0-1699591741636.png

 

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

ManishSolanki_1-1699593249035.png

 

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:

ManishSolanki_2-1699593831734.png

Expression used for:

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

 

Thanks

@ManishSolanki 

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

 

Ashokkumar450_1-1700132297585.png

 

Ashokkumar450_2-1700132407390.png

 

 

Thanks in advance!

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

@ManishSolanki 

Below is the error when i tried with same approach.

Ashokkumar450_0-1700197770960.png

 

Ashokkumar450_1-1700197805682.png

 

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

@ManishSolanki 

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

Ashokkumar450_0-1700198997901.png

 

Ashokkumar450_1-1700199153734.png

 

@ManishSolanki 

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

Ashokkumar450_2-1700199572188.png

 

 

Ashokkumar450_0-1700199454256.png

 

Hi @ManishSolanki,

It works perfectly, thank you for the clear explaination!

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

PatriciaHogen1_0-1700773779236.png

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!

Hi @PatriciaHogen1 

 

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

 

Thanks

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:

ManishSolanki_0-1700918647739.png

 

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:

ManishSolanki_0-1701007817563.png

 

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:

ManishSolanki_1-1701008091795.png

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:

ManishSolanki_2-1701008272114.png

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:

 

ManishSolanki_3-1701008847586.png

 

 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

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

 

PatriciaHogen1_0-1701181111086.png

 

What did I do wrongly here?

 

 

Hi @PatriciaHogen1 

 

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

 

Thanks

Hi @ManishSolanki ,

Here you go:

PatriciaHogen1_0-1701182425177.png

PatriciaHogen1_1-1701182468514.png

Key 1: 

PatriciaHogen1_2-1701182492876.png

Value1:

PatriciaHogen1_3-1701182512278.png

Key2:

PatriciaHogen1_4-1701182565613.png

Value2:

PatriciaHogen1_5-1701182589549.png

 

 

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

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

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'

IMG_20231202_171012.jpg

 

 

 

IMG_20231202_171012.jpg

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 

 

Here is the screen shot of the same 

IMG_20231202_172959.jpg

Hi @Tester08 

 

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

 

Thanks

This is how it looks 

IMG_20231202_173534.jpg

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

@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 

IMG_20231202_175644.jpg

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:

ManishSolanki_0-1701520922835.png

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

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

IMG_20231202_183745.jpg

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.

 

 

ajay_baby_0-1701951861138.png

 

ajay_baby_0-1701952979703.png

 

 

Email body:

ajay_baby_3-1701952834423.png

 

 

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

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

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.

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

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

@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

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:

ManishSolanki_0-1706075143959.pngManishSolanki_1-1706075179951.png

 

About the Author
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: https://ganeshsanapblogs.wordpress.com/about MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/