Powershell Multiple SQL queries into Excel workbook

Question

Firstly, I do apologize if this is a duplicate question. I searched and searched but haven't found anything which addresses the problem I'm stuck on.

My goal is taking 7 Powershell scripts that output a CSV from a SQL query into 1 Excel workbook, each CSV on a different worksheet.

I located this https://social.technet.microsoft.com/Forums/scriptcenter/en-US/ef70e191-1b2e-4ba8-8845-58c4b1159ab7/multiple-csvs-into-multiple-sheets-of-an-excel which works however the Excel workbook result has the First column as the last column.

function Export-Excel{
[cmdletBinding()]
Param([Parameter(ValueFromPipeline=$true)]
[string]$junk)

begin{
    $header=$null
    $row=1
}
process{
    if(!$header){
        $i=0
        $header=$_ | Get-Member -MemberType NoteProperty | select name
        $header | %{$Global:ws.cells.item(1,++$i)=$_.Name}
    }
    $i=0
    ++$row
    foreach($field in $header){
        $Global:ws.cells.item($row,++$i)=$($_."$($field.Name)")
    }
}

}

$xl=New-Object -ComObject Excel.Application
$wb=$xl.WorkBooks.add(1)
$Global:ws=$wb.WorkSheets.item(1)
$Global:ws.Name='Sunday'
import-csv 'C:\Sunday.csv' | Export-Excel

Because if I had SQL installed, I could use the invoke-sqlcmd cmdlet and the point of Powershell is to provide automation without using the GUI. — user4317867
I should clarify. I'm trying to automate a procedure from MS Access which queries an SQL DB over ODBC exporting to MS Excel. Some manual formatting was required. With Powershell I can skip most of those steps in an easy to run script. — user4317867

user4317867 user4317867 · Accepted Answer · 2015-02-25T23:42:14

Ended up working out the following. If there is a more efficient method, please let me know. SQL results sent to Excel to take advantage of EntireColumn.AutoFit() and have each SQL query result on it's own worksheet.

$docs = "C:\Scripts\Output.xlsx"
If (Test-Path $docs){Remove-Item $docs}
Function Run-Query {
 param([string[]]$queries,[string[]]$weekdays)
## - Create an Excel Application instance:
$xlsObj = New-Object -ComObject Excel.Application
$xlsObj.DisplayAlerts = $false
$xlsWb = $xlsobj.Workbooks.Add(1)
## - Create new Workbook and Sheet (Visible = 1 / 0 not visible)
$xlsObj.Visible = 0
$time = 7
for ($i = 0; $i -lt $queries.Count; $i++){
 $percentage = $i / $time
 $remaining = New-TimeSpan -Seconds ($time - $i)
 $message = "{0:p0} complete" -f $percentage, $remaining    
Write-Progress -Activity "Creating Daily Reboot Spreadsheet" -status $message -PercentComplete ($percentage * 100)
 $query = $queries[$i]
 $weekday = $weekdays[$i]
$xlsSh = $xlsWb.Worksheets.Add([System.Reflection.Missing]::Value, $xlsWb.Worksheets.Item($xlsWb.Worksheets.Count))
$xlsSh.Name = $weekday
### SQL query results sent to Excel
$SQLServer = 'ServerName'
$Database = 'DataBase'
## - Connect to SQL Server using non-SMO class 'System.Data':
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $Database; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $query
$SqlCmd.Connection = $SqlConnection
## - Extract and build the SQL data object '$DataSetTable:
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSetTable= $DataSet.Tables["Table"]
## - Build the Excel column heading:
[Array] $getColumnNames = $DataSetTable.Columns | SELECT ColumnName
## - Build column header:
[Int] $RowHeader = 1
foreach ($ColH in $getColumnNames)
{
$xlsSh.Cells.item(1, $RowHeader).font.bold = $true
$xlsSh.Cells.item(1, $RowHeader) = $ColH.ColumnName
$RowHeader++
}
## - Adding the data start in row 2 column 1:
[Int] $rowData = 2
[Int] $colData = 1
foreach ($rec in $DataSetTable.Rows)
{
foreach ($Coln in $getColumnNames)
{
## - Next line convert cell to be text only:
$xlsSh.Cells.NumberFormat = "@"
## - Populating columns:
$xlsSh.Cells.Item($rowData, $colData) = $rec.$($Coln.ColumnName).ToString()
$ColData++
}
$rowData++; $ColData = 1
}
## - Adjusting columns in the Excel sheet:
$xlsRng = $xlsSH.usedRange
[void] $xlsRng.EntireColumn.AutoFit()
}#End For loop.
#Delete unwanted Sheet1.
$xlsWb.Sheets.Item('Sheet1').Delete()
#Set Monday to Active Sheet upon opening Workbook.
$xlsWb.Sheets.Item('Cert').Activate()
## ---------- Saving file and Terminating Excel Application ---------- ##
$xlsFile = "C:\Scripts\Output.xlsx"
[void] $xlsObj.ActiveWorkbook.SaveAs($xlsFile)
$xlsObj.Quit()
## - End of Script - ##
Start-Sleep -Milliseconds 700
 While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsRng)) {'cleanup xlsRng'}
 While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsSh)) {'cleanup xlsSh'}
 While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsWb)) {'cleanup xlsWb'}
 While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsObj)) {'cleanup xlsObj'}
[gc]::collect() | Out-Null
[gc]::WaitForPendingFinalizers() | Out-Null
}#End Function
 $queries = @()
 $queries += "Query1"
 $queries += "Query2"
 $queries += "Query3"
 $queries += "Query4"
 $queries += "Query5"
 $queries += "Query6"
 $queries += "Query7"
$weekdays = @("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday")
Run-Query -queries $queries -weekdays $weekdays

Powershell Multiple SQL queries into Excel workbook

2 Answers

>