Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I wrote about getting a quick archive of SQL Saturday data last week, and while doing that, I had some issues building the HTML needed in PowerShell. I decided to work through this a bit and determine what was wrong.
My original code looked like this:
$folder = "E:\Documents\git\SQLSatArchive\SQLSatArchive\SQLSatArchive\ClientApp\public\Assets\PDF"
$code = ""
$list = Get-ChildItem -Path $folder
ForEach ($File in $list) {
#write-host($File.name)
$code = $code + "<li><a href=$($File.Name)>$($File.BaseName)</a></li>"
}
write-host($code)
This gave me the code I needed, which I then edited in SSMS to get the proper formatting. However, I knew this needed to work.
I
had used single quotes and then added in the slashes, but that didn’t work. This code:
$folder = "E:\Documents\git\SQLSatArchive\SQLSatArchive\SQLSatArchive\ClientApp\public\Assets\PDF"
$code = ""
$list = Get-ChildItem -Path $folder
ForEach ($File in $list) {
#write-host($File.name)
$code = $code + '<li><a href="/Assets/PDF/$($File.Name)" >$($File.BaseName)</a></li>'
}
write-host($code)
produced this type of output:
<li><a href="/Assets/PDF/$($File.Name)" >$($File.BaseName)</a></li>
Not exactly top notch HTML.
I decided that I should look around. I found a post on converting some data to HTML, which wasn’t what I wanted, but it had a clue in there. The double quotes.
I needed to escape quotes here, as I wanted the double quotes around my string. I changed the line building the string to this:
$code = $code + "<li><a href=""/Assets/PDF/$($File.Name)"" >$($File.BaseName)</a></li>"
And I then had what I wanted:
<li><a href="/Assets/PDF/1019.pdf" >1019</a></li>
Strings in PoSh can be funny, so a little attention to escaping things and knowing about variables and double quotes is helpful.
SQLNewBlogger
This was about 15 minutes of messing with Google and PoSh to solve, but then only about 10 minutes to write up.
A good example that shows some research, initiative, and investigation in addition to solving a problem.
Voice of the DBA 
So here is what I did to create HTML documents (emails) from Powershell. First I created a HTML “template” file. This was created using a simple HTML editor that I found on the web as a free download. I created the template placing work tokens like , , etc within the template. I saved this template file off onto a public file share. One thing I had to do was to examine the actual HTML to insure all of my tags were intact. I have found that HTML editors can do some funky things to the actual document.
I had a table that I used to stage the outbound email as well as a column for the HTML email (body). My PS script would read this table, and process it by performing a substitution of the HTML tag in the template with a value read from the database. Once I processed all the tags I would send out the email using the newly formed body from the PS script.
Any changes to the static text within the body or if I need to make any font changes, etc, I edit the template file in the HTML editor.
This process works flawlessly. It is simple and adds a bit more of a finished look to the emails.
LikeLike
Doubling your double quotes works, so does escaping the double quotes with the PowerShell escape character, the backtick ` (U+0060, WordPress’ quotes plugin is going to absolutely mangle all my quotes in this comment, I wish Markdown was supported here. See also @CurlyQuoteFails on Twitter.) Backtick is also how you represent dollar sign `$, tab `t, newline `n, carriage return `r, alert bell `a, backtick “ (doubling it), &c.
Templates are also a good way to go when you’ve reached a certain degree of complexity, though that requires maintaining and locating an external file (or files).
I’ve had the best luck with multiline strings for short snippets of HTML (and other formats, like SQL, Markdown, Graphviz, &c), where you start the string with at-sign quote newline (U+0040, U+0022, optionally a U+000D, then U+000A), then you can type anything at all in the string, including newlines, then end it with the reverse: newline quote at-sign (optionally a U+000D, U+000A, U+0022, U+0040). Reading and maintaining multiline strings is much easier:
$code+= @”
$($File.BaseName)
“@
(Although note that HTML also allows you to use single quotes for attribute values for situations where escaping the quotes makes things less readable.)
(Multiline strings can also use the single quote if you don’t want interpolation of dollar sign $variables and $( expressions ) )
(Also note that PowerShell knew people would be pasting code out of WordPress blogs, and allows you to use curly quotes U+201C and U+201D, maybe others, anywhere you’d use quotes though it doesn’t actually distinguish the left curly quote from the right curly quote.)
LikeLike
Ah, interesting, HTML is supported!
LikeLike