Answered

Reading data from e-mail table and then updating Excel file

(0) Share

Report

Posted on by barp21

Hello,

I'm trying to build a flow that counts comments from a table distributed daily via email (example below):

The idea would be to count rows where comments were provided (total variable value) and then look for some other words than "Reporting Available" (missing variable value). By dividing the missing by the total I would have a fault ratio (ratio variable).

Having that in place solution should be adding rows to a table each time when e-mail arrives (picture 2).

I have stuck with counting the occurrences within the table. Any ideas on how to move-on are more than appreciated.

Categories:

Building flows

All responses (5)

Answers (1)

Sort by

Caspar_Rubin 831 on at

Like
a
(0)

Report
Copy link

Link copied!
Hey,

there might of course be better or easier ways to do this but I would do it like this:

I created two Excel files. Excel1.xlsx and Excel2.xlsx. Make sure you actually make tables out of your data under the "Insert" tab, click on "Table". If you do not do that, the Excel actions won't be able to get your data!

My tables are both called Table1.
So Table1 in Excel1.xlsx is the table with all the comment stuff, and Table1 in Excel2.xlsx is the table with the date & percentage.

Now, the flow:

Recurrence trigger once a day
List rows present in a table action and point it towards Table1 in the first Excel (Excel1.xlsx).
Initialize two new variables. ReportingAvailable and ReportingNotAvailable, both as Float, not Integer.

Add an Apply to each action and use the Dynamic content "value" from the List rows present in a table action.
First, check with a Condition action if the Comments (dynamic content) is equal to Reporting Available.
If yes, Increment the ReportingAvailable variable by 1 by using the Increment variable action.
If no, add another Condition action and check if the Comments is not equal to "".
If that Condition is yes, then Increment the second variable ReportingNotAvailable.

(If you want to count empty values towards the percentage as well, you can just remove the second Condition and put the second Increment variable action into the first If no...)

After that, add the Current time action to get todays date and after that, use the Convert time zone to convert the Current time dynamic value to your time zone and format of your liking.
For example I go from UTC to UTC+1 and format it like this: dd.MM.yyyy

For the last step, add a Add a row into a table action and point it towards Table1 in the second Excel (Excel2.xlsx)
For Date, you use the dynamic value from the Converted time action.
For Missing data, you use the following Expression:

mul(div(variables('ReportingNotAvailable'), variables('ReportingAvailable')), 100)

Don't forget to add the % sign after the Expression. 😃

let us know if this works,
cheers
barp21 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks, @Caspar_Rubin for this detailed explanation. I guess I haven't pointed out this as strong as I should but the table with data is a part of the e-mail (the table is part of the HTML mail, not an attachment). So the trigger to this action would have to be an e-mail and then there is a challenge of converting mail to excel and then following the actions described you.
Verified answer

Caspar_Rubin 831 on at

Like
a
(0)

Report
Copy link

Link copied!
Hey,

I might be a little off here but what if you take the content of the E-Mail and just simply check how many times "Reporting Available" occurs in the E-Mail. And then do the same for "Reporting Not Available".

I don't think we need to do anything with the table inside the E-Mail, right?

When a new email arrives trigger
Html to text (preview) action with the Dynamic content Body from the trigger
Initialize a new variable called newLine as a String and in the Value just press Enter key once.
Then use a Compose action to split the E-Mail into an array delimited by newLine.
The Expression would be:

split(outputs('Html_to_text')?['body'], variables('newLine'))

The above would result in the following Output in the Compose SplitUpEmail:
When you scroll down, you can see that how the E-Mail was split into different values. Every row is a value.

We now just have to loop every value and count how many times Reporting Available and Reporting not Available occurs in the array.

To do that, Initialize another two new Variables. ReportingAvailable and ReportingNotAvailable, both Float without any Value.
The Apply to each action looks almost the same as before. We just loop the Outputs Dynamic content from the Compose SplitUpEmail action and check with two Conditions if the value (Expression: item()) is Reporting Available, and if not, if it is Reporting Not Available. If it's true, we increment the related variable by 1.

After the Apply to each, we can then simple add the steps where we add a new Row to the Excel table with Date & Percentage.

The Formular / Expression is still the same:

mul(div(variables('ReportingNotAvailable'), variables('ReportingAvailable')), 100)

cheers
barp21 on at

Like
a
(0)

Report
Copy link

Link copied!

It works perfect @Caspar_Rubin , thanks for all the effort put in place to look for a solution and explain it so detail.
barp21 on at

Like
a
(0)

Report
Copy link

Link copied!

I have one more question here to make the solution even more effective. How I can cut from the e-mail body which I receive in the step "HTML to text" to evaluate not the whole e-mail but only a part of it which is staring after a given sentence like "APO BW" for example?