HTML to Excel

(0) Share

Report

Posted on by Chipper

116

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

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (1)

Sort by

PrasadAthalye on at

Like (0)

Report
Copy link

Link copied!
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]

Was this reply helpful? Yes No
Chipper 116 on at

Like (0)

Report
Copy link

Link copied!
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'))

Was this reply helpful? Yes No
PrasadAthalye on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Chipper 116 on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Verified answer

PrasadAthalye on at

Like (0)

Report
Copy link

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

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

Was this reply helpful? Yes No
Chipper 116 on at

Like (0)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
PrasadAthalye on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No