PowerShell sql query to CSV to Excel Workbook

Question

-Apologies for the back and forth question!

I pieced together the following PowerShell script which runs two SQL queries, exports each query to a CSV file then moves the CSV files into an Excel workbook.

The code works as expected when the two CSV files are already created. But the script fails when it is run the first time when the CSV files get created.

Function Run-Query {
 param([string[]]$queries,[string[]]$sheetnames,[string[]]$filenames)
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = 0
$dest = $Excel.Workbooks.Add(1)
 for ($i = 0; $i -lt $queries.Count; $i++){
 $query = $queries[$i]
 $sheetname = $sheetnames[$i]
 $filename = $filenames[$i]
### SQL query results sent to Excel
$SQLServer = 'Server'
$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 '$Table2':
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] | Export-Csv -NoTypeInformation -Path "C:\Scripts\Organize\ExcelStuff\$sheetname.csv"
}#End For.
#Begin excel test, loop over each CSV.
 $loopy = (Resolve-Path $filename).ProviderPath
$Book = $Excel.Workbooks.Open($loopy)
 foreach ($item in $loopy){
 $next = $Excel.workbooks.Open($item)
 $next.ActiveSheet.Move($dest.ActiveSheet)
 $xlsRng = $dest.ActiveSheet.UsedRange
 $xlsRng.EntireColumn.AutoFit() | Out-Null
}# END ForEach
#$Excel.Visible = 1 #For debugging.
$dest.sheets.item('Sheet1').Delete()
$xlsFile = "C:\Scripts\MonthlyReboots.xlsx"
$Excel.ActiveWorkbook.SaveAs($xlsFile) | Out-Null
$Excel.Quit()
 While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsRng)) {'cleanup xlsRng'}
 While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($next)) {'cleanup xlsSh'}
 While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($Book)) {'cleanup xlsWb'}
 While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)) {'cleanup xlsObj'}
[gc]::collect() | Out-Null
[gc]::WaitForPendingFinalizers() | Out-Null
}#End Function
$queries = @()
$queries += @'
'@
$queries += @'
'@
$sheetnames = @('Cert','Prod')
$filenames = @(".\prod.csv", ".\cert.csv")
Run-Query -queries $queries -sheetnames $sheetnames -filenames $filenames

Answer 1

Ok, we've got a few lessons to work with here I think. First, functions, what they should do, and what they shouldn't do, and structure. Later we'll touch on organizing your script so that it runs a bit more optimally.

So let's look at that massive function you've got there. That's a lot of stuff in there, and I'm willing to bet that it probably shouldn't all be in there. What is in there will benefit from using the Begin, Process, and End scriptblock sections. For the time being, we're going to ignore Excel, and have the function actually just work with your SQL queries. Right now your function (remember, ignoring Excel for the time being) takes a collection of strings for queries, connects to the SQL server, runs a query, disconnects from the server, reconnects to the server, runs a query, disconnects from the server, and keeps doing that until it runs out of queries. I think a better option would be to use the Begin scriptblock to connect to the server once, then the Process scriptblock to run each query, and the End block to close the connection and return the query results. That stops us from having to open and close the connection a bunch, and keeps the function focused on doing one thing, but doing it well.

Function Run-Query {
param([string[]]$queries)

Begin{
    $SQLServer = 'Server'
    $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"
}

Process{
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.CommandText = $queries
    $SqlCmd.Connection = $SqlConnection
    ## - Extract and build the SQL data object '$Table2':
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $DataSet.Tables[0]
}

End{
    $SqlConnection.Close()
}
}#End Run-Query Function

That will put out an array of objects for however many queries you feed it. So then we just assign a variable to that and we have two datasets in an array. That part is simple:

#Define Queries
$Queries = @()
$Queries += @'
Select * From TableA;
Where Stuff = 'Cert'
'@
$Queries += @'
Select * From TableB;
Where Stuff = 'Prod'
'@

#Get data from SQL
$Data = Run-Query -queries $Queries

Now that we have our datasets we will launch Excel, create a new workbook, name the sheet it starts with, make a second sheet and name that, then just paste the data directly into Excel. There is no reason to export to CSV files, load them into Excel, and copy the data around within Excel when we can just paste the data directly into Excel.

#Launch Excel and add a workbook
$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Add()

#Set the current worksheet at Cert, and add a new one as Prod, then name them appropriately
$Cert = $Workbook.ActiveSheet
$Prod = $Workbook.Worksheets.Add()
$Cert.Name = 'Cert'
$Prod.Name = 'Prod'

#Copy the data from the first query to the clipboard as a tab delimited CSV, then paste it into the Cert sheet
$Data[0] | ConvertTo-Csv -notype -Delimiter "`t" | Clip
[Void]$Cert.Cells.Item(1).PasteSpecial()
#Do the same with the second query and paste it into the Prod sheet
$Data[1] | ConvertTo-Csv -notype -Delimiter "`t" | Clip
[Void]$Prod.Cells.Item(1).PasteSpecial()

You should now have an open workbook with two sheets, each containing the results of one SQL query. Now to just perform the autofit to make it look nice, save the workbook, close it, exit Excel, and perform garbage collection...

#Autofit the columns to make it all look nice
$Prod.UsedRange.EntireColumn.AutoFit()
$Cert.UsedRange.EntireColumn.AutoFit()

#Save the workbook
$Workbook.SaveAs("C:\Scripts\MonthlyReboots.xlsx")

#Close the worbook, and Excel
$Workbook.Close()
$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)|Out-Null
[gc]::collect() | Out-Null
[gc]::WaitForPendingFinalizers() | Out-Null

That should do it. No more opening Excel a whole bunch and working with a bunch of files, the SQL connection just gets opened once, and closed once, with queries performed during the session. If the script takes a long time to run at this point I'd be willing to bet it's the SQL queries that are taking the bulk of the time because once you have the data out of SQL bringing up Excel, and getting the data into the sheets should be really fast.

Edit: Well, it sounds like you aren't getting back results from all of the queries that you are submitting, so I have restructured the function a little and hopefully this will work better.

Function Run-Query {
param([string[]]$queries)

Begin{
    $SQLServer = 'Server'
    $Database = 'Database'
    $Results = @()
}

Process{
    ## - 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 = $queries
    $SqlCmd.Connection = $SqlConnection
    ## - Extract and build the SQL data object '$Table2':
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $Results += $DataSet.Tables[0]
}

End{
    $SqlConnection.Close()
    $Results
}
}#End Run-Query Function

If it doesn't, you could always go back to your old way of doing things, and instead of outputting to CSV files you can start pasting to Excel directly like I've shown you how to do. That should speed things up at the least. Like, open Excel, run the old function (except take out the part that opens Excel), and have the old function paste into sheets in Excel.

I do wish I had a SQL server I could test against. Everything should have worked as far as I could tell, but obviously didn't work like I had anticipated.

Answer 2

Major thanks given to TheMadTechnician for the guidance on using a function.

Here is what I've cobbled together which does work and it creates an Excel file with two worksheets in under 2 seconds. Additionally, the code correctly cleans up the Excel ComObject I'm boasting here but I'd love to see someone come up with a faster way of accomplising this!

Function Run-Query {
 param([string[]]$queries,[string[]]$sheetnames,[string[]]$filenames)
Begin{
 $SQLServer = 'ServerName'
 $Database = 'DataBase'
 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
 $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $Database; Integrated Security = True"
 $Excel = New-Object -ComObject Excel.Application
 $Excel.Visible = 0
 $dest = $Excel.Workbooks.Add(1)
}#End Begin
Process{
 For($i = 0; $i -lt $queries.Count; $i++){
 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
 $SqlCmd.CommandText = $queries[$i]
 $SqlCmd.Connection = $SqlConnection
 $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
 $SqlAdapter.SelectCommand = $SqlCmd
 $DataSet = New-Object System.Data.DataSet
 $SqlAdapter.Fill($DataSet)
 $DataSet.Tables[0] | Export-Csv -NoTypeInformation -Path "C:\Scripts\$($sheetnames[$i]).csv" -Force
}#end for loop.
 }#End Process
End{
 $SqlConnection.Close()
 #Excel magic test!
 For($i = 0; $i -lt $queries.Count; $i++){
 $loopy = (Resolve-Path -Path $filenames[$i]).ProviderPath
 $Book = $Excel.Workbooks.Open($loopy)
 $next = $Excel.workbooks.Open($loopy)
 $next.ActiveSheet.Move($dest.ActiveSheet)
 $xlsRng = $dest.ActiveSheet.UsedRange
 $xlsRng.EntireColumn.AutoFit() | Out-Null
}
 $dest.sheets.item('Sheet1').Delete()
 $xlsFile = "C:\Scripts\MonthlyReboots.xlsx"
 [void] $Excel.ActiveWorkbook.SaveAs($xlsFile)
 $Excel.Quit()
 While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($xlsRng)) {'cleanup xlsRng'}
 While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($next)) {'cleanup xlsSh'}
 While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($Book)) {'cleanup xlsWb'}
 While ([System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)) {'cleanup xlsObj'}
 [gc]::collect() | Out-Null
 [gc]::WaitForPendingFinalizers() | Out-Null
}#End end block.
}#End function run-query.