cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
paddy76
Frequent Visitor

Email Body Data to Excel Business Table

Hi All,

 

I have the following data in an email.

 

Statistic Date:07.01.2021
OPD:1234
IPD:0111
OR:2468

 

I need the above 4 copied to a single row that has 4 columns in Excel like below:

 

Date | OPD | IPD | OR

07.01.2021 | 1234 | 0111 | 2468

 

I am completely stuck on how to get the flow to do what I want. Any help greatly appreciated 🙂

 

14 REPLIES 14
fchopo
Super User II
Super User II

Hello @paddy76 

You should use the split operation, using the carriage return. As it explained in this post, you have to use a variable with a line break.

compose2.png

 

Hope it helps!

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!
v-yiwenxie-msft
Microsoft
Microsoft

 

Thanks for your solution @fchopo. And I want to make a supplement.

 

Hi @paddy76 

 

First you need to separate the input and get those cells you want.

You can do some data operations to the input as below:

1.11-1.PNG

1.11-2.PNG

 

Go to the link to see what function ‘split’ means: Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

 

 

Next,  add content to excel as below.

1.11-3.PNG

 

Go to the link to see what function ‘variables’ means: Reference guide for functions in expressions - Azure Logic Apps | Microsoft Docs

 

 

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

paddy76
Frequent Visitor

Thank you so very much Kira. 

 

Your suggestion did work really well. Sadly, it worked 3 times and but then it stopped working 😞 

 

Error is in the "apply to each" part in the solution you gave.

 

The error reported says: 

 

Unable to process template language expressions in action 'Append_to_array_variable' inputs at line '1' and column '26863': 'The template language expression 'split(item(),':')[1]' cannot be evaluated because array index '1' is outside bounds (0, 0) of array. Please see https://aka.ms/logicexpressions for usage details.'.

 

Getting rid of the HTMLtoTEXT step and just using COMPOSE pasted values works too. But not if it is from email. That only worked 3 times like I said.

 

I also have tried several "cures" suggested in various forums. Saving it as a a different filename fixes it for 1 or 2 times as well. Then same error. 😞 Can you advise?

paddy76
Frequent Visitor

If I change split(item(),':')[1]     to    split(item(),':')[0]

 

it doesn't error. But then obviously the results in the spreadsheet aren't what we want 😞

v-yiwenxie-msft
Microsoft
Microsoft

Hi @paddy76 

 

It seems that your source data has missed the punctuation':'.

 

The [0] here in split(item(),':')[0] is used to take the first object in an array. [1] is used to take the second object in the array, and so on.

 

So, if the number of objects in an array is only one and you use [1] to get the second object which doesn't exist, it causes an error.

 

Make sure that you have put the punctuation ':' in every line the same way as below:

1.14-4.PNG

 

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Kira Xie,

 

Thanks for the reply. I do have exact the same inputs in there, so it is not missing the ':'

 

It is random though. It works sometimes and sometimes not with the same error. Clearing cache logging out and back in again seems to do the trick, but just sometimes. It must be something else, we can't use Flow for our application, because it just randomly fails. Actually it also does it occasionally on the "Add row" part, after first passing.

paddy76
Frequent Visitor

runs.JPGrandom.jpg

v-yiwenxie-msft
Microsoft
Microsoft

Hi @paddy76 

 

Could you share the screenshot of detailed data of all the steps in the failed flow?

 

To solve the problem, I need more info to check whether your data is in correct structure which is needed to make the flow succeed.

 

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi 🙂

 

It is all the same flow actually. Sometimes works, sometimes does not.

 

Should I just take a screenshot of each step?

 

Paulie78
Super User III
Super User III

I would like to have a look at this as well, could you post a screenshot of your entire flow as it is right now. Also could you post the HTML source of the email.

v-yiwenxie-msft
Microsoft
Microsoft

Hi @paddy76 

 

Yes.

Could you take a screen shot of each step before the flow runs?

 

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Sure 🙂

 

11223344before5.JPGbefore6.JPGbefore7.JPGbefore8.JPGbefore9.JPGbefore10a.JPGbefore10b.JPG

firstvariable-outputfirstvariable-output82result.jpg83result.jpg84result.jpg

The variables outputs is almost always where it fails. 

v-yiwenxie-msft
Microsoft
Microsoft

Hi @paddy76 

 

Please check the 'body' of the email only contains the data you want to fill into excel columns as below:

2.2-3.PNG

 

If the email body contains other data, don't worry, you can use 'filter array' action to get the data you want. 

 

The reason why the variable output fails is that the data input is not in a right structure. 

 

If problem still exists, could you please share the screenshot of your email body with private information covered up?

 

Best Regards,
Community Support Team _ Kira Xie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (70,757)