Flow: extract text from an email

[Jay15]

Hi everyone,

I would need your help on this. I would like to extract specific texts from an email. 

What I extract should go in a table in excel. What is extracted should be what is below each name. This table has 3 columns named "Hello", "Hallo" and "Halo". 

Some emails that I receive contain the 3 categories and others only 2 of them. The order is always the same: Hello, Hallo and Halo. 

Email example:

Hello

Xxxxxxx

Hallo

Yyyyyyy

Halo

Zzzzzzzz

I've already created a flow ( email received --> html to text --> add new line in table) but I'm stuck when it comes to transferring those text to excel. 

I want the flow to understand that if "Hallo" is missing, it should go to the next one "Halo" and extract what is below. 

Thank you in advance for your help ND please let me know if you want me to clarify anything.

[grantjenkins]

Are you able to show exactly what you would have in the email including other text before and after the content you provided? You mentioned a table, but the example looks like it's just a list of key/value pairs.

[Jay15]

Well I would receive an email in outlook saying:

"Hi Jay,

Please add the following informations for each category in excel:

category 1:

Xxxxxxx

category 2:

Yyyyyyyyy

category 3:

Zzzzzzzz

Thank you for your help,

Best regards,"

And then the header of my table in excel contains:

(cell A1)"category 1", (cell B1)"category 2" and (cell C1)"category 3". 

And for each email that I would receive, it would add one new line. And also from time to time "category 2" could be missing in the email. I have used an expression that tells to extract the text between values and it worked. But somehow when the expression says extract the text between the value "category 1" and "category 2", if there is no "category 2" mentioned in the email take the text between "category 1" and "category 3", then there's a problem. 

For the text under "category 3", I would extract the text between "category 3" and "Thank you". This part at the end of the email is always the same. But I'm having a problem with the "category 2".

Let me show you the expression and flow later on today as I am not at home now. 

Hopefully you understand better my problem. Thank you again for your help and sorry if my explanation is a bit messy 😕

[Jay15]

Hi again,

Here are some extra infos:

1. expression that worked: (takes the text between "category 1" and "category 2"

substring(join(body('HTML_zu_Text_1'), ''), add(indexOf(join(body('HTML_zu_Text_1'), ''), 'category 1'), 10), sub(indexOf(join(body('HTML_zu_Text_1'), ''), 'category 2'), add(indexOf(join(body('HTML_zu_Text_1'), ''), 'category 1'), 10)))

2. expression that did not work: (takes the text between "category 1" and "category 3", in case "category 2" is not in the email)

• substring( join(body('HTML_zu_Text_1'), ''), add(indexOf(join(body('HTML_zu_Text_1'), ''), 'category 1'), 9), sub( coalesce( indexOf(join(body('HTML_zu_Text_1'), ''), 'category 2'), indexOf(join(body('HTML_zu_Text_1'), ''), 'category 3') ), add(indexOf(join(body('HTML_zu_Text_1'), ''), 'category 1'), 9) ) )

Sorry, a part of the screenshot is in german.

 

[grantjenkins]

This is how I would likely build the flow.

For this example, I've got the following Excel Table.

See full flow below. I'll go into each of the actions.

When a new email arrives is what you already have.

Html to text converts the body of the email to text.

Compose extracts the data we want using the following expression.

slice(
    outputs('Html_to_text')?['body'], 
    indexOf(outputs('Html_to_text')?['body'], 'Please add the following informations for each category in excel'), 
    indexOf(outputs('Html_to_text')?['body'], 'Thank you for your help')
)

Filter array splits the data by new line and removes all empty rows, and skips the first row which is our "Please add the following...".

//From
skip(split(outputs('Compose'), decodeUriComponent('%0A')), 1)

//Condition
trim(item())

Initialize variable creates a variable called categories of type object, and sets it to an empty object using an opening and closing curly brace {}.

Apply to each iterates over the items two at a time using the chunk expression.

chunk(body('Filter_array'), 2)

Compose Append appends each of the properties using the following expression.

addProperty(
    variables('categories'), 
    item()?[0], 
    item()?[1]
)

Set variable sets the categories variable to the value in Compose Append.

After the Apply to each, we have Add a row into a table using the following expressions.

variables('categories')?['category 1:']

variables('categories')?['category 2:']

variables('categories')?['category 3:']

After the flow runs we will have a new row in our Excel table. If not all categories are present in the email then they will get an empty value.

[Jay15]

Thank you so much for your help. 

It looks good. 

Now it's a bit tricky because my power automate is in German and somehow I don't have the same way to write variables as you. I'll need to try it again as I've tried and I've got errors/synthax. 

[Jay15]

I've just changed the language of my power automate in english but I've got an error. Im asking myself how is it possible? Ive followed your instructions... I need to check it again what I made wrong :

 

InvalidTemplate

Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language function 'indexOf' expects its first parameter to be of type string. The provided value is of type 'Null'.

[Jay15]

Now everything is working except the step "apply to each".

Here is a screenshot:

 

[grantjenkins]

For your Compose, did you change it to match the actual content you expect in your email?

//Change the two sentences to match what is in your email.

slice(
    outputs('Html_to_text')?['body'], 
    indexOf(outputs('Html_to_text')?['body'], 'Please add the following informations for each category in excel'), 
    indexOf(outputs('Html_to_text')?['body'], 'Thank you for your help')
)

[Jay15]

Actually I have adapted my email according to your code:

Please add the following informations for each category in excel

category 1:

Xxxxxxx

category 2:

Yyyyyyyyy

category 3:

Zzzzzzzz

Thank you for your help

here are some screenshots:

 

 Thank you again for your help. 

[Jay15]

I've just found my mistake. I've chosen "compose" instead of "compose append". But the thing is, I cannot find "compose append". How is this possible? Any idea why ?

[grantjenkins]

Compose Append is just a Compose that I renamed to Compose Append.