Getting query text from SSMS into Excel with PowerShell

Question

Getting query text from SSMS into Excel with PowerShell

Shawn Melton

SSC-Insane

Points: 24685
More actions
September 6, 2015 at 5:13 am

#321002

Comments posted to this topic are about the item Getting query text from SSMS into Excel with PowerShell
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

The title of this is misleading isn't it? Because you're not really getting query results out of SSMS, you're just re-running the query in PS. If you've got a resultset in SSMS that you can't reproduce, say there are blocks you want to preserve that you need to look into later, they may not be there when you run the query again, then this method won't work because you've actually got the results in SSMS and they'll be gone when you query them from PS.

So while this is a good technique for putting results into excel and getting around the multiline problem, it doesn't exactly do what the title of the article says.

Still, nice technique.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 2

KenpoDBA (9/7/2015)
The title of this is misleading isn't it? Because you're not really getting query results out of SSMS, you're just re-running the query in PS. If you've got a resultset in SSMS that you can't reproduce, say there are blocks you want to preserve that you need to look into later, they may not be there when you run the query again, then this method won't work because you've actually got the results in SSMS and they'll be gone when you query them from PS.
So while this is a good technique for putting results into excel and getting around the multiline problem, it doesn't exactly do what the title of the article says.
Still, nice technique.

Hmmm, nice evaluation.

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 3

But I want to make it clear that the article is solid. It just needs a better title.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

jeffrey yao SSCarpal Tunnel Points: 4365 More actions · Answer 4

Actually, the title is misleading in another perspective, it is not "... into Excel..", it is simply into a CSV file.

If it is indeed an Excel file, I'd say you should be able to export to different Excel sheets. 😛

dave.rogers 954 SSC Enthusiast Points: 190 More actions · Answer 5

You could use the Open Source DLL EPPlus to write the output from a SQL query directly to an Excel spreadsheet, e.g.,

$cmdText = "select object_name(i.object_id) as [Table Name], i.name as [Index Name], case when i.type = 1 then 'Clustered' else 'Nonclustered' end as [Index Type], i.fill_factor as [Fill Factor], s.avg_fragmentation_in_percent as Fragmentation, s.page_count as [Page Count]

from sys.dm_db_index_physical_stats(db_id(), null, null, null, null) s

inner join sys.indexes i with (nolock) on i.object_id = s.object_id and i.index_id = s.index_id

where i.name is not null

and s.avg_fragmentation_in_percent > 5

and s.page_count > 1000

order by s.avg_fragmentation_in_percent desc"

$dt = New-Object System.Data.DataTable

$da = New-Object System.Data.SqlClient.SqlDataAdapter $cmdText, "Server=???;Database=???;Trusted_Connection=Yes"

$da.SelectCommand.CommandTimeout = 300

[void]$da.Fill($dt)

$file = New-Object System.IO.FileInfo ("Book1.xlsx")

if (Test-Path $file.FullName) {Remove-Item $file.FullName}

Import-Module ($env:USERPROFILE + "\Documents\WindowsPowerShell\EPPlus.dll")

$package = New-Object OfficeOpenXml.ExcelPackage $file

$sheet = $package.Workbook.Worksheets.Add("Sheet1")

[void]$sheet.Cells["A1"].LoadFromDataTable($dt, $true)

$sheet.Row(1).Style.Font.Bold = $true

$sheet.View.FreezePanes(2, 1)

$sheet.Cells.AutoFitColumns()

$sheet.Column(4).Style.NumberFormat.Format = "#,##0"

$sheet.Column(5).Style.NumberFormat.Format = "##0.00"

$sheet.Column(6).Style.NumberFormat.Format = "#,##0"

[void]$package.Save()

$package.Dispose()

[void][System.Diagnostics.Process]::Start($file.FullName)

dave.rogers 954 SSC Enthusiast Points: 190 More actions · Answer 6

The PowerShell script in the article uses the SQLPS PowerShell module which may not exist on the computer on which you run the script. Modern versions of SQL Server or SSMS will install a SqlServer module instead; and this may not be immediately available for use in a script. It may be necessary (or expedient, at least) to add the following line to the script:

Import-Module SqlServer

djhislop SSC Rookie Points: 37 More actions · Answer 7

In SSMS Query Options you can untick "Retain CR/LF on copy or save" and then the copy will automatically remove the line feeds for you.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 8

dave.rogers 954 - Friday, March 23, 2018 3:26 AM
The PowerShell script in the article uses the SQLPS PowerShell module which may not exist on the computer on which you run the script. Modern versions of SQL Server or SSMS will install a SqlServer module instead; and this may not be immediately available for use in a script. It may be necessary (or expedient, at least) to add the following line to the script:
Import-Module SqlServer

That is incorrect. The sqlserver module is only available via the PowerShell Gallery. If you install SSMS 17.x you will get the sqlps module but none of the media for SQL Server will install the sqlserver module unless your run Install-Module.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

Sue_H SSC Guru Points: 90860 More actions · Answer 9

Shawn Melton - Friday, March 23, 2018 7:42 AM
dave.rogers 954 - Friday, March 23, 2018 3:26 AM
The PowerShell script in the article uses the SQLPS PowerShell module which may not exist on the computer on which you run the script. Modern versions of SQL Server or SSMS will install a SqlServer module instead; and this may not be immediately available for use in a script. It may be necessary (or expedient, at least) to add the following line to the script:
Import-Module SqlServer
That is incorrect. The sqlserver module is only available via the PowerShell Gallery. If you install SSMS 17.x you will get the sqlps module but none of the media for SQL Server will install the sqlserver module unless your run Install-Module.

I believe it depends on versions of SSMS in terms of which module is or isn't installed. SQLServer module used to be included with 16.x versions of SSMS.
SQL Server PowerShell

They discontinued shipping Powershell with SSMS. I can only find the docs listing this as happening with 17.6 but I think it started with 17.4. The documentation states:
The SQL Server PowerShell module is now a separate install through the PowerShell Gallery
Download SQL Server Management Studio (SSMS)

The screenshots and error messages when trying to use Powershell with 17.4 are in this article:
SSMS Version 17.4 no more SQLPS Module

Sue

Robert Sterbal SSChampion Points: 11050 More actions · Answer 10

I wish people would publish the powershell scripts as a list of statements rather than these compound one liners.

412-977-3526 call/text

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 11

Thanks for the script.

Right click inside the query results windows and choose "Save Results As..." will also preserve the formatting when a csv is opened in Excel.