Can anyone help me to write an expression to extract data from email body to excel?
Below is what is looks like in the email body, is not an attachment.
If anyone can help me will greatly appreciate.
Best,
Thank you for your reply, but you can see from the subject. I did stuck in the expression.
On this step
It's the same process for extracting the text and sending it to Excel. I have provided the connector for reading text or HTML from an email body above. If you want more help, you'll need to provide more information on what you're specifically trying to do. You're not likely to find that people will write the code for you, but if you post what you've done so far with identifying information removed, you'll get suggestions on how to improve your code.
Mine is not attachment, just in email body and look like excel table.
It works for me. I have a system to extract text from attachments and send them to Excel on OneDrive. Post images of your anonymized Power Automate flow and Office Script if you want some help troubleshooting.
It does not work, I think it should be add expression in order to extract the data out.
The best way to get the data and send it to Excel is most likely to be by using the HTML to text connector.
For example, an email with this HTML table in the body:
Would be converted by
to:
Col1
Col2
Col3
1
2
3
4
5
6
That could be sent to an Office Script using Run Script to write the data to a spreadsheet.
You can put the body of the email directly into a string, then parse the HTML, or you can use the HTML to text connector to extract the text from the body.
For example, I created an email with this HTML table in the body.
If I put the body of the email directly into a string variable, it looks like this:
<html><head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><style type="text/css" style="display:none">
<!--
p
{margin-top:0;
margin-bottom:0}
-->
</style></head><body dir="ltr"><div class="elementToProof" style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0); background-color:rgb(255,255,255)"><br></div><div lang="EN-US" style="word-wrap:break-word"><div class="x_WordSection1"><table class="x_MsoTableGrid" border="1" cellspacing="0" cellpadding="0" style="border-collapse:collapse; border:none"><tbody><tr><td width="208" valign="top" style="width:155.8pt; border:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">Col1</p></td><td width="208" valign="top" style="width:155.85pt; border:solid windowtext 1.0pt; border-left:none; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">Col2</p></td><td width="208" valign="top" style="width:155.85pt; border:solid windowtext 1.0pt; border-left:none; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">Col3</p></td></tr><tr><td width="208" valign="top" style="width:155.8pt; border:solid windowtext 1.0pt; border-top:none; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">1</p></td><td width="208" valign="top" style="width:155.85pt; border-top:none; border-left:none; border-bottom:solid windowtext 1.0pt; border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">2</p></td><td width="208" valign="top" style="width:155.85pt; border-top:none; border-left:none; border-bottom:solid windowtext 1.0pt; border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">3</p></td></tr><tr><td width="208" valign="top" style="width:155.8pt; border:solid windowtext 1.0pt; border-top:none; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">4</p></td><td width="208" valign="top" style="width:155.85pt; border-top:none; border-left:none; border-bottom:solid windowtext 1.0pt; border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">5</p></td><td width="208" valign="top" style="width:155.85pt; border-top:none; border-left:none; border-bottom:solid windowtext 1.0pt; border-right:solid windowtext 1.0pt; padding:0in 5.4pt 0in 5.4pt"><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif">6</p></td></tr></tbody></table><p class="x_MsoNormal" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif"> </p><p class="x_MsoNormal elementToProof" style="margin:0in; font-size:11pt; font-family:Calibri,sans-serif"><br></p></div></div></body></html>
If I use the use the HTML to text connector and put that into a string variable, it looks like this:
Col1
Col2
Col3
1
2
3
4
5
6
If you have a known table structure, the second one is probably better.
You can then feed the text to Excel using Run Script and have an Office Script that creates a table out of it.
Is HTML
What is the table? Is it text, HTML, an image, ...?