Email Body Data to Excel Business Table

[paddy76]

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 🙂

[fchopo]

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.

Hope it helps!

Ferran

[v-yiwenxie-msft]

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:

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.

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]

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]

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]

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:

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]

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]

[v-yiwenxie-msft]

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.

[paddy76]

Hi 🙂

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

Should I just take a screenshot of each step?

[Paulie78]

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]

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.

[paddy76]

Sure 🙂

1234

[paddy76]

firstvariable-output

The variables outputs is almost always where it fails. 

[v-yiwenxie-msft]

Hi @paddy76 

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

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.