David Steimle: Accessing JSON Data and Powershell Objects within Strings

Yesterday we discussed getting JSON data from a SqLite3 database.

Using that data in Powershell means turning it into an object. Referencing it often means working with strings.

First, lets get our data back, and then convert it to an object

PS A:\> $MyData = Get-Content $TempCsv | ConvertFrom-Csv | ConvertTo-Json
PS A:\> $MyJson = $MyData | ConvertFrom-Json
PS A:\> $MyJson

name            twitter        url
----            -------        ---
David           dbsteimle      rhymeswithtimely.blogspot.com
Commander Candy codingComander codingcommanders.com

I want to turn this data into usable HTML. I could use the ConvertTo-HTML command:

PS A:\> $MyJson | ConvertTo-Html
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"

  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>HTML TABLE</title>
</head><body>
<table>
<colgroup><col/><col/><col/></colgroup>
<tr><th>name</th><th>twitter</th><th>url</th></tr>
<tr><td>David</td><td>dbsteimle</td><td>rhymeswithtimely.blogspot.com</td></tr>
<tr><td>Commander Candy</td><td>codingComander</td><td>codingcommanders.com</td></tr>
</table>
</body></html>

Which results in:

name	twitter	url
David	dbsteimle	rhymeswithtimely.blogspot.com
Commander Candy	codingComander	codingcommanders.com

Some of that data is for link generation, though. My users shouldn't have to cut and paste a URL from an HTML document. Instead, let's treat Powershell like it were PHP, and loop through our object and create a proper table.

PS A:\> $OutPut = $null
PS A:\> $Output += "<table>`n<tr>`n<th>Name</th>`n<th>Twitter</th>`n<th>URL</th>`n</tr>`n"
PS A:\>
PS A:\> foreach($Record in $MyJson){
>> $OutPut += "<tr>`n"
>> $OutPut += "<th>$($Record.name)</th>`n"
>> $Output += "<td><a href='https://twitter.com/$($Record.twitter)'>@$($Record.twitter)</a></td>`n"
>> $Output += "<td><a href='http://$($Record.url)'>$($Record.url)</a></td>`n"
>> $OutPut += "</tr>`n"
>> }
PS A:\> $OutPut += "</table>"
PS A:\> $OutPut
<table>
<tr>
<th>Name</th>
<th>Twitter</th>
<th>URL</th>
</tr>
<tr>
<th>David</th>
<th><a href='https://twitter.com/dbsteimle'>@dbsteimle</a></th>
<th><a href='http://rhymeswithtimely.blogspot.com'>rhymeswithtimely.blogspot.com</a></th>
</tr>
<tr>
<th>Commander Candy</th>
<th><a href='https://twitter.com/codingComander'>@codingComander</a></th>
<th><a href='http://codingcommanders.com'>codingcommanders.com</a></th>
</tr>
</table>

Since $Record has properties we want to reference withing a string, we need to isolate it, as in $($Record.name) to allow the .name to parse properly.

Now, our resultant table has both twitter and webpage links, making the HTML more useful for our readers.

Name	Twitter	URL
David	@dbsteimle	rhymeswithtimely.blogspot.com
Commander Candy	@codingComander	codingcommanders.com

David Steimle

Friday, March 29, 2019

Accessing JSON Data and Powershell Objects within Strings

No comments: