cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LoaiAlhalabi
Frequent Visitor

Reading Table from Email and Adding it to Excel Sheet (Business)

Hi All, 

 

i'm trying to read an email and the parse the data from it into some variables and table so i can use it for further flows.

 

fow now, i have the following.

this is a sample of the Email received which contains the table and some field i need to read them.

(note: table columns is fixed - only rows are variables ranging from 1 row to N) 

 

Untitled.png

 

i start by converting the email from HTML to text as follows

 

Screenshot 2021-10-13 231538.png

 

Splitintolines

 

 

 

 

 

 

split(outputs('Html_to_text')?['body'],outputs('Newline'))

 

 

 

 

 

 

where NewLine is a new line compose only.

Screenshot 2021-10-13 232724.png

 

(Outputs)

----------

(HTML to Text)

 

 

 

 

 

From: ad.rew@si.com <ad.rew@si.com>
Sent: Wednesday, October 13, 2021 12:17 PM
To: Loai Alhalabi (MEA) <Loai.Alhalabi@si.com>
Subject: Quote Number 3745556 Quote Name customer 1 - SOC Room Survey (84575)
for Buyer Royal Court requires your approval for processing

 

direct
global
e-marketplace



 * Reasons for Violation:

Rule

Additional Comments

Overall Margin >= 0.00% And Amount >= 0.00.

 



Approver: xxx,yyy
Buyer: customer 1
Quote Number: 3745556
Quote Name: customer 1  - SOC Room Survey (84575)
Created By: solution member 1
Technology Margin: 20.00%
Services Margin: N/A
Total: SAR 0.00
requires your immediate approval.
Please action below accordingly.
Approve
[https://protect-eu.mimecast.com/s/t-WpCP1nAf3lRzZRC0oPI7?domain=direct.si.global.ss]
OR Reject
[https://protect-eu.mimecast.com/s/ywxACQ1oBfoxpv9ptMeQkZ?domain=direct.si.global.ss]


MANUFACTURER SUMMARY

Manufacturer

Ext Price

Ext Cost

Ext Margin

Margin

Apple

SAR 4,021.88

SAR 3,217.50

SAR 804.38

20%

Microsoft

SAR 187,110.00

SAR 149,688.00

SAR 37,422.00

20%

Cisco

SAR 12,110.63

SAR 9,688.50

SAR 2,422.13

20%

xyz

SAR 62,500.00

SAR 50,000.00

SAR 12,500.00

20%

LG Electronics

SAR 237,500.00

SAR 190,000.00

SAR 47,500.00

20%

DDRXS

SAR 21,680.85

SAR 17,344.75

SAR 4,336.10

20%

Logic

SAR 42,000.00

SAR 33,600.00

SAR 8,400.00

20%

TOTAL

SAR 566,923.36

SAR 453,538.75

SAR 113,384.61

20%



PRODUCT SUMMARY

Item Type

Ext Price

Ext Cost

Ext Margin

Margin

Product

SAR 566,923.36

SAR 453,538.75

SAR 113,384.61

20%

TOTAL

SAR 566,923.36

SAR 453,538.75

SAR 113,384.61

20%



MARGIN WORKFLOW DETAILS

Approver

Date Actioned

Action

Comments

Manager 1

 

Pending

Manager 2

 

Pending



ad rew (MEA)

AV Solution Specialist

m. +

t.

email.

si.com [https://www.si.com/]

.

.



This email and all contents are subject to the following disclaimer:
https://www.si.com/email-disclaimer

 

 

 

 

 

 

Splitintolines Output 

 

 

 

 

 

 

[
  "From: ad.rew@si.com <ad.rew@si.com>",
  "Sent: Wednesday, October 13, 2021 12:17 PM",
  "To: Loai Alhalabi (MEA) <Loai.Alhalabi@si.com>",
  "Subject: Quote Number 3745556 Quote Name customer 1 - SOC Room Survey (84575)",
  "for Buyer Royal Court requires your approval for processing",
  "",
  " ",
  "",
  "direct",
  "global",
  "e-marketplace",
  "",
  "",
  "",
  " * Reasons for Violation:",
  "",
  "Rule",
  "",
  "Additional Comments",
  "",
  "Overall Margin >= 0.00% And Amount >= 0.00.",
  "",
  " ",
  "",
  "",
  "",
  "Approver: xxx,yyy",
  "Buyer: customer 1",
  "Quote Number: 3745556",
  "Quote Name: customer 1  - SOC Room Survey (84575)",
  "Created By: solution member 1",
  "Technology Margin: 20.00%",
  "Services Margin: N/A",
  "Total: SAR 0.00",
  "requires your immediate approval.",
  "Please action below accordingly.",
  "Approve",
  "[https://protect-eu.mimecast.com/s/t-WpCP1nAf3lRzZRC0oPI7?domain=direct.si.global.ss]",
  "OR Reject",
  "[https://protect-eu.mimecast.com/s/ywxACQ1oBfoxpv9ptMeQkZ?domain=direct.si.global.ss]",
  "",
  "",
  "MANUFACTURER SUMMARY",
  "",
  "Manufacturer",
  "",
  "Ext Price",
  "",
  "Ext Cost",
  "",
  "Ext Margin",
  "",
  "Margin",
  "",
  "Apple",
  "",
  "SAR 4,021.88",
  "",
  "SAR 3,217.50",
  "",
  "SAR 804.38",
  "",
  "20%",
  "",
  "Microsoft",
  "",
  "SAR 187,110.00",
  "",
  "SAR 149,688.00",
  "",
  "SAR 37,422.00",
  "",
  "20%",
  "",
  "Cisco",
  "",
  "SAR 12,110.63",
  "",
  "SAR 9,688.50",
  "",
  "SAR 2,422.13",
  "",
  "20%",
  "",
  "xyz",
  "",
  "SAR 62,500.00",
  "",
  "SAR 50,000.00",
  "",
  "SAR 12,500.00",
  "",
  "20%",
  "",
  "LG Electronics",
  "",
  "SAR 237,500.00",
  "",
  "SAR 190,000.00",
  "",
  "SAR 47,500.00",
  "",
  "20%",
  "",
  "DDRXS",
  "",
  "SAR 21,680.85",
  "",
  "SAR 17,344.75",
  "",
  "SAR 4,336.10",
  "",
  "20%",
  "",
  "Logic",
  "",
  "SAR 42,000.00",
  "",
  "SAR 33,600.00",
  "",
  "SAR 8,400.00",
  "",
  "20%",
  "",
  "TOTAL",
  "",
  "SAR 566,923.36",
  "",
  "SAR 453,538.75",
  "",
  "SAR 113,384.61",
  "",
  "20%",
  "",
  "",
  "",
  "PRODUCT SUMMARY",
  "",
  "Item Type",
  "",
  "Ext Price",
  "",
  "Ext Cost",
  "",
  "Ext Margin",
  "",
  "Margin",
  "",
  "Product",
  "",
  "SAR 566,923.36",
  "",
  "SAR 453,538.75",
  "",
  "SAR 113,384.61",
  "",
  "20%",
  "",
  "TOTAL",
  "",
  "SAR 566,923.36",
  "",
  "SAR 453,538.75",
  "",
  "SAR 113,384.61",
  "",
  "20%",
  "",
  "",
  "",
  "MARGIN WORKFLOW DETAILS",
  "",
  "Approver",
  "",
  "Date Actioned",
  "",
  "Action",
  "",
  "Comments",
  "",
  "Manager 1",
  "",
  " ",
  "",
  "Pending",
  "",
  "Manager 2",
  "",
  " ",
  "",
  "Pending",
  "",
  "",
  "",
  "ad rew (MEA)",
  "",
  "AV Solution Specialist",
  "",
  "m. +",
  "",
  "t.",
  "",
  "email.",
  "",
  "si.com [https://www.si.com/]",
  "",
  ".",
  "",
  ".",
  "",
  "",
  "",
  "This email and all contents are subject to the following disclaimer:",
  "https://www.si.com/email-disclaimer"
]

 

 

 

 

 

FilterArray Output

 

 

 

 

 

[
  "From: ad.rew@si.com <ad.rew@si.com>",
  "Sent: Wednesday, October 13, 2021 12:17 PM",
  "To: Loai Alhalabi (MEA) <Loai.Alhalabi@si.com>",
  "Subject: Quote Number 3745556 Quote Name customer 1 - SOC Room Survey (84575)",
  "for Buyer Royal Court requires your approval for processing",
  "direct",
  "global",
  "e-marketplace",
  " * Reasons for Violation:",
  "Rule",
  "Additional Comments",
  "Overall Margin >= 0.00% And Amount >= 0.00.",
  "Approver: xxx,yyy",
  "Buyer: customer 1",
  "Quote Number: 3745556",
  "Quote Name: customer 1  - SOC Room Survey (84575)",
  "Created By: solution member 1",
  "Technology Margin: 20.00%",
  "Services Margin: N/A",
  "Total: SAR 0.00",
  "requires your immediate approval.",
  "Please action below accordingly.",
  "Approve",
  "[https://protect-eu.mimecast.com/s/t-WpCP1nAf3lRzZRC0oPI7?domain=direct.si.global.ss]",
  "OR Reject",
  "[https://protect-eu.mimecast.com/s/ywxACQ1oBfoxpv9ptMeQkZ?domain=direct.si.global.ss]",
  "MANUFACTURER SUMMARY",
  "Manufacturer",
  "Ext Price",
  "Ext Cost",
  "Ext Margin",
  "Margin",
  "Apple",
  "SAR 4,021.88",
  "SAR 3,217.50",
  "SAR 804.38",
  "20%",
  "Microsoft",
  "SAR 187,110.00",
  "SAR 149,688.00",
  "SAR 37,422.00",
  "20%",
  "Cisco",
  "SAR 12,110.63",
  "SAR 9,688.50",
  "SAR 2,422.13",
  "20%",
  "xyz",
  "SAR 62,500.00",
  "SAR 50,000.00",
  "SAR 12,500.00",
  "20%",
  "LG Electronics",
  "SAR 237,500.00",
  "SAR 190,000.00",
  "SAR 47,500.00",
  "20%",
  "DDRXS",
  "SAR 21,680.85",
  "SAR 17,344.75",
  "SAR 4,336.10",
  "20%",
  "Logic",
  "SAR 42,000.00",
  "SAR 33,600.00",
  "SAR 8,400.00",
  "20%",
  "TOTAL",
  "SAR 566,923.36",
  "SAR 453,538.75",
  "SAR 113,384.61",
  "20%",
  "PRODUCT SUMMARY",
  "Item Type",
  "Ext Price",
  "Ext Cost",
  "Ext Margin",
  "Margin",
  "Product",
  "SAR 566,923.36",
  "SAR 453,538.75",
  "SAR 113,384.61",
  "20%",
  "TOTAL",
  "SAR 566,923.36",
  "SAR 453,538.75",
  "SAR 113,384.61",
  "20%",
  "MARGIN WORKFLOW DETAILS",
  "Approver",
  "Date Actioned",
  "Action",
  "Comments",
  "Manager 1",
  "Pending",
  "Manager 2",
  "Pending",
  "ad rew (MEA)",
  "AV Solution Specialist",
  "m. +",
  "t.",
  "email.",
  "dimensiondata.com [https://www.si.com/]",
  ".",
  ".",
  "This email and all contents are subject to the following disclaimer:",
  "https://www.si.com/email-disclaimer"
]

 

 

 

 

 

 

Buyer Compose Output as follows

Screenshot 2021-10-14 105255.png

 

now i have two problems:

 

1- some time the index of the fields change due to some additional lines in the email (so the variables not getting the real values as it should be)

 

i'm using this to get the Buyer, Quote Number, Quote Name, Quote Created By, etc.. 

 

 

 

 

 

 

 

split(body('Filter_array')[18],':')[1]

 

 

 

 

 

 

 

while [18] is the index.

 

2- how can i read the data from the "MANUFACTURER SUMMARY" Table since the data coming now into Arrays not as a table.

 

do i need to loop over the array, if yes; how i can know exactly where to start the loop from and where to end with konwing that i tried to use compose and varible to get the index of ['MANUFACTURER SUMMARY'] item from the array keep getting this error

 

Screenshot 2021-10-13 232329.png

 

 

3 REPLIES 3
Jronash
Solution Supplier
Solution Supplier

It would help if you could post the output of your HTML to Text action, and the expression you are using in your compose action where you are trying to find the line that contains MANUFACTURER SUMMARY.

Hi I have added the details to the main thread

Jronash
Solution Supplier
Solution Supplier

I think it is a mistake to start with the HTML to Text action.  That effectively flattens your table, making it much harder to reconstruct it in JSON.  If you use leave the HTML tags in, you can use them to transform your data.  This video shows a good example of the process.

How to convert a HTML Table to JSON with Power Automate - YouTube

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Users online (1,558)