cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JurajB
Level: Powered On

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
Highlighted
Community Support Team
Community Support Team

Re: How to: html table in email to SharePoint list

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
Super User
Super User

Re: How to: html table in email to SharePoint list

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



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

Proud to be a Flownaut!


Highlighted
Community Support Team
Community Support Team

Re: How to: html table in email to SharePoint list

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

JurajB
Level: Powered On

Re: How to: html table in email to SharePoint list

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!

JurajB
Level: Powered On

Re: How to: html table in email to SharePoint list

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
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (5,385)