PowerShell sql查询到CSV到Excel Workbook [英] PowerShell sql query to CSV to Excel Workbook

问题描述

- 来回问题的问题！

-Apologies for the back and forth question!

我拼凑了以下PowerShell脚本，它运行两个SQL查询，将每个查询导出到CSV文件，然后将CSV文件导入Excel工作簿。

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.

当两个CSV文件已创建时，代码正常工作。但是，当第一次创建CSV文件时，脚本运行失败。

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

推荐答案

主要感谢TheMadTechnician对于使用函数的指导。

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

这里是我拼凑在一起的工作，它创建一个Excel文件有两个工作表在2秒以下。此外，代码正确地清理了Excel ComObject 我在这里拥有，但我很想看到有人提出了一个更快的方式来实现这一点！

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.

这篇关于PowerShell sql查询到CSV到Excel Workbook的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！