New Line Issue when copying data from SQL Server 2012 to Excel

Advanced REPLACE: Cover all bases

We have a recurring issue where REPLACE functions as a one-hit wonder. Fighting on multiple fronts can be challenging. Below, we use QUOTENAME to put double quotes around your column data. Excel behaves nicely with QUOTENAME wrapped data, especially when dealing with multi-line text fields.

SELECT 
  QUOTENAME(REPLACE(REPLACE(YourColumn, CHAR(13) + CHAR(10), ''), CHAR(10), ''), '"') AS EnclosedData
FROM 
  YourTable


-- Please, no applause. We just like making you happy. 


Using Excel direct import

Direct import of data into Excel from SQL Server can maintain new line integrity. We love it when all roads lead to Rome. Execute the following steps in Excel - Data > Get External Data > From Other Sources > From SQL Server - and voila! You have your neatly organized data.

Special SSMS settings

The SSMS (SQL Server Management Studio) has a quirky feature. The "Retain CR/LF on copy or save" check box, found at SSMS Tools > Options > Query Results > SQL Server > Results to Grid, is a hidden gem. It's all about managing your expectations with newline characters when copying data from the grid.

-- We weren't kidding about the quirky part! 


Remember, you have to start a new session for the changes to take effect. Also, the preview version of SSMS downloadable at https://msdn.microsoft.com/library/mt238290.aspx includes this fancy feature. No need for the full SQL media. You're welcome!

Mastering Excel's Empty cells

Excel’s empty cells have a knack for delivering the unexpected. These little rascals can sometimes spawn unintended new lines. Use the snippet below if you wish to blissfully ignore Excel's annoying quirks:

SELECT COALESCE(YourColumn, '""') AS ExcelReadyColumn
FROM YourTable


This is where COALESCE steals the show - this function relieves NULL values off their duty by replacing them with two double quotes. Excel sees these as blank cells. It's your little victory dance!