Getting query text from SSMS into Excel with PowerShell

  • Shawn Melton

    SSC-Insane

    Points: 24554

    Comments posted to this topic are about the item Getting query text from SSMS into Excel with PowerShell

    Shawn Melton
    Twitter: @wsmelton
    Blog: blog.wsmelton.info

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    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

    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: 9801

    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: 4244

    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: 183

    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: 183

    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

    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: 24554

    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: blog.wsmelton.info

  • Sue_H

    SSC Guru

    Points: 89425

    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: 10818

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

    412-977-3526

  • PHXHoward

    SSCrazy Eights

    Points: 8221

    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.

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

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