Extract HTML Table from Email in Power Automate

Learn how to use Xpath, XML, IndexOf and Substring expressions in Power Automate in order to extract an HTML table from an incoming email, convert to a JSON array, and import into a new Excel File containing a table. I will talk you though the basics of HTML Tables, how to read the tags and understand how XPath might help you with your data extraction.

#PowerAutomate #JSON #HTML #Table #Excel

00:00 Intro
01:01 Let's get started
02:25 HTML Tables W3Schools
03:31 How to Use Xpath in Power Automate
05:35 Quick Overview of HTML to JSON Array
06:14 Explore the solution in detail
10:22 Further explanation of XPath
11:03 Select vs Apply to Each method of extracting data from HTML
18:12 Create Excel File and populate with HTML Table
20:15 A demo of the solution, HTML Table to JSON to Excel
21:23 Summary and thanks for watching

[nikkithanwin-h]

Hi Damien, as with our email discussion.

Some of you guys might face some issues with the xpath when following this tutorial. You might get this error when you run the flow that is shown in this tutorial : "Jason property name: the property with name " already exists.".

Fear not, this is an issue with xpath.

In my working solution to select 'Country', I used the following xpath. " xpath(xml(outputs('ComposeHTML')),

(Use wildcard to select text, aka use "//text()" rather than "/text()", as the formatting for the table headers might not allow you to select the text() properly.)

Do the same for the remaining column headers and anytime that these column headers are used in the tutorial. Another best practice for Microsoft flow is to create variables first, so it is easier to just change 1 variable declaration rather than finding all the points where this expression is used and changing it individually.

I hope this helps!

[angelanfish]

Thank you so much for posting this great video! Not only it's super helpful, the quality is also top notch! Truly appreciate the clear and thorough explanation of each step & sharing of such great knowledge!

[dnordon]

Wow, this was great, I really needed to work out how to do this. I'm fairly new to Power Automate, but you took me through so much that was new to me including Xpath and functions in such a short time and I actually got it to work Really appreciate it man.

[sandrasoniec]

Hi Damien, thank you so much for sharing. I am working with larger data sets and Apply-to-each causes too many API calls. Adapted to my needs and really superfast.

[scottjames]

This is brilliant. Matches my use case exactly - my company is getting spammed with emails (that contain a table of data) that we need to insert into a database. Thank you very much.

[lpapmarcial]

Great video. Thanks for your effort. Everything was nicely explained.

[pauliinaheikkila]

Thanks! This was great. I was actually trying to extract an html table from an email in Azure Logic Apps, but couldn't find anything useful. Luckily I thought to search for Power Automate instructions and this came up. A few little tweeks in xpather and I got my app working!

[mariahreymarkvig]

Great video, It pointed me in the right direction with my issue. How every I have one <td> that has style - <td width="555"><b><font color="FF0000">Critical Error</font></b></td> - When I run the flow the <td> appears as null. I have tested without the style and it works. But how do I grab the value without deleting the style?

[ingridschiele]

As always: Really great stuff. Thanks!

[mfg]

Thank you so much, Damien, this is a great video and channel. Really appreciate the effort you put into producing such comprehencive guides and taking the time to explain things!

Im a total beginner, and this is my first power automate project, im about 5 hours in now and have the flow running completely without any errors (I had to do lots of modification to the table read and select sections as the data source tables i will be dealing with have spans and other elements). Your excellent tutorial helped me actually understand what i was doing as opposed to just giving me a working final product with no understanding. Xpather was so helpful. Really glad i found your content!.

Im currently troubleshooting why the output excel files are coming up blank. I can see the correct number of rows and columns being created in the table, but all the headings, table content and sheet name arent being entered as configured. When i check the post-test screen, i can see the data from my table being listed correctly, so i think its a minor thing ive missed. Looking forward to cracking this last hurdle!

Thanks again!

[daanbundervoet]

Hi Damien, excellent tutorial. It worked perfectly with the tip in the comment section to replace '/text()' to '//text()". I'm trying to, instead of making a new excel file with the table, update a table in an existing based on the data of the table in the mail via a key in the first column. I haven't figured out if that's possible yet but this seems like a first step to achieve that.

[trideepnath]

Excellent demo you have shown... .
I am having little confusion what will be the solution, if the table has no <TH> but <TD>s inside 6th < TR> has all the header names. and from 7th <TR> has all the <TD>s with data ..

[onionman]

This didnt work but it was the last straw that broke the camels back. I had to go and learn how to use Arrays, strings, split and int (all from your vids) and have managed to make something that works despite the CSS stopping this solution working for me :)

[Kildergcowboy]

Hey, great detailed video. Unfortunately, I keep getting an error "indexof' expects its first parameter to be of type string" The output from the email seems fine for HTML table. any ideas guys?

[niiivee]

Hi Damien,

This was really helpful. Thank you for the video! I am stuck at one step where our html in the email has styling so when we try to get table data, the styling part also populated with it. In Xpather, this styling is not seen with the same code. Any guidance on how to solve this?

[MrKork]

Great Video!
Have you tested loading all stuff to the Office Script then do the job inside the script?
Office Scripts are JavaScript natively so maybe its easier to access HTML objects.

[justinwilcoxphotography]

Great video! I appreciate you putting it together! Any advice on what to do when an email contains multiple tables? I've got an email with three or so tables and only need data from one.

[vinithbattula]

Hi Damien,
I have a question. How do I set the rows range when I don't have a fixed number of rows in my table? In your previous example, you showed how it works with 7 rows, but how do I handle it when I don't have a fixed number of rows?
Please let me know if you have any suggestions or insights on this. I'd appreciate your help.

[syqnpip]

This is a great video, thank you. I do have 2 questions:
1. I am understanding the table syntax, but the header in my email body is in a column on the left side and the data I want to copy into excel file is in the column on the right side, is there a syntax or video you can share for me to learn?
2. can you confirm, since I already have a table I wouldn't need to create a excel table rather add a row and apply each field to match header in the table?
thank you again.

[Bornforbapa]

Thanks for informative video....need your support to decode the mail body table in to HTML view and some selected line need to agin send on mail via mail body by using power automate