cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JurajB
New Member

How to: html table in email to SharePoint list

I receive an email with server logs that has couple of simple fields like

--------------------

Name: John Dough

System: Windows

Reason: Fix stuff

---------------------

I built a workflow that takes the email, converts its html to text and inserts it into SharePoint list but I need name, system and reason in individual coulmns.

 

How can I achieve it? How can I read certain line only or after certain text grab everything and put it into the list?

 

many thanks

1 ACCEPTED SOLUTION

Accepted Solutions
v-bacao-msft
Community Support
Community Support

Hi @JurajB,

 

If the content in the email body is the one you mentioned, you could consider using WDL function to achieve your needs:

5.PNG

 

Name:

trim(first(split(last(split(body('Html_to_text'),'Name:')),'System:')))

System:

trim(first(split(last(split(body('Html_to_text'),'System:')),'Reason:')))

Reason:

If Reason is the last field, you could use the following formula, like:

7.PNG

trim(last(split(body('Html_to_text'),'Reason:')))

If Reason is not the last content, you could follow the expression that gets Name and use String after Reason value as the boundary of split() function, like:

6.PNG

trim(first(split(last(split(body('Html_to_text'),' Reason:')),'Example:')))

Run history:

8.PNG

There is a similar Thread here, you could also refer to the methods provided:

https://powerusers.microsoft.com/t5/Building-Flows/Extract-text-from-body-of-an-email-and-create-new...

Please take a try.

 

Best Regards,

 

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

View solution in original post

4 REPLIES 4
SandyU
MVP

Hi @JurajB

You could do this using various String functions in Flow. Here's what I've tested to grab the name, so you could do something similar to get the other values...

After the Convert Html to Text action, I use Compose to find the place in the text where the person's name begins. (I could have done this within the next Compose also, but I would have had to repeat this expression twice, so I put it in its own Compose.)

add(IndexOf(body('Html_to_text'),'Name:'),6)

This finds the location in the 'Html_to_text' body where 'Name:' begins - so if it's truly at the beginning of the email body, that's an index of 0. And then add 6, which is the number of characters in 'Name: ' (including the space). This tells us where the actual name begins (index location 6). I renamed this action step to ComposeNameLoc, so I can use it in the next step...

 

Next, I added another Compose, which I'm calling ComposeName, the output of which will be the actual person's name (John Dough). To do that, the following expression takes the location of the beginning of the name, which we got above, and grabs the Substring with a length calculated by finding where the next keyword [System:] starts.

substring(body('Html_to_text'),outputs('ComposeNameLoc'),sub(indexOf(body('Html_to_text'),'System:'),outputs('ComposeNameLoc')))
Breaking this expression apart, working from the inside out, it finds the index location where 'System:' begins: indexOf(body('Html_to_text'),'System:')

Then the sub function subtracts the index location where the actual name began, which came from the previous step: outputs('ComposeNameLoc'). This gives us the length of the person's name.

And then the substring function again uses the 'Html_to_text' body as its input, and begins a substring at ComposeNameLoc, and the length of the substring is the result of the sub function. This gives us the person's name, no matter how many characters it has...

Does this make sense...? What I often do when I'm trying to come up with an expression like this, is to try pieces of it in a Compose action, and run (or Test) the Flow to see the result and make sure it's producing the output I expect, before adding another function to the expression, building it from the inside out as I've described above. 

 

So then after doing something similar to parse out each of the three pieces of data, you can use the SharePoint Create an Item action, using the outputs from the Compose actions as the values to save in the columns.

 

Does this help you...?

 

Sandy

Sandy
@SandyU



Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


v-bacao-msft
Community Support
Community Support

Hi @JurajB,

 

If the content in the email body is the one you mentioned, you could consider using WDL function to achieve your needs:

5.PNG

 

Name:

trim(first(split(last(split(body('Html_to_text'),'Name:')),'System:')))

System:

trim(first(split(last(split(body('Html_to_text'),'System:')),'Reason:')))

Reason:

If Reason is the last field, you could use the following formula, like:

7.PNG

trim(last(split(body('Html_to_text'),'Reason:')))

If Reason is not the last content, you could follow the expression that gets Name and use String after Reason value as the boundary of split() function, like:

6.PNG

trim(first(split(last(split(body('Html_to_text'),' Reason:')),'Example:')))

Run history:

8.PNG

There is a similar Thread here, you could also refer to the methods provided:

https://powerusers.microsoft.com/t5/Building-Flows/Extract-text-from-body-of-an-email-and-create-new...

Please take a try.

 

Best Regards,

 

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

View solution in original post

Thanks @SandyU and @v-bacao-msft for detailed explanations. I was able to use what @v-bacao-msft suggested and it works as expected. Beautiful!

I want to ask a follow up question - of on the fields (didn't include in the original question) consists of two words or more words and powershell or something splits it up in the middle into a new line and the workflow breaks. Is there something I can do to account for and fix in the flow or do I need to adjust the email template to single words/hyphens?

 

thanks!

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 (40,739)