You’re offline. This is a read only version of the page.
Hello everyone!
I'm trying to parse as JSON the following HTML Table which comes from email:
Share
Report<table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="100%" style="width:100.0%; border-collapse:collapse">
<tbody>
<tr>
<td width="185" valign="top" style="width:138.75pt; border-top:solid #E4E4E5 1.0pt; border-left:none; border-bottom:solid #E4E4E5 1.0pt; border-right:none; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value1: </span>
</b>
</p>
</td>
<td valign="top" style="border-top:solid #E4E4E5 1.0pt; border-left:none; border-bottom:solid #E4E4E5 1.0pt; border-right:none; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 1</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value2:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 2</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value3:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">
<a href="https://google.com">Content 3</a>
</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value4:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 4</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value5:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 5</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value6:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 6</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value7:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in"/>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value8:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 8</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value9: </span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 9</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value10:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 10</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value11:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 11</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value12:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in"/>
</tr>
</tbody>
</table>
</td>
</tr>
<tr>
<td style="padding:11.25pt 11.25pt 11.25pt 11.25pt">
<p class="unsub">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Text </span>
<span class="notifyname1">
<span style="font-family:"Arial",sans-serif">Text Text Text Text</span>
</span>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E"> Text, click <a href="mailto:test@test.com">Unsubscribe</a> Text Text Text Text.</span>
</p>
<p class="MsoNormal">
<span style="font-size:9.0pt; font-family:"Arial",sans-serif; color:#C8C9C7">© company. All rights reserved.</span>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E"/>
</p>
</td>
</tr>
</tbody>
</table>
</div>
<div>
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E"> </span>
</p>
</div>
<div>
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Ref:XXXXXXXXX</span>
</p>
</div>
<p class="MsoNormal"> </p>
<p style="margin:5.0pt">
<span style="font-size:7.0pt; color:#737373">Internal Use - Confidential</span>
</p>
<p class="MsoNormal"> </p>
<p style="margin:5.0pt">
<span style="font-size:7.0pt; color:#737373">Internal Use - Confidential</span>
</p>
<p class="MsoNormal"> </p>
<p style="margin:5.0pt">
<span style="font-size:7.0pt; color:#737373">Internal Use - Confidential</span>
</p>
</div>
</body>
</html>
You can paste this HTML on https://www.w3schools.com/html/tryit.asp?filename=tryhtml_default to have a better visualization if necessary.
Here is the format of HTML table:
I tried to follow below articles but no lucky:
https://powerusers.microsoft.com/t5/Building-Flows/Parsing-HTML-table/m-p/791991#M110115
Also tried to follow below videos from @Paulie78 but no lucky:
How to use Power Automate to parse a HTML Table and convert to JSON
https://www.youtube.com/watch?v=IwRKWaTnl3U
Parsing Text from email with Microsoft Power Automate
https://www.youtube.com/watch?v=7tZ6bRtco3Y
I think I'm not getting because the way my table is formatted is different from all tables provided on above samples.
My table has a 'Menu' values on each row instead on the header for each column and I'm guessing this is why I'm not getting the expected results.
I don't know how to transforme the data to JSON, I'm stuck here on the last step:
Image link: https://ibb.co/MD1McXN
Thank you in advanced.
Hello @DeniseDL,
I have figured out my case by doing the following:
1 - After have my JSON, I converted it to string.
2 - After convert JSON to String, I removed the '},{' which was causing multiple objects into the JSON.
3 - Parse the output again to JSON.
convertString
All responses (
Answers (
string(body('Parse_JSON'))replaceKeyComma
replace(string(outputs('convertString')),'},{',',')
Since the HTML code is relatively clean, you can replace the entire flow with just a Select and a Compose using xml() and xpath():
Compose
The HTML
<table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="100%" style="width:100.0%; border-collapse:collapse">
<tbody>
<tr>
<td width="185" valign="top" style="width:138.75pt; border-top:solid #E4E4E5 1.0pt; border-left:none; border-bottom:solid #E4E4E5 1.0pt; border-right:none; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value1: </span>
</b>
</p>
</td>
<td valign="top" style="border-top:solid #E4E4E5 1.0pt; border-left:none; border-bottom:solid #E4E4E5 1.0pt; border-right:none; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 1</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value2:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 2</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value3:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">
<a href="https://google.com">Content 3</a>
</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value4:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 4</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value5:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 5</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value6:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 6</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value7:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in"/>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value8:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 8</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value9: </span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 9</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value10:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 10</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value11:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Content 11</span>
</p>
</td>
</tr>
<tr>
<td width="185" valign="top" style="width:138.75pt; border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in">
<p class="MsoNormal">
<b>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Value12:</span>
</b>
</p>
</td>
<td valign="top" style="border:none; border-bottom:solid #E4E4E5 1.0pt; padding:7.5pt 0in 7.5pt 0in"/>
</tr>
</tbody>
</table>
</td>
</tr>
<tr>
<td style="padding:11.25pt 11.25pt 11.25pt 11.25pt">
<p class="unsub">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Text </span>
<span class="notifyname1">
<span style="font-family:"Arial",sans-serif">Text Text Text Text</span>
</span>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E"> Text, click <a href="mailto:test@test.com">Unsubscribe</a> Text Text Text Text.</span>
</p>
<p class="MsoNormal">
<span style="font-size:9.0pt; font-family:"Arial",sans-serif; color:#C8C9C7">© company. All rights reserved.</span>
<span style="font-family:"Arial",sans-serif; color:#0E0E0E"/>
</p>
</td>
</tr>
</tbody>
</table>
</div>
<div>
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E"> </span>
</p>
</div>
<div>
<p class="MsoNormal">
<span style="font-family:"Arial",sans-serif; color:#0E0E0E">Ref:XXXXXXXXX</span>
</p>
</div>
<p class="MsoNormal"> </p>
<p style="margin:5.0pt">
<span style="font-size:7.0pt; color:#737373">Internal Use - Confidential</span>
</p>
<p class="MsoNormal"> </p>
<p style="margin:5.0pt">
<span style="font-size:7.0pt; color:#737373">Internal Use - Confidential</span>
</p>
<p class="MsoNormal"> </p>
<p style="margin:5.0pt">
<span style="font-size:7.0pt; color:#737373">Internal Use - Confidential</span>
</p>
</div>
</body>
</html>
Select
From
xpath(
xml(
replace(
slice(
outputs('Compose'),
indexOf(outputs('Compose'), '<table'),
add(
indexOf(outputs('Compose'), '</table>'),
8
)
),
' ',
' '
)
),
'//tr'
)
Map key
trim(
xpath(
item(),
'string(//td[1])'
)
)
Map value
trim(
xpath(
item(),
'string(//td[2])'
)
)
Compose 2
first(
json(
replace(
string(body('Select')),
'},{',
','
)
)
)
Result:
{
"Value1:": "Content 1",
"Value2:": "Content 2",
"Value3:": "Content 3",
"Value4:": "Content 4",
"Value5:": "Content 5",
"Value6:": "Content 6",
"Value7:": "",
"Value8:": "Content 8",
"Value9:": "Content 9",
"Value10:": "Content 10",
"Value11:": "Content 11",
"Value12:": ""
}
Hi @DeniseDL
Pls write the below expression in "From" parameter of Select action to avoid getting duplicates:
range(0,1)
You could use parse action to parse the output of select action and then use create item action to add the rows in SharePoint list.
If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.
This is amazing! Thank you so much for your help with this. I have been trying to figure this out for a very long time. I am also a beginner. My only concern is that my output seems to be repeated. Is this correct?
Also, would this be where I add in the action to populate my SharePoint List? I'm just so amazed that I lost sight of my end goal😊
Thank you again!
Denise
Hi Denise,
I have a sample for you.
After filter array action, add compose action that stores the heading of all the questions as an array. This is optional as you could hard coded those headers in the key column of Map parameter in select action (next action):
[
"Internal / External",
"Type",
"Barrier",
"Region",
"Feedback / Question",
"Response Requested",
"Email address"
]
Next, add "Select" action and write an expression in the "From" parameter:
range(0,1)
Expression used for Key/Value in Map parameter:
The output body of Select action will give the required JSON object.
Output:
If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.
Hello,
I just came across your solution to this problem and since I have the same or similar problem, I tried it and it got me closer to what I need to do.
My table looks like this:
I have gone through the same video:
How to use Power Automate to parse a HTML Table and convert to JSON
https://www.youtube.com/watch?v=IwRKWaTnl3U
My Filter array output is:
Thank you!
Denise
Hello @ManishSolanki,
There is a way to put all content in the same JSON Object instead multiple objects?
This JSON is getting multiples objects, and since I'm using a condition to filter for specific value to do a specific action based on the value (post message in different teams channels based on different values) I'm not able to get all objects infos.
#1
WarrenBelz
146,660
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
66,004
Most Valuable Professional
