Tech

[PowerShell] How to index each tables in the header

I am working with PowerShell where I am running SQL query and converting the output to Html file.

As I have multiple queries that give a different output. I am able to get output also I am able to convert the output to HTML and get the *.html file but I don’t know how to index each table to the header.

Below is the script:

$file = Get-ChildItem -Path 'C:\Users\xyz\Desktop\MSSQL_HDB\Scripts\'
#$file.count
for($i = $file.count-1 ; $i -ge 0 ; $i-- ){
    $file_name = $file.Name[$i]
    $LOC="C:\Users\xyz\Desktop\MSSQL_HDB\Scripts\$file_name"
    $SQl_QUE = get-content -Path $LOC | Out-String
    $ServerInstanceName = "192.xxx.xx.xx\Instancename"
    $DataBaseName = "master"
    $base_name = (Get-Item $LOC).Basename
    $SqlResults = Invoke-Sqlcmd -ServerInstance $ServerInstanceName -Database $DataBaseName -Query $SQl_QUE -Username "Trainee" -Password "password@123"
$Header = @"
<style>
TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #6495ED;}
TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
</style>
"@
$SqlResults | ConvertTo-Html -Head $Header -Body "<h2>$base_name</h2>" -CssUri "http://www.w3schools.com/lib/w3.css" | Out-File -FilePath C:\Users\xyz\Desktop\status.html -Append
}

This should do the trick :slight_smile:

$AllBcksummary.psobject.properties | select Name
1 Like

Or…

$AllBcksummary[0].psobject.properties | select name

Name
----
Serveur
RG
Environment
Availibility
Coffre
Status
Regle
Dernier Backup
Type
1 Like

You’ll have to output the contents of your table twice. Once to build the index providing just some names for the sections, and again to build the content with the appropriate bookmarks/section headers. You need to create hyperlinks to bookmarks in the index, and the anchors with bookmarks at the start of the appropriate content.

The index will look like this:

HTML

Go To Section 1

Then, at the top of your content section, add HTML like this:

HTML

Section 1

Use whatever formatting you prefer (h1, h2, etc).