63306

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.

<pre class="lang-psh prettyprint-override">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

Answer1:

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.

<strong>Edit:</strong> 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.

Answer2:

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!

<pre class="lang-psh prettyprint-override">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.

Recommend

  • PowerShell sql query to CSV to Excel Workbook
  • How to copy excel columns from one file to another?
  • Changing path to parent directory of active working folder
  • Bootstrap datetimepicker calendar is not visible
  • Locating largest value and return it's heading from table using excel vba
  • Excel VBA: Select the column next to the column with data and then insert 3 columns
  • .net c# excel column AutoFit
  • Formula Application Defined or Object Defined Error
  • F# Excel UsedRange is not Defined
  • Showing hidden column in another sheet
  • Excel VBA Macro--Search For Column names and then copy into defined columns on another template work
  • How to search data from two different sheets and copy them to a third sheet?
  • can't set PageSetup.Orientation = xlLandscape from MS Project
  • MATCH() function in VBA
  • IronPython - Run an Excel Macro
  • Excel Range in C# using interop.Excel. Range both empty and not empty?
  • Postgresql “no pg_hba.conf entry” error
  • Insert Columns between columns in excel
  • How to delete column from range if cell contains specific value in VBA/Excel
  • ASP.NET MVC2 Error: No parameterless constructor defined for this object
  • Extract All Possible Paths from Expression-Tree and evaluate them to hold TRUE
  • XSLT foreach repeating nodes to flat
  • How to use jQuery's $.post() method with async/await and typescript
  • Overlapping controls in Windows XP
  • List images(01.png) and descriptions(01.txt) from directory
  • Setting up SourceTree to merge unity3d scenes with UnityYAMLMerge
  • D3 nodes and links from JSON with nested arrays of children
  • Unity3D & Android: Difference between “UnityMain” and “main” threads?
  • Sails.js/waterline: Executing waterline queries in toJSON function of a model?
  • Can Jackson SerializationFeature be overridden per field or class?
  • How to redirect a user to a different server and include HTTP basic authentication credentials?
  • Can I make an Android app that runs a web view in Chrome 39?
  • VBA Convert delimiter text file to Excel
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • NSLayoutConstraint that would pin a view to the bottom edge of a superview
  • LevelDB C iterator
  • Linking SubReports Without LinkChild/LinkMaster
  • How to push additional view controllers onto NavigationController but keep the TabBar?