Introduction

I tend to live in both SSMS and Excel most of my day when troubleshooting an issue or looking at the performance of a SQL Server instance. Whether I am pulling out some of the DMV data on the plan cache or pulling out Extended Event data that I captured, I use both tools extensively. In either case I am usually pulling a column that contains an ad-hoc statement or some portion of a procedure call (e.g. sql_text column) into Excel. In the 2005 and 2008 versions of SSMS there was not always an issue getting this to paste from the grid view pane into Excel. Although with SQL Server 2012 and VS Integrated Shell, you have to deal with the possibility that your result will contain (commonly) multiline values, which does wonders when pasting it into Excel.

It really does not matter if you are only dealing with a few records, because that one "sql_text" column could have a procedure that is 5 lines or 100. When it comes to your alterntiaves there is one with T-SQL I have seen but that requires you to change your scripts. The other solution would be to use PowerShell to just draw up a simple script that just replaces any "new line" value and then outputs my data to a CSV.

The Script

This is the base template of the script I use to generate a CSV file of a given query, it does utilize the SQLPS module cmdlet Invoke-Sqlcmd:

$qry = @"
<Copy/Paste the query here>
"@
Invoke-Sqlcmd -ServerInstance <Server> -Database <Database> -Query $qry |
   select <column list>, @{Label="ColumWithCarriageReturn";Expression={$_.ColumnName -replace "`n"," "}} |
      Export-Csv -Path <FilePath> -NoClobber -NoTypeInformation -Delimiter "|" -Force

The breakdown of this toexplain a few things, if you are not completely familar with PowerShell:

1. The variabler $qry is populated with a multiline string (referenced as a Here-String), just copy and paste what you ran in SSMS into that variable.
2. Invoke-Sqlcmd is used to run the query
3. Since we have to specify the one column with the carriage return, you end up having to write out all of them with the select command.
4. The column with the carriage return is re-created as a custom property using a hashtable to specify the label (or column name), and then in the expression you are populating it with the value. In this case we are simply using the -replace operator to find any new line value (`n) and change it to a space.

The Example

The most common place I use this is when I want to get data captured from an XEvent session into Excel for easier reference or presentation to a client. Which again with most records returned it is not a problem, until you hit something like this:

To use the script above I would populate a variable $XEquery with the actual query I use to read from the sys.fn_xe_file_target_read_file DMV and just specify the columns of interest to the select command:

Invoke-Sqlcmd -ServerInstance MANATARMS\SQL08R2 -Database master -Query $XEquery | 
   select event_name,timestamp,activity_id, event_sequence,
    @{label="sql_text";expression={$_.sql_text -replace "`n"," "}} | 
     Export-Csv -Path .\outfile.csv -NoClobber -Delimiter "|" -NoTypeInformation -Force

Import the CSV into Excel and magic:

Try it out for yourself, it can come in handy to have in your arsenal of scripts.