cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

How to make multiple lines of text in SP appear as separate entries in their own columns from a flow

Hi All, 

 

I have built a flow that triggers when I receive an email from a certain mailbox. The flow saves the body of the email as a multiple line of text field. The body of the email is autogenerated and follows the following format:

 

Name:

Account Number:

Email Address:

Date:

 

I want to be able to split these fields in the body of the email to appear in separate columns of the SharePoint list. Is this possible?

 

Thanks you in advance for your help.

Ian

 

1 ACCEPTED SOLUTION

Accepted Solutions

The HTML source I got from you 😆 But I just put what you provided into a "Compose" step to simulate your scenario. I trigged the flow manually.

 

So yours would be like:

  1. When an email arrives
  2. HTML to Text (Using the body of the email from the "When email arrives" step).
  3. Compile the data as per the flow I gave you.
  4. Create your item, augmenting it with the output from the parse JSON step.

I would build it into your current flow. It shouldn't be difficult to modify.

View solution in original post

14 REPLIES 14
Paulie78
Super User
Super User

Yes it is possible. I do this often and just trying to help someone else in another thread doing a similar thing. The best thing is for you to post the output of the email into the "Code Sample" on here, then I or someone else can help you build the expression.

Anonymous
Not applicable

Hi Paulie, 

 

Thanks for your response. Stupid question but is the "Code Sample" the html output of the body? If so please find below. Apologies if this is not what is required, Let me know and I can provide the correct information. Thanks a lot for your help. 

 

<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="Generator" content="Microsoft Word 15 (filtered medium)"><style>

<!--

@font-face

              {font-family:Helvetica}

@font-face

              {font-family:"Cambria Math"}

@font-face

              {font-family:Calibri}

p.MsoNormal, li.MsoNormal, div.MsoNormal

              {margin:0cm;

              margin-bottom:.0001pt;

              font-size:11.0pt;

              font-family:"Calibri",sans-serif}

a:link, span.MsoHyperlink

              {color:#0563C1;

              text-decoration:underline}

a:visited, span.MsoHyperlinkFollowed

              {color:#954F72;

              text-decoration:underline}

p

              {margin-right:0cm;

              margin-left:0cm;

              font-size:12.0pt;

              font-family:"Times New Roman",serif}

span.EmailStyle17

              {font-family:"Calibri",sans-serif;

              color:windowtext}

.MsoChpDefault

              {font-family:"Calibri",sans-serif}

@page WordSection1

              {margin:72.0pt 72.0pt 72.0pt 72.0pt}

div.WordSection1

              {}

-->

</style></head><body lang="EN-IE" link="#0563C1" vlink="#954F72"><div class="WordSection1"><p style="margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; background:white"><strong><span style="font-size:11.5pt; font-family:&quot;Helvetica&quot;,sans-serif; color:black; font-weight:normal">Hello,</span></strong></p><p style="margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; background:white"><strong><span style="font-size:11.5pt; font-family:&quot;Helvetica&quot;,sans-serif; color:black; font-weight:normal">A person has requested to be contacted.</span></strong></p><p style="margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; background:white"><strong><span style="font-size:11.5pt; font-family:&quot;Helvetica&quot;,sans-serif; color:black; font-weight:normal">Information:</span></strong></p><p style="margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; background:white"><strong><span style="font-size:11.5pt; font-family:&quot;Helvetica&quot;,sans-serif; color:black; font-weight:normal">Name: Joe Bloggs</span></strong><b><span style="font-size:11.5pt; font-family:&quot;Helvetica&quot;,sans-serif; color:black"></span></b></p><p style="margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; background:white; box-sizing:inherit; font-variant-ligatures:normal; font-variant-caps:normal; orphans:2; text-align:start; widows:2; text-decoration-style:initial; text-decoration-color:initial; word-spacing:0px"><strong><span style="font-size:11.5pt; font-family:&quot;Helvetica&quot;,sans-serif; color:black; font-weight:normal">Account Number: 100</span></strong><b><span style="font-size:11.5pt; font-family:&quot;Helvetica&quot;,sans-serif; color:black"></span></b></p><p style="margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; background:white; box-sizing:inherit; font-variant-ligatures:normal; font-variant-caps:normal; orphans:2; text-align:start; widows:2; text-decoration-style:initial; text-decoration-color:initial; word-spacing:0px"><strong><span style="font-size:11.5pt; font-family:&quot;Helvetica&quot;,sans-serif; color:black; font-weight:normal">Email Address: joebloggs@gmail.com</span></strong><b><span style="font-size:11.5pt; font-family:&quot;Helvetica&quot;,sans-serif; color:black"></span></b></p><p style="margin-right:0cm; margin-bottom:0cm; margin-left:0cm; margin-bottom:.0001pt; background:white; box-sizing:inherit; font-variant-ligatures:normal; font-variant-caps:normal; orphans:2; text-align:start; widows:2; text-decoration-style:initial; text-decoration-color:initial; word-spacing:0px"><strong><span style="font-size:11.5pt; font-family:&quot;Helvetica&quot;,sans-serif; color:black; font-weight:normal">Date: 02/11/2020</span></strong><b><span style="font-size:11.5pt; font-family:&quot;Helvetica&quot;,sans-serif; color:black"></span></b></p><p class="MsoNormal">&nbsp;</p><p class="MsoNormal">&nbsp;</p><br><p class="msipfooter9ab28e29" align="Left" style="margin:0"><span style="font-size:7.0pt; font-family:Calibri; color:#000000">C2 General</span></p></div></body></html>

That is the output I meant. But I also meant for you to use this button:

codeSample.PNG

Because the HTML output, which is key to making what you want has to be accurate for us to be able to make the correct expression and pasting it in the normal window changes it a bit. Can you paste it to https://pastebin.com/ and then share the link, then it will be exactly right.

Anonymous
Not applicable

Thanks Paulie, 

 

Please find link here: https://pastebin.com/QAzcdAMh

You can do it like this: https://ibb.co/J5kDHx7

GetEmailData.png

Explanation:

  • HTML is converted to text
  • Create a variable that is just a linefeed
  • Split the text on a linefeed into an array:

 

split(outputs('Html_to_text')?['body'], variables('lineFeed'))​

 

  • Filter the resulting array to remove all blank lines.
  • Create a JSON with the output from the filter:

 

{
 "Name": "@{trim(substring(body('Filter_array')[3],5))}",
 "AccNo":"@{trim(substring(body('Filter_array')[4],15))}",
 "email":"@{trim(substring(body('Filter_array')[5],14))}",
 "Date":"@{trim(substring(body('Filter_array')[6],6))}"
}​

 

 

  • Add a parse JSON step to make the values easier to access.

That should be it!

 

Please...

If I answered your question Accept it as a solution ✔️ 

If I helped you out, please give me some Kudos 👍

Thanks 😙

 

Anonymous
Not applicable

Hi Paulie, 

 

Thank you this is very helpful. Sorry for the stupid questions but where are you getting the HTML source and what is your trigger? Do I build this into my current flow or should I make it a separate flow? My current flow is simply When an email arrives > Create Item

The HTML source I got from you 😆 But I just put what you provided into a "Compose" step to simulate your scenario. I trigged the flow manually.

 

So yours would be like:

  1. When an email arrives
  2. HTML to Text (Using the body of the email from the "When email arrives" step).
  3. Compile the data as per the flow I gave you.
  4. Create your item, augmenting it with the output from the parse JSON step.

I would build it into your current flow. It shouldn't be difficult to modify.

Anonymous
Not applicable

Perfect, Thanks for all your help and timely responses. I t was very helpful. 

Anonymous
Not applicable

Hi Paulie, 

 

I am getting the following error message. Do you know how this can be resolved?

 

Thanks

Ian

 

https://ibb.co/Q8dGyBP

I can certainly help you figure it out. That expression is trying to access the 3rd element of the array. Which is the first line of data that you are trying to access. So it suggests that the array has not been created. Go into the run history and see what the output is from the filter array action, that will show you what the state of the array is. Based on that, we can find a solution.

Anonymous
Not applicable

Hi Paul, 

 

Thanks again for your response. You are correct the output of the filter array is blank. This is the input: 

 

[
  "Hello,\n\nA person has requested to be contacted.\n\nInformation:\n\nName: Joe Bloggs\n\nAccount Number: 100\n\nEmail Address: joebloggs@gmail.com\n\nDate: 02/11/2020\n\n\n\n\n\n\n\n\nC2 General"
]
This is obviously also the output of "Create an Array of Lines". 
Would you be able to advise me on a solution?
Thanks
Ian

Yes, it seems to me that the "split" has not happened at the creation of the array. All of the newline characters are still present. Which makes me think that you did not put a line feed in this step:

linefeed.png

You need to literally put your cursor in the Value field and press the enter key. Then it should split on every new line.

 

Try changing your split expression to the following, it is a bit neater:

split(outputs('Compose'), base64ToString('Cg=='))
Anonymous
Not applicable

Hi Paul, 

 

I made the change you mentioned and I hit enter in the value field. The output from the filter array is the same as the input and is as follows:

[
  "Hello,\n\nA person has requested to be contacted.\n\nInformation:\n\nName: Joe Bloggs\n\nAccount Number: 100\n\nEmail Address: joebloggs@gmail.com\n\nDate: 02/11/2020",
  "\nC2 General"
]
 
However I am still getting the same error message: https://ibb.co/5R6j62f
 
Do you know why this might be? Please see full flow for your reference. 
Thanks again

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (3,288)