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
}