extract the content from table in email to sharepoint list

[iko_chen]

Notification	FLOC	FLOC Descr.	Short text	Long description	Work order
2200025222	RCS-C-000	RECEIVING,RAW MATERIALS, MIXING, OTHER	热熔胶房，罐区桥架内线缆检查	* 08.09.2020 01:53:54热熔胶房，罐区桥架内线缆检查	310083157
2400027317	RCS-C-000	RECEIVING,RAW MATERIALS, MIXING, OTHER	08:00-16:00 早班电气巡检		330003723

@Django 

[fchopo]

Hello @iko_chen 

Have a look at the following post on how to extract data from an email and parse it: https://365basics.com/microsoft-flow-parse-email-and-extract-information/

Hope it helps!

Ferran

[iko_chen]

Hi @fchopo 

thank you very much, i had get the solution to extract the content from email, the challenge is my current standardize the notification is a html table, so i need to convert this table to sharepoint list.

thank you very much.

br

iko

[Django]

Hi @iko_chen,

If you are able to extract the data --> what do you with it then?

If you want every row of your table to be a newly created item in a SharePoint list:

1. Create an array and append every table row to that array
2. Use the array in Apply to each-loop (https://docs.microsoft.com/en-us/power-automate/apply-to-each)
3. In the loop use the SharePoint Create item action

[iko_chen]

Hi @Django 

yes, i can extract the content with table(standardized short content) by follow steps, it's not a smart way, so i can't use this way for the tables.

thanks!

[Django]

Just tried to explain in a post (took me over an hour and then lost all my text 😒 because the Forum Site timed out and now is blocking me) so I will try to do in parts.

[Django]

Hi @iko_chen ,

Because this can get very messy, very fast --> I always recommend to focus on the weakest link in your chain.

Based on the concept of "Garbage in, Garbage out", my advise would be to change your email content in a way that gives you the most flexibility and less headache. You already found my concept here: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Extract-and-parse-data-from-an-email/td-.... If you could change the content of your email to match keys with values, I think you have the most flexibility.

[Django · Accepted solution]

However... if you absolutely cannot change your email content there may be a way:

My table = 

Read the HTML of your email body to count the number of <TR> tags in the HTML code. In my case an expression like:

length(split(outputs('Compose_BodyHTML'),'<tr>'))​

This gives you the number of pieces of texts/strings separated by.

So my table has 5 rows of which the first row contains only the header but because there is text before the first row we end up with 6 pieces pieces of texts/strings.

We can use this number 6 to loop through every piece of text so we can isolate the data of every row in a Do until loop (do this action until max number is reached) using an expression to extract the text:

last(take(split(outputs('Compose_BodyHTML'),'<tr>'),variables('varNRrowsLooped')))

​This gives you the data of a whole row in a variable number of loops: in my case my second loop (the one with row data in it) would result in the HTML of the whole header row:

<td style="border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.8513in; padding:4pt 4pt 4pt 4pt"><p lang="en-US" style="margin:0in; font-family:Calibri; font-size:11.0pt"><span style="font-weight:bold">Investment Approval Request</span></p></td><td style="border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.2937in; padding:4pt 4pt 4pt 4pt"><p lang="en-US" style="margin:0in; font-family:Calibri; font-size:11.0pt"><span style="font-weight:bold">Investment Approval Step</span></p></td><td style="border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.8076in; padding:4pt 4pt 4pt 4pt"><p lang="en-US" style="margin:0in; font-family:Calibri; font-size:11.0pt"><span style="font-weight:bold">Investment Approval Configuration</span></p></td><td style="border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.7159in; padding:4pt 4pt 4pt 4pt"><p lang="en-US" style="margin:0in; font-family:Calibri; font-size:11.0pt"><span style="font-weight:bold">Investment Approval Log</span></p></td></tr>

Within the loop we also need to extract every cell of the row separately --> we use the same concept with the Do until here but then for every cell looking for the end of the cell with the </td> tag

length(split(outputs('Compose_ItemData'),'</td>'))

This gives you the data of a cell in your row: in my case the first loop would be the HTML of the cell with Investment Approval Request in it:

<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.8513in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:11.0pt\"><span style=\"font-weight:bold\">Investment Approval Request</span></p>

Then we append this data to a new array of celldata within the array of items (rows)

This gives you the chance to determine what you want to do with every row: in my case the new array looks like

[
  {
    "Itemnumber": 2,
    "celldata": [
      {
        "CellData1": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.8513in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:11.0pt\"><span style=\"font-weight:bold\">Investment Approval Request</span></p>"
      },
      {
        "CellData2": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.2937in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:11.0pt\"><span style=\"font-weight:bold\">Investment Approval Step</span></p>"
      },
      {
        "CellData3": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.8076in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:11.0pt\"><span style=\"font-weight:bold\">Investment Approval Configuration</span></p>"
      },
      {
        "CellData4": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.7159in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:11.0pt\"><span style=\"font-weight:bold\">Investment Approval Log</span></p>"
      }
    ]
  },
  {
    "Itemnumber": 3,
    "celldata": [
      {
        "CellData1": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.8513in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:9.0pt\">Purpose:<br>save data of the request</p>"
      },
      {
        "CellData2": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.302in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:9.0pt\">Purpose:</p><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:9.0pt\">save data of approval steps linked to a single request</p>"
      },
      {
        "CellData3": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.8076in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:9.0pt\">Purpose:</p><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:9.0pt\">Save approval step configuration based on properties of the Investment Approval Request</p><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:9.0pt\">&nbsp;</p>"
      },
      {
        "CellData4": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.6201in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:9.0pt\">Purpose:</p><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:9.0pt\">Save important milestones of the process as individual records linked to a single request</p>"
      }
    ]
  },
  {
    "Itemnumber": 4,
    "celldata": [
      {
        "CellData1": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.8708in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri\"><span style=\"font-size:11.0pt\">IA Name<br></span><span style=\"font-size:9.0pt\">(a unique name combination created by Power App during submission so linked entities will have meaningful lookup name)</span></p>"
      },
      {
        "CellData2": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.3048in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:11.0pt\">n:1 lookup with </p><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:9.0pt\"><span style=\"font-weight:bold\">Investment Approval Request</span></p>"
      },
      {
        "CellData3": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.8076in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:11.0pt\">&nbsp;</p>"
      },
      {
        "CellData4": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.4694in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:11.0pt\">n:1 loookup</p>"
      }
    ]
  },
  {
    "Itemnumber": 5,
    "celldata": [
      {
        "CellData1": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.8513in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri\"><span style=\"font-size:11.0pt\">IA Type<br></span><span style=\"font-size:9.0pt\">(a text field to store the types of IA processes) </span></p><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:11.0pt\">&nbsp;</p><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:9.0pt\"><span style=\"background:#FF99CC\">Option set better??</span></p>"
      },
      {
        "CellData2": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.2937in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:11.0pt\">&nbsp;</p>"
      },
      {
        "CellData3": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.8076in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:11.0pt\">&nbsp;</p>"
      },
      {
        "CellData4": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.5006in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:11.0pt\">&nbsp;</p>"
      }
    ]
  },
  {
    "Itemnumber": 6,
    "celldata": [
      {
        "CellData1": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.8513in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:11.0pt\">&nbsp;</p>"
      },
      {
        "CellData2": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.2937in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:11.0pt\">&nbsp;</p>"
      },
      {
        "CellData3": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.8076in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri\"><span style=\"font-size:11.0pt\">IA Configuration<br></span><span style=\"font-size:9.0pt\">(a unique name combination created by Power App during submission so linked entities will have meaningful lookup name)</span></p>"
      },
      {
        "CellData4": "<td style=\"border-style:solid; border-color:#A3A3A3; border-width:1pt; vertical-align:top; width:1.5006in; padding:4pt 4pt 4pt 4pt\"><p lang=\"en-US\" style=\"margin:0in; font-family:Calibri; font-size:11.0pt\">&nbsp;</p>"
      }
    ]
  }
] 

Put this array in an own Apply to each loop and here you can do anything you like with the collected row data.

Like create a SharePoint item for every row or clean up the HTML using the preview action of HTML to text:

[Django]

I realize my explanation is too big / too complex for the site to load it nicely so let me add an example Flow as an attachment.

Make sure that the only/last email in your inbox contains a table and it should work / explain the concept.

[iko_chen]

Hi @Django 

thank you very much, i will try your solution.

thanks for you details instruction!

thanks!

br

iko

[iko_chen]

Hi @Django 

thank you for your solution, i had extracted the HTML table to sharepoint list, thank you very much.

[Omarcin90]

Coul you explain me, how did you do it step by step, please?