cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
adam-WP
Level: Powered On

Table in Email Body to SQL

I regularly get emails form a single source that has an table in the body of the email.  I want to automate the process of taking the table data and populating a database.  

 I am having issue extracting the table data from the email body into a format I can then populate a database with.

 

Can anyone help?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Table in Email Body to SQL

Hi @adam-WP,

 

It seems that you have inserted a Table in the email body.

Do you want to update corresponding field to SQL table? To achieve what you want to do, we will have to extract each field from the email body first.

I have made the following test, please take it for a reference.

The trigger is When an email arrives. Of course, there is a table inserted in the body. 

1.PNG

Then convert Html to text.

Add Compoe2 to get the length of the body:

length(body('Html_to_text'))

Add Compose3, use the following code to get the ID:

substring(body('Html_to_text'),3,7)

Add Compose4, use the following code to get the start position of Request Type:

add(indexOf(body('Html_to_text'),'Request Type'),12)

Add Compose5, use the code to get the Start position of Description:

indexOf(body('Html_to_text'),'Description')

Add Compose6 to get the Request type:

substring(body('Html_to_text'),outputs('Compose_4'),sub(outputs('Compose_5'),outputs('Compose_4')))

Image for your reference.

1.PNG2.PNG

The same way with other fields. After you got all the fields from the email body, then insert each field into SQL table.

Hope it could be your reference.

 

Best regards,

Mabel

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Super User
Super User

Re: Table in Email Body to SQL

Hi @adam-WP

 

Do you mind posting a screenshot of the table from the email body? You can blur all the content out, it's just to have an idea of what the table looks like and see what parameters can be extracted to populate the database with.

 

Thank you,

Fausto Capellan, Jr





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

Proud to be a Flownaut!


adam-WP
Level: Powered On

Re: Table in Email Body to SQL

ID

12345

DocID Id

9876

Request Type

Standards

Description

Some Description

Comments

Some Comments

Project

1234

 

The above is a visual of the table and below is the code (Outlook save as html)

 

<table class=MsoNormalTable border=1 cellspacing=0 cellpadding=0
 style='mso-cellspacing:0cm;mso-yfti-tbllook:1184;mso-padding-alt:3.0pt 3.0pt 3.0pt 3.0pt'>
 <tr style='mso-yfti-irow:0;mso-yfti-firstrow:yes'>
  <td style='padding:3.0pt 3.0pt 3.0pt 3.0pt'>
  <p class=MsoNormal><span style='mso-fareast-font-family:"Times New Roman"'>ID<oSmiley Tongue></oSmiley Tongue></span></p>
  </td>
  <td style='padding:3.0pt 3.0pt 3.0pt 3.0pt'>
  <p class=MsoNormal><span style='mso-fareast-font-family:"Times New Roman"'><a
  href="somelink">12345</a><oSmiley Tongue></oSmiley Tongue></span></p>
  </td>
 </tr>
 <tr style='mso-yfti-irow:1'>
  <td style='padding:3.0pt 3.0pt 3.0pt 3.0pt'>
  <p class=MsoNormal><span style='mso-fareast-font-family:"Times New Roman"'>DocID
  Id<oSmiley Tongue></oSmiley Tongue></span></p>
  </td>
  <td style='padding:3.0pt 3.0pt 3.0pt 3.0pt'>
  <p class=MsoNormal><span style='mso-fareast-font-family:"Times New Roman"'>9876<oSmiley Tongue></oSmiley Tongue></span></p>
  </td>
 </tr>
 <tr style='mso-yfti-irow:2'>
  <td style='padding:3.0pt 3.0pt 3.0pt 3.0pt'>
  <p class=MsoNormal><span style='mso-fareast-font-family:"Times New Roman"'>Request
  Type<oSmiley Tongue></oSmiley Tongue></span></p>
  </td>
  <td style='padding:3.0pt 3.0pt 3.0pt 3.0pt'>
  <p class=MsoNormal><span style='mso-fareast-font-family:"Times New Roman"'>Standards<oSmiley Tongue></oSmiley Tongue></span></p>
  </td>
 </tr>
 <tr style='mso-yfti-irow:3'>
  <td style='padding:3.0pt 3.0pt 3.0pt 3.0pt'>
  <p class=MsoNormal><span style='mso-fareast-font-family:"Times New Roman"'>Description<oSmiley Tongue></oSmiley Tongue></span></p>
  </td>
  <td style='padding:3.0pt 3.0pt 3.0pt 3.0pt'>
  <p class=MsoNormal><span style='mso-fareast-font-family:"Times New Roman"'>Some Description<oSmiley Tongue></oSmiley Tongue></span></p>
  </td>
 </tr>
 <tr style='mso-yfti-irow:4'>
  <td style='padding:3.0pt 3.0pt 3.0pt 3.0pt'>
  <p class=MsoNormal><span style='mso-fareast-font-family:"Times New Roman"'>Comments<oSmiley Tongue></oSmiley Tongue></span></p>
  </td>
  <td style='padding:3.0pt 3.0pt 3.0pt 3.0pt'>
  <p class=MsoNormal style='margin-bottom:12.0pt'><span style='mso-fareast-font-family:
  "Times New Roman"'>Some Comments<oSmiley Tongue></oSmiley Tongue></span></p>
  </td>
 </tr>
 <tr style='mso-yfti-irow:5;mso-yfti-lastrow:yes'>
  <td style='padding:3.0pt 3.0pt 3.0pt 3.0pt'>
  <p class=MsoNormal><span style='mso-fareast-font-family:"Times New Roman"'>Project<oSmiley Tongue></oSmiley Tongue></span></p>
  </td>
  <td style='padding:3.0pt 3.0pt 3.0pt 3.0pt'>
  <p class=MsoNormal><span style='mso-fareast-font-family:"Times New Roman"'>1234<oSmiley Tongue></oSmiley Tongue></span></p>
  </td>
 </tr>
</table>
Community Support Team
Community Support Team

Re: Table in Email Body to SQL

Hi @adam-WP,

 

It seems that you have inserted a Table in the email body.

Do you want to update corresponding field to SQL table? To achieve what you want to do, we will have to extract each field from the email body first.

I have made the following test, please take it for a reference.

The trigger is When an email arrives. Of course, there is a table inserted in the body. 

1.PNG

Then convert Html to text.

Add Compoe2 to get the length of the body:

length(body('Html_to_text'))

Add Compose3, use the following code to get the ID:

substring(body('Html_to_text'),3,7)

Add Compose4, use the following code to get the start position of Request Type:

add(indexOf(body('Html_to_text'),'Request Type'),12)

Add Compose5, use the code to get the Start position of Description:

indexOf(body('Html_to_text'),'Description')

Add Compose6 to get the Request type:

substring(body('Html_to_text'),outputs('Compose_4'),sub(outputs('Compose_5'),outputs('Compose_4')))

Image for your reference.

1.PNG2.PNG

The same way with other fields. After you got all the fields from the email body, then insert each field into SQL table.

Hope it could be your reference.

 

Best regards,

Mabel

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
adam-WP
Level: Powered On

Re: Table in Email Body to SQL

You are awesome, thank you very much!

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

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

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 Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 145 members 4,543 guests
Please welcome our newest community members: