HTML to Excel

[Chipper]

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.

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.

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....

 

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

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'], ' ',',')

  

 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.

Do you have any suggestions on how best to do this?

Thank you for your help.

Chip

[PrasadAthalye]

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]

[Chipper]

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'))

[PrasadAthalye]

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

[Chipper]

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'.'.

 Thank you for helping me out.

[PrasadAthalye · Accepted solution]

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

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

[Chipper]

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.

[PrasadAthalye]

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