cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chipper
Helper I
Helper I

HTML to Excel

I have spent the better part of a day trying to beat this thing into submission. 

 

Here is what I have.

I get an email that a bunch of data in it.

 

Screen Shot 2021-05-14 at 7.30.45 PM.png

 

LBR: 0.2%
DEL: 4
Deliv Pct: 38.6%
On Runs: 3
Employee Punch-In Overridden: 8
Load Time: -1.0
Net: $3,538.63
   Last WK: $4,510.22
   LW%: -21.5%
   '20: $4,338.81
   '20%: -18.4%
ADT: 27.6
Order Load Time: 4.7
OTD: 13.3
Current Hr: 21714
UNM: 4
#Pies: 12
Returning: 1
LBR: 14.3%
Queued Items: -1
Past 1st Shift: -1
NoDeliv Waiting: 19
Non-pizza unmade: 2
Order Length: 1:39
Order Expedited Time: 0.0
Load: 6.2
In store: 28
Open: 30
Oven Items Hr: 0
7:13 pm: 
WEB: 17
Voided Orders: 0
Voided Items: 0
Voided Amount: $0.00

 

I have tried my best to get this into some sort of usable format to be able to pull parts out of it for an excel sheet that I want to build. This email comes to me about every 15 min.

 

I have made this flow.

 

Screen Shot 2021-05-14 at 7.33.39 PM.png

 

The first expression is like this:  

first(skip(split(first(split(body('Html_to_text'),'Queued Items: ')),'LBR'),1))
 
However, this return all of this....
 Screen Shot 2021-05-14 at 7.37.00 PM.png

 You can see at the bottom that the "Returning: 1" is just above the data that I want.

Screen Shot 2021-05-14 at 7.37.59 PM.png

But I figured that may have something to do with the other "LBR" that is found at the top of the data.

 

Ok, so I took a different approach and tried this expression in hopes that I could change the spaces for "," and try and to a text to column sort of thing using a macro.

replace(outputs('Html_to_text')?['body'], ' ',',')
  Screen Shot 2021-05-14 at 7.41.53 PM.png

 But guess what, you can't do a text to columns with a macro.  🤣

Now I had another thought and maybe I could somehow take the text from the HTML to text part and create a CVS.  But I haven't been able to figure a way of doing that.   I tried this, but I get an error.

 

Screen Shot 2021-05-14 at 7.45.16 PM.png
 
Do you have any suggestions on how best to do this?
 
Thank you for your help.
 
Chip
1 ACCEPTED SOLUTION

Accepted Solutions

HTML to text is not a variable. Please use following code...

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

 

 
Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community

View solution in original post

7 REPLIES 7
PrasadAthalye
Super User
Super User

Can you split the string based on new line delimiter and then use array elements as you need?

 

Declare a variable called delimiter and just press enter key in the value. Then use that variable to split and then use array elements...

 

Split like below

split(variables('PertManager'), variables('delimitor'))

 

and then use individual elements like below...

 

outputs('Compose_2')[0]

 

PrasadAthalye_0-1621042793549.png

 

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community

When I went to save the flow, I got this message.

 

Flow save failed with code 'InvalidVariableOperation' and message 'The inputs of workflow run action 'Compose' of type 'Compose' are not valid. The variable 'PertManager' must be initialized before it can be used inside action 'Compose'.'.

 

What is "pertmanager"?

split(variables('PertManager'), variables('delimitor'))

@Chipper - PertManager is a variable that I used for my testing purpose. Please use HTML body from your trigger instead.

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community

Thats what I thought, but I get the same error.

 

split(variables('html_to_text'), variables('delimitor'))

 

Flow save failed with code 'InvalidVariableOperation' and message 'The inputs of workflow run action 'Compose' of type 'Compose' are not valid. The variable 'html_to_text' must be initialized before it can be used inside action 'Compose'.'.

 

Screen Shot 2021-05-18 at 4.05.15 PM.png

 Thank you for helping me out.

HTML to text is not a variable. Please use following code...

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

 

 
Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community

View solution in original post

I thought I had tried that, but i guess not good enough.  However, That did the trick.  But.......  You knew this was coming.

 

Apparently, the carriage return is not where I thought they were.  I was hoping that they would be at the end of each line, however, I am guessing they are not.  This is the ouput:

 

[
    "Employee Punch-In Overridden: 1 Load: 1.4 LBR: 69.3% Load Time: -1.0 Past 1st",
    "Shift: -1 Net: $300.22 Last WK: $2,433.96 LW%: -87.7% '20: $2,620.71 '20%:",
    "-88.5% NoDeliv Waiting: 0 Non-pizza unmade: 1 ADT: 0.0 Order Expedited Time: 0.0",
    "Order Length: 0:00 Order Load Time: 0.0 OTD: 11.5 Current Hr: 4905 In store: 1",
    "Open: 2 UNM: 0 Oven Items Hr: 0 #Pies: 13 Queued Items: -1 Returning: 1 2:43 pm:",
    "Voided Amount: $0.00 Voided Items: 0 Voided Orders: 0 WEB: 6"
]

So I changed the delimiter to a space and it outputs a more manageable set of data.  It is going to make the flow rather long but I can slice and dice it back together in excel.

 

Is there perhaps a way to have the output read something like give me what is in between "Load:" and "LBR:"?

 

Thank you again for your time.

Only way then is to play with Substring function to get the value...

Please Like and Mark this as Answer if it resolves your Issue.

Power Automate Community

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (3,016)