logo标签列表

PowerShell将SQL查询导出为CSV再转换为Excel工作簿

excelpowershellcsv
3
-对于来回问题表示歉意！
我拼凑出了下面的PowerShell脚本，它运行两个SQL查询，将每个查询导出到CSV文件，然后将CSV文件移动到Excel工作簿中。
当两个CSV文件已经创建时，代码按预期工作。但是，在第一次创建CSV文件时运行脚本会失败。
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
1
CSV文件没有工作表，因此您不能为每个查询拥有1个工作表。您是想要一个XLS文件吗？ - TheMadTechnician
这是我一直得到的答案，但我无法解释为什么当使用Excel打开CSV文件时，我可以创建一个新的工作表。VBA：Sheets.Add After:=Sheets(Sheets.Count) 我已经编写了代码将多个SQL查询输出到Excel中，每个查询对应一个工作表，但需要15分钟才能完成。 - user4317867
CSV是逗号分隔值文件，字面上就是每个项目之间用逗号分隔的文本。它没有支持多个工作表的结构。您可以在Excel中打开它并添加一个工作表，但是您不能将其保存为CSV，否则除了一个工作表外，所有内容都会丢失。 - TheMadTechnician
你的所有SQL查询结果是否具有相同的列？如果是这样，您可以将结果导出到Export-CSV并使用-Append参数，这将生成一个包含所有结果的大型CSV文件。这可能比使用Excel com对象更快。 - TheMadTechnician
糟糕！事情太美好了，但当我尝试将CSV保存为带有两个工作表的CSV时，结果只得到了一个工作表的CSV。我会编辑问题。 - user4317867
显示剩余3条评论
2个回答
2

好的，我想我们有几个课程要学习。首先是函数，它们应该做什么，以及不应该做什么，还有结构。稍后我们将涉及如何组织您的脚本，使其运行更加高效。

现在让我们来看看您有的那个庞大的函数。里面有很多东西，我敢打赌它们可能都不该在那里。使用Begin、Process和End脚本块部分可以受益于里面的内容。暂时忽略Excel，让函数实际上只与您的SQL查询一起工作。现在您的函数（记住，暂时忽略Excel）采用字符串集合进行查询，连接到SQL服务器，运行查询，断开与服务器的连接，重新连接到服务器，再次运行查询，断开与服务器的连接，并一直这样做，直到查询用完为止。我认为更好的选择是使用Begin脚本块一次连接服务器，然后使用Process脚本块运行每个查询，最后使用End块关闭连接并返回查询结果。这样我们就不必多次打开和关闭连接，保持函数专注于做一件事情，但做得很好。

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

这将针对您提供的所有查询生成一系列对象。然后，我们只需将变量分配给该数组，就可以得到两个数据集。这部分很简单：

#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

现在我们已经有了数据集，我们将启动Excel，创建一个新的工作簿，命名第一个工作表，再创建第二个工作表并命名它，然后直接将数据粘贴到Excel中。没有必要将数据导出为CSV文件，加载到Excel中，并在Excel中复制数据，因为我们可以直接将数据粘贴到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()

您现在应该有一个包含两个工作表的工作簿，每个工作表都包含一个SQL查询结果。现在只需要执行自动调整以使其看起来更漂亮，保存工作簿，关闭它，退出Excel并执行垃圾回收即可...

#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

这样就可以了。不再需要打开一堆Excel文件并处理大量文件，SQL连接只会开启一次，并在会话期间执行查询后关闭。如果此时脚本运行时间很长，我肯定是因为SQL查询占用了大部分时间，因为一旦从SQL中获取数据，将其放入工作表中应该非常快速。

编辑： 好像您没有从提交的所有查询中获取结果，因此我稍微重构了函数，希望这次能更好地运行。

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

如果不行的话，你可以回到以前的做法，不再输出CSV文件，而是像我教你的那样直接粘贴到Excel中。这至少可以加快速度。比如打开Excel，运行旧函数（除了打开Excel的部分），然后让旧函数将内容粘贴到Excel表格中。
我希望我有一个SQL服务器可以进行测试。据我所知，一切都应该能够正常工作，但显然并没有像我预期的那样工作。
非常感谢！我正在处理这个问题。看起来两个查询的结果都没有被写入工作簿。实际上，我只在“Prod”工作表中从“Cert”查询得到了一个结果。 - user4317867
你能否验证$Data中是否有多组数据？不幸的是，我现在没有SQL服务器来运行查询以测试该部分。 - TheMadTechnician
如果我运行$Data | GM，我会得到TypeName: System.Int32，然后它变成了TypeName: System.Data.DataRow。 - user4317867
1

非常感谢TheMadTechnician对使用函数的指导。

以下是我拼凑出来的代码，它确实能在不到2秒的时间内创建一个具有两个工作表的Excel文件。此外，该代码正确地清理了Excel中的ComObject。我很自豪这个成就，但我希望看到有人能够想出更快的方法！

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.
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接