cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mhala1234
Regular Visitor

How to parse an information from an e-mail HTML table?

Hi all,

 

I would like to ask you for a help. I'm trying to build a Flow which takes a Purchase Order number (PO) from the incoming e-mail.

Incoming e-mail is always the same.

mhala1234_0-1658204955980.png

 

Unfortunately there are no any delimiters in the e-mail and I don't know how to obtain required information.

This is the output of the e-mail body:

 

 

{"headers":{"Pragma":"no-cache","Transfer-Encoding":"chunked","Retry-After":"3600","Vary":"Accept-Encoding","Cache-Control":"no-store, no-cache","Location":"https://flow-apim-msmanaged-na-northcentralus-01.azure-apim.net/apim/office365/e3984f93-b9a5-4ee1-a63e-21a5-6486c498/v3/Mail/OnNewEmail?folderPath=Id%3a%3aAAMkADI3N2FhN2IwLWNlMDMtNDc1ZC1iZTc2LTk3ZDZkZTQ1MGQ2MAAuAAAAAAC7Dt1p9E19Qb2KoQMv7xdjAQDSDyPWvsiySL-_Uv3wKot3AAAAALsjAAA%3d&importance=Any&fetchOnlyWithAttachment=false&includeAttachments=false&subjectFilter=Tech+Data+Distribution+s.r.o.&LastPollInformation=eyJMYXN0UmVjZWl2ZWRNYWlsVGltZSI6IjIwMjItMDctMThUMTI6MDM6MDErMDA6MDAiLCJMYXN0Q3JlYXRlZE1haWxUaW1lIjoiMjAyMi0wNy0xOFQxMjowMzowMSswMDowMCIsIkxhc3RNZXNzYWdlSWQiOiJBQU1rQURJM04yRmhOMkl3TFdObE1ETXRORGMxWkMxaVpUYzJMVGszWkRaa1pUUTFNR1EyTUFCR0FBQUFBQUM3RHQxcDlFMTlRYjJLb1FNdjd4ZGpCd0RTRHlQV3ZzaXlTTC1fVXYzd0tvdDNBQUFBQUxzakFBRFNEeVBXdnNpeVNMLV9VdjN3S290M0FBWXR0UXN1QUFBPSIsIkxhc3RJbnRlcm5ldE1lc3NhZ2VJZCI6IjxBRFI0NzAwMDAwODg4NTgwMzMwMDAwMEQzQUMzQjI1MTFFRUQ4MUQyMzBEMkFEOUJGMEM0QE1JQ1JPU09GVC5DT00%2bIn0%3d","Set-Cookie":"ARRAffinity=f2f616db1fb852bebf8d33f4c2e809bd2033537707f3f091cc1bbac7ec19b816;Path=/;HttpOnly;Secure;Domain=office365-eus2.azconn-eus2-003.p.azurewebsites.net,ARRAffinitySameSite=f2f616db1fb852bebf8d33f4c2e809bd2033537707f3f091cc1bbac7ec19b816;Path=/;HttpOnly;SameSite=None;Secure;Domain=office365-eus2.azconn-eus2-003.p.azurewebsites.net","x-ms-request-id":"5e4ce288-b818-4984-99e5-730cda4a96b7;bb610027-e231-4114-acf6-bebabb72cb2e","Strict-Transport-Security":"max-age=31536000; includeSubDomains","X-Content-Type-Options":"nosniff","X-Frame-Options":"DENY","Timing-Allow-Origin":"*","x-ms-apihub-cached-response":"true","x-ms-apihub-obo":"true","Date":"Mon, 18 Jul 2022 12:03:18 GMT","Content-Type":"application/json; charset=utf-8","Expires":"-1","Content-Length":"6239"},"body":{"id":"AAMkADI3N2FhN2IwLWNlMDMtNDc1ZC1iZTc2LTk3ZDZkZTQ1MGQ2MABGAAAAAAC7Dt1p9E19Qb2KoQMv7xdjBwDSDyPWvsiySL-_Uv3wKot3AAAAALsjAADSDyPWvsiySL-_Uv3wKot3AAYttQsuAAA=","receivedDateTime":"2022-07-18T12:03:01+00:00","hasAttachments":true,"internetMessageId":"<ADR47000008885803300000D3AC3B2511EED81D230D2AD9BF0C4@MICROSOFT.COM>","subject":"Invoice 9881328328 for Tech Data Distribution s.r.o.","bodyPreview":"   This email originated outside of TD SYNNEX.  Please help keep our organization and partners safe. It's up to us; think before you click.\r\n\r\n\r\n\r\n\r\nDear Tech Data Distribution s.r.o.,\r\n\r\nMicrosoft would like to thank you for your recent business. Please ","importance":"normal","conversationId":"AAQkADI3N2FhN2IwLWNlMDMtNDc1ZC1iZTc2LTk3ZDZkZTQ1MGQ2MAAQABryTCmuL2xHiH7tMHYc2vY=","isRead":false,"isHtml":true,"body":"<html><head>\r\n<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\"></head><body><table border=\"0\" cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" height=\"20\" bgcolor=\"#FFFF00\"><tbody><tr><td bgcolor=\"#FF0000\" width=\"10\"></td><td bgcolor=\"#FFFF00\"><font face=\"corbel\" color=\"#000000\"><b>&nbsp;&nbsp; This email originated outside of TD SYNNEX.&nbsp; Please help keep our organization and partners safe. It's up to us; think before you click.</b></font></td></tr></tbody></table><br><div><br><img alt=\"[image]\" src=\"cid:microsoft.jpg\"><br><p><font face=\"Segoe UI\">Dear Tech Data Distribution s.r.o.,</font></p><p><font face=\"Segoe UI\">Microsoft would like to thank you for your recent business. Please find the attached billing document(s) for your convenience and review. </font></p><p><font face=\"Segoe UI\"><u>Summary of billing details:</u></font></p><span id=\"table1231656_begin\" tabindex=\"0\" title=\"Table; 1 rows, 8 columns; Rows 1 to 1 are displayedTo skip, choose S\"></span><table border=\"1\"><tbody><tr><th tabindex=\"0\" scope=\"col\" bgcolor=\"#0078d4\" title=\"Column Billing Doc Type (Credit Memo or Invoice)\"><font face=\"Segoe UI\" color=\"white\">Billing Doc Type (Credit Memo or Invoice)</font></th><th tabindex=\"0\" scope=\"col\" bgcolor=\"#0078d4\" title=\"Column Billing Document#\"><font face=\"Segoe UI\" color=\"white\">Billing Document#</font></th><th tabindex=\"0\" scope=\"col\" bgcolor=\"#0078d4\" title=\"Column Customer PO#\"><font face=\"Segoe UI\" color=\"white\">Customer PO#</font></th><th tabindex=\"0\" scope=\"col\" bgcolor=\"#0078d4\" title=\"Column Enrollment#\"><font face=\"Segoe UI\" color=\"white\">Enrollment#</font></th><th tabindex=\"0\" scope=\"col\" bgcolor=\"#0078d4\" title=\"Column Billing Date\"><font face=\"Segoe UI\" color=\"white\">Billing Date</font></th><th tabindex=\"0\" scope=\"col\" bgcolor=\"#0078d4\" title=\"Column Due Date\"><font face=\"Segoe UI\" color=\"white\">Due Date</font></th><th tabindex=\"0\" scope=\"col\" bgcolor=\"#0078d4\" title=\"Column Total Amount (Including Tax)\"><font face=\"Segoe UI\" color=\"white\">Total Amount (Including Tax)</font></th><th tabindex=\"0\" scope=\"col\" bgcolor=\"#0078d4\" title=\"Column Currency\"><font face=\"Segoe UI\" color=\"white\">Currency</font></th></tr><tr><td tabindex=\"0\"><font face=\"Segoe UI\" color=\"black\" size=\"3\">Invoice</font></td><td tabindex=\"0\"><font face=\"Segoe UI\" color=\"black\" size=\"3\">9881328328</font></td><td tabindex=\"0\"><font face=\"Segoe UI\" color=\"black\" size=\"3\">3514850857</font></td><td tabindex=\"0\"><font face=\"Segoe UI\" color=\"black\" size=\"3\">68100478</font></td><td tabindex=\"0\"><font face=\"Segoe UI\" color=\"black\" size=\"3\">07/18/2022</font></td><td tabindex=\"0\"><font face=\"Segoe UI\" color=\"black\" size=\"3\">08/22/2022</font></td><td tabindex=\"0\"><font face=\"Segoe UI\" color=\"black\" size=\"3\">350.01</font></td><td tabindex=\"0\"><font face=\"Segoe UI\" color=\"black\" size=\"3\">EUR</font></td></tr></tbody></table><span id=\"table1231656_end\" tabindex=\"0\" title=\"Outbound table; Choose S to return to start\"></span><p><font face=\"Segoe UI\">If this is an invoice, payments should be made by Electronic Funds Transfer (EFT) or ACH. Payment instructions are located on the invoice. If this is a credit or if you have prepaid, please disregard payment instructions. </font></p><p><font face=\"Segoe UI\">Should you have any questions or concerns, please contact your partner and/or MSCREDIT@microsoft.com to facilitate a timely response. </font></p><p><font face=\"Segoe UI\">If you are a partner and have questions, contact your Regional Operations Center using the Call Logging Tool (CLT) at <a href=\"https://urldefense.com/v3/__https://clt.partners.extranet.microsoft.com/CLT__;!!CYQycLDRcg!spPWyDg4CPXJ-RgCSpYXODI0X0HWlQUTrasgUHbcGrzhfcF6_95hIG9udoLehw2D2wFVp8uv-2SXUbRHuhZQ$\">https://clt.partners.extranet.microsoft.com/CLT</a> or via explore.ms.</p><p><font face=\"Segoe UI\"></p><p><font face=\"Segoe UI\">Thank you again,</font></p><p><font face=\"Segoe UI\">Microsoft Operations</font></p><p><font face=\"Segoe UI\">****This email is system generated. Please do not reply.****</font></p></font></font></div></body></html>","from":"msftinv@microsoft.com","toRecipients":"martin.madar@techdata.com;CZinvoices@techdata.eu;Michal.Nemcek@techdata.com;FakturyTechData@techdata.cz;Martin.Hala@techdata.com;Tomas.Kaspar@techdata.com;Ludek.Marold@techdata.com","attachments":[{"@odata.type":"#microsoft.graph.fileAttachment","id":"AAMkADI3N2FhN2IwLWNlMDMtNDc1ZC1iZTc2LTk3ZDZkZTQ1MGQ2MABGAAAAAAC7Dt1p9E19Qb2KoQMv7xdjBwDSDyPWvsiySL-_Uv3wKot3AAAAALsjAADSDyPWvsiySL-_Uv3wKot3AAYttQsuAAABEgAQAPb_U392kfpLkewF-_P1jCw=","lastModifiedDateTime":"2022-07-18T12:03:01+00:00","name":"microsoft.jpg","contentType":"image/jpg","size":2738,"isInline":true},{"@odata.type":"#microsoft.graph.fileAttachment","id":"AAMkADI3N2FhN2IwLWNlMDMtNDc1ZC1iZTc2LTk3ZDZkZTQ1MGQ2MABGAAAAAAC7Dt1p9E19Qb2KoQMv7xdjBwDSDyPWvsiySL-_Uv3wKot3AAAAALsjAADSDyPWvsiySL-_Uv3wKot3AAYttQsuAAABEgAQAGe2M2L029ZDn2nzPMhTNCE=","lastModifiedDateTime":"2022-07-18T12:03:01+00:00","name":"Invoice_9881328328.PDF","contentType":"application/pdf","size":15132,"isInline":false}]}}

 

 

 

The information what I need is the PO number - in this case the value of the PO is 3514850857.

I used HTML to text function and then compose. Unfortunately the best what I'm able to get is this output:

 

 

[
  "Billing Doc Type (Credit Memo or ",
  ")Billing Document#Customer\nPO#Enrollment#Billing DateDue DateTotal Amount (Including\nTax)Currency",
  "988132832835148508576810047807/18/202208/22/2022350.01EUR"
]

 

 

 

Any ideas?

 

Thank you in advance.

Martin

1 ACCEPTED SOLUTION

Accepted Solutions
mhala1234
Regular Visitor

I'm trying to find any existing solution. For now I added symbols which are adding a symbol to the text, so in the output I have at least something what I can use.

mhala1234_0-1658245540470.png

For now I'm trying to get the PO number 3514853596. Any idea how to do that?

 

EDIT: Now I have this data, I believe I will able to solve that in next few hours. If you have any idea how to do it faster, let me know.

mhala1234_0-1658250204497.png

 

EDIT2: So I got the value which I need. Unfortunately there are some additive characters which must be removed.

Thank you for your video tutorial @Paulie78 - https://www.youtube.com/watch?v=f68e4RP2QTs

mhala1234_0-1658250937588.png

mhala1234_1-1658250955096.png

How to remove characters [] and "?

 

EDIT3: Done. I used initialize variable and then another compose value to remove those characters.

View solution in original post

1 REPLY 1
mhala1234
Regular Visitor

I'm trying to find any existing solution. For now I added symbols which are adding a symbol to the text, so in the output I have at least something what I can use.

mhala1234_0-1658245540470.png

For now I'm trying to get the PO number 3514853596. Any idea how to do that?

 

EDIT: Now I have this data, I believe I will able to solve that in next few hours. If you have any idea how to do it faster, let me know.

mhala1234_0-1658250204497.png

 

EDIT2: So I got the value which I need. Unfortunately there are some additive characters which must be removed.

Thank you for your video tutorial @Paulie78 - https://www.youtube.com/watch?v=f68e4RP2QTs

mhala1234_0-1658250937588.png

mhala1234_1-1658250955096.png

How to remove characters [] and "?

 

EDIT3: Done. I used initialize variable and then another compose value to remove those characters.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (4,039)