In a previous post, I’ve covered how to import and query HTML table data in SQL Server, so I figured it makes sense to also show ho to export SQL Server query results to an HTML table using PowerShell and dbatools.
This is really similar to how the HTML report from PSBlitz is generated. The differences being that PSBlitz doesn’t rely on dbatools and there’s a bit more formatting and some JavaScript involved.
Environment
You just need dbatools installed, and the example relies on the AdventureWorks2019 database.
Why?
This is a great option for outputting data to a portable format that can be viewed on pretty much anything, and doesn’t really allow for accidental editing.
And, if your reporting requirements aren’t worth setting up something like an SSRS server, you can set up a periodically refreshing dashboard using this, an IIS server and some scheduled tasks or SQL Server Agent jobs.
How to do it
HTML doctype, header and CSS
First, I load the HTML doctype declaration, header related info, CSS (so that we won’t end up with an extremely bland table), and the start of the body tag in a variable to use later one.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | #This acts as both page title and the H1 header $HtmlTabName = "Employees" #Doctype declaration, CSS to handle formatting, head, and opening the body tag $HtmlPre = @" <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <style> body { background-color:#FFFFFF; font-family:Tahoma; font-size:11pt; } table { margin-left: auto; margin-right: auto; border-spacing: 1px; } th { background-color: dodgerblue; color: white; font-weight: bold; padding: 5px; text-align: center; border: 1px solid black; position: sticky; top: 0; z-index: 1; } td { padding: 5px; border: 1px solid black; vertical-align: top; } tr:hover{ background-color: rgba(255, 255, 0, 0.4); } h1 { text-align: center; } </style> <title>$HtmlTabName</title> </head> <body> "@ |
The CSS will tell browsers how the table and its components should be displayed.
Controlling everything from background color to highlighting rows on cursor hover.
Query and columns
This would all be pointless without an actual query to retrieve the desired result set.
So, I load that in a variable too.
| 1 2 3 4 5 6 7 | $Query = @" SELECT [BusinessEntityID], [Title], [FirstName], [MiddleName], [LastName], [Suffix], [JobTitle], [PhoneNumber], [PhoneNumberType], [EmailAddress], [EmailPromotion], [AddressLine1], [AddressLine2], [City], [StateProvinceName], [PostalCode], [CountryRegionName] FROM [HumanResources].[vEmployee]; "@ |
And I also load the list of columns that I want to be exclude from the resulting data set.
Notice how those columns aren’t really part of the above query? I’ll come back to that later.
| 1 | $ExcludeColumns = @("RowError", "RowState", "Table", "ItemArray", "HasErrors") |
Generating the HTML table
Now I can use Invoke-DbaQuery to get the result set and then use Select-Object together with Convert-ToHTML to generate an HTML table.
| 1 2 3 | $htmlTable = Invoke-DbaQuery -SqlInstance localhost\VSQL2019 -Database AdventureWorks2019 -Query $Query | Select-Object -Property * -ExcludeProperty $ExcludeColumns | ConvertTo-Html -As Table -Fragment |
At this point, the $htmlTable variable contains only the HTML table itself.
Putting it all together
All that’s left now is to build the contents of the HTML file and write it to disk.
In order to do that, I join the contents of the $HtmlPre variable (doctype, header, and CSS) with the H1 header, and the table data. I then add a line break and close the tags opened in $HtmlPre.
| 1 2 3 4 5 6 7 | $html = $HtmlPre + @" <h1>$HtmlTabName</h1> $HtmlTable <br> </body> </html> "@ |
Now, to write it to an actual HTML file.
| 1 | $html | Out-File -Encoding utf8 -FilePath F:\Temp\$HtmlTabName.html |
The end result
The resulting HTML file can be viewed in any browser.
It’s all in a nice formatted table in a nice and portable format, which, with a bit of CSS, allows for neat effects such as highlighting the row you’re currently hovering over.
But why did I pipe to Select-Object?
Now, to demo what happens without throwing Select-Objects in the mix.
The command would look like this:
| 1 2 | $htmlTable = Invoke-DbaQuery -SqlInstance localhost\VSQL2019 -Database AdventureWorks2019 -Query $Query | ConvertTo-Html -As Table -Fragment |
And the resulting HTML file would have 5 additional columns that we don’t really want.
Conclusion
Exporting SQL Server query results to HTML using PowerShell is a nifty way to generate “quick and dirty” reports and dashboards, and also a nice little PowerShell exercise to get data professionals more into PowerShell.
And people with a little more HTML and CSS knowledge than I have (I barely have enough to do this demo 🙂 )can make the results look even better.