“I receive an email with data summary in a table, how can I parse the data from there using Power Automate flow?”
One of the most promoted use cases for Power Automate is email processing. You receive an email with an attachment and it’ll save the attachment to OneDrive. But often it’s not that simple. You might want to look for a specific attachment or a specific keyword in the subject, and even then just saving the attachment might be not enough. There’s also the email text which can contain relevant information. While it can be often extracted using the ‘HTML to Text’ action, it doesn’t work that well for tables.
How do you then extract data from a table?
Find the table
The first step to extract data from a table is to find the table. It doesn’t matter if it’s one or more tables, you must always find the right one, e.g. the one with ‘Table2’ in the header.
Take the whole email body, and split it by the ‘<table’ string, the opening tag of an HTML table.
split(triggerOutputs()?['body/body'],'<table')
The result won’t look pretty, but it doesn’t matter, it’ll give you each table as a separate item in an array.
Now you can search for the table based on some value. The whole table is a single item so you can use ‘Filter array’ action to search for that value (which must be unique!), e.g. ‘Table2’.
The result should be a single item with the table string.
Isolate the table
But at this moment it still contains more than just the table, it can contain all the other HTML tags before the next ‘<table’ string. To extract only the table, take the first output from ‘Filter array’. Use another split(…) expression, this time splitting by the closing table tag ‘</table>’, and take everything before that.
split(first(body('Filter_array')),'</table>')[0]
Return back the opening and closing tags that were removed with the split(…) expressions.
concat('<table', outputs('Compose_2'), '</table>')
That’ll give you a complete HTML table, convertible into XML with the xml(…) expression.
Once you have an XML, you can use xpath(…) to pick the desired value.
Summary
As you can see, it’s not complicated to parse data from an email with an HTML table using Power Automate. Using the HTML tags you can split(…) the email into pieces, extract and rebuild only the desired table, and extract the values directly with the xml(…) and xpath(…) expressions.
Thank you for this. It all works great until the last step where I get an error because the table contains image tags which are not closed, so it says the opening tag does not match the closing tag:
How might you suggest I remove all the image tags?
Many thanks
Hello Jim,
some time ago I tried to get rid of these tags but I didn’t find any good solution. I tried replacing them, e.g. replace(…, ‘
Thank you very much for the instructions!
The last step gives me the error
“InvalidTemplate. Unable to process template language expressions in action ‘Compose_4’ inputs at line ‘0’ and column ‘0’: ‘The template language function ‘xml’ parameter is not valid. The provided value cannot be converted to XML: ”\’ is an unexpected token. The expected token is ‘”‘ or ”’. Line 1, position 14.’. Please see https://aka.ms/logicexpressions#xml for usage details.’.”
What can I do?
Thank you!
Hello Nick,
you can try to google some XML parser and put the output of ‘Compose_3’ into the parser to tell you what’s wrong with the XML, there might be some character that must be removed based on the error message.
I am new to power automation flow,
how to pick 1 table (multiple tables in compose like 1.. 10) I want to get 4th table data to extract
Hello rathnasiri,
that’s a bit complicated for a comment response, I might write a post on this topic in the future, but for now I’m sure there’ll be some videos on Youtube how to parse emails.
xml(table_html) doesn’t transform anything. you need pure html tags without class added by outlook.
Hello, everything works for me except the last step: it does not convert anything to XML.
Hello Nick,
that’s hard to tell without seeing the email as each email is different, you’ll have to try and play with it a bit.
Hello,
Great blog. Managed to get table out of the email following the steps. The output is now is in XML format. I need to add this table to an excel file. Is that possible?
Hello Nivedhana,
I have no idea, never needed to convert XML table into Excel.
Hello
Thank you for sharing. I’m stuck in “filter array” step. I can’t call the Dynamic “Item” to compare, it only show the output of compose before. can you help me
Same issue for me
Go to ‘Edit in advanced mode’ and type the following
@contains(item(), ‘[text to find]’)