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

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
Community Champion
Community Champion

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

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
Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Users online (5,470)