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

Question

I recently upgraded to SQL2012 and am using Management Studio. One of my columns in the database has a CHAR(13) + CHAR(10) stored in it.

When I was using SQL Server 2008, this would copy and paste completely fine into Excel. Now, however, copying and pasting the same data creates a new line/ carriage return in the data I have in Excel.

Is there a setting I missed in SQL2012 that will resolve this issue? I don't want to simply REPLACE(CHAR(13) + CHAR(10)) on every single database selection, as I would have to go from using SELECT * to defining each individual column.

How do you copy and paste, just select the query results, ctrl+c, ctrl+V? — nutsch, Nov 14 '12 at 21:27
I replicated the error with Management Studio 2008 (no new lines) and Management Studio 2012 (new lines), using query `select top 10 char(10) + char(13) as [struff] from dbo.tbEntries` — nutsch, Nov 14 '12 at 21:40
So you're saying you don't want the carriage return to show up in excel, despite it being in the data? It sounds like they simply fixed a bug from 2008 to 2012 if that's the case...if that's how your data is represented you need to manipulate it to the format you want instead — Derek, Nov 15 '12 at 04:06
@Derek Kromm that is exactly what i am saying. I am going to try installing server manager 2008 again and see if the issue persists, If this is so then it is most probably an excel issue or something. — MrPink, Nov 15 '12 at 10:17
This sucks. I was able to copy thousands of rows, with and without carriage returns, from SSMS 2008 and paste them into Excel perfectly. Now that I've upgraded to SSMS 2012, it's completely screwed up. SSMS 2012 is very broken, and the export to CSV option is useless in both versions, because it utterly fails to follow the CSV file format specification. — Triynko, Apr 09 '13 at 21:07
The CSV format is very specific, and accounts for all possible characters by requiring strings with quotes, commas, or line breaks to be enclosing in double quotes, with actual double quotes doubled. SSMS 2012 (and 2008) just throws everything in a file and sticks commas between cells, utterly sloppy and useless. Whatever format 2012 is putting on the clipboard is very very wrong, unlike SSMS 2008. — Triynko, Apr 09 '13 at 21:08
There is an option under Tools > Options > Query Results > Results To Grid > "Quote strings containing list separators when saving .csv results". It's nonsense that this option is unchecked by default, in other words a complete violation of the CSV file format. — Triynko, Apr 09 '13 at 21:19
LOL, even WORSE... with that option checked, instead of turning double quotes into pairs of double quotes like the CSV specification says, it converts double quotes into two single quotes. This is utterly, completely unacceptable. — Triynko, Apr 09 '13 at 21:23
"RFC 4180: Each row, or tuple, is separated by a linefeed, and the last line can be terminated by a linefeed. Each line should contain the same number of fields, which are separated by a single character, usually a comma. **Fields may be enclosed in double-quote characters. If they are, then fields may contain commas or linefeed characters. They can also contain double-quote characters if ‘escaped’ with a second adjacent double-quote character.** Null data values are denoted by two delimiters in a row with no data between them." — Triynko, Apr 09 '13 at 21:52
Someone already filed a bug report about this here: https://connect.microsoft.com/SQLServer/feedback/details/783274/ssms-2012-copy-paste-results-with-line-breaks-causes-new-rows-in-excel# Definitely a bug with SSMS 2012. I added a workaround saying just use SSMS 2008 and complained about the poor CSV implementation. — Triynko, Apr 09 '13 at 22:03
Microsoft has directed users to this connect issue: https://connect.microsoft.com/SQLServer/feedback/details/735714 It's listed as and "Active" known bug at the moment and MS have posted a workaround which is to basically replace CHAR(10) and CHAR(13) with empty string. It'll work but it's not the fix any of us were looking for. — Michael12345, Sep 29 '13 at 22:09
This thread has a lot of subscribed followers. Anyone experiencing this problem should go here and vote for it so that Microsoft releases a fix for it: https://connect.microsoft.com/SQLServer/feedback/details/735714 — dsa42, Oct 07 '13 at 16:46
Ugh, this is awful. I am under huge time pressure and have no time for this. — tnktnk, Apr 25 '15 at 17:41
This article might help: https://www.mssqltips.com/sqlservertip/3416/line-split-issues-when-copying-data-from-sql-server-to-excel/ — Tot Zam, Aug 02 '17 at 16:44

Robert Jeppesen · Answer 1 · 2012-11-23T23:50:51.223

56

My best guess is that this is not a bug, but a feature of Sql 2012. ;-) In other contexts, you'd be happy to retain your cr-lf's, like when copying a big chunk of text. It's just that it doesn't work well in your situation.

You could always strip them out in your select. This would make your query for as you intend in both versions:

select REPLACE(col, CHAR(13) + CHAR(10), ', ') from table

edited Nov 23 '12 at 23:50

answered Nov 21 '12 at 20:09

Robert Jeppesen

7,837
3
35
50

This problem is compounded by the fact that Excel's interpretation of the data on the clipboard depends on the last used separator character for a "text to columns" operation. The default is "\t" (tab), but the problem now seems to be that SSMS 2012 is actually ALTERING the data in the cells, converting "\r" to "\r\n" on the clipboard, which is very very bad. I know this because my Flash-based client stores line breaks as "\r" (carriage returns), and when I pasted this data from SSMS 2008 into Excel, it was not triggering a new row to be started in the middle of a cell like it is now. – Triynko Apr 09 '13 at 21:15
7

I had to run `select REPLACE(REPLACE(col, CHAR(13), '') CHAR(10), ' ') from table` To make sure no single \n or \r messed up my Excel sheet – Robert Fricke Apr 29 '13 at 11:30
4

@Robert Fricke ``select REPLACE(REPLACE(col, CHAR(13), ''), CHAR(10), ' ')`` -- You left out a comma there. – GoalBased May 16 '14 at 21:08
1

@GoalBased, my junk data required me to use `SELECT REPLACE(REPLACE(REPLACE(col, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ') AS Col FROM Table` – AHiggins Oct 30 '14 at 18:32
Thanks @RobertFricke, your commented helped me in my case. – Beytan Kurt May 14 '15 at 08:43
1

Altering data to "make it work" is not a good solution. It's actually a really bad solution. If the newline is a significant part of the data, the best solution would be a way to make the SSMS-Excel copy-paste put the data from the column containing the newline into a multi-line cell. Ronald's answer is better than this one because of that. – Jed Schaaf May 09 '16 at 22:07

score 45 · Answer 2 · edited May 17 '17 at 17:56

45

This is fixed by adding a new option Retain CR\LF on copy or save under the Tools -> Options... menu, Query Results -> SQL Server -> Results to Grid.

You need to open new session (window) to make the change take a place.

The default is unselected (false) which means that copying/saving from the grid will copy the text as it is displayed (with CR\LF replaced with spaces). If set to true the text will be copied/saved from the grid as it actually is stored - without the character replacement.

In case people missed following the chain of connect items (leading to https://connect.microsoft.com/SQLServer/feedback/details/735714), this issue has been fixed in the preview version of SSMS.

You can download this for free from https://msdn.microsoft.com/library/mt238290.aspx, it is a standalone download so does not need the full SQL media anymore.

(Note - the page at https://msdn.microsoft.com/library/ms190078.aspx currently isn't updated with this information. I'm following up on this so it should reflect the new option soon)

edited May 17 '17 at 17:56

Ondrej

1,209
1
11
21

answered Oct 29 '15 at 17:13

Charles Gagnon

3,619
2
17
7

3

Also you need to open new session to make the changes take the effect. – Ondrej May 17 '17 at 13:17
3

This is a saviour and should be more upvoted. However there seems to be a bug in the current version of SSMS (17.8.1) whereby the check box for **Retain CR\LF on copy or save** is unchecked by default, but the setting is still applied so it still retains the CR/LF chars. You need to tick it, save the settings, then un-tick it for it to ignore CR/LF on copy/paste. – theyetiman Dec 14 '18 at 10:08
With the change of time, this should be an accepted answer – Aamir May 13 '20 at 00:56

score 29 · Answer 3 · answered Jun 06 '14 at 08:50

29

I found a workaround for the problem; instead of copy-pasting by hand, use Excel to connect to your database and import the complete table. Then remove the data you are not interested in.

Here are the steps (for Excel 2010)

Go to menu Data > Get external data: From other sources > From SQL Server
Type the sql server name (and credentials if you don't have Windows authentication on your server) and connect.
Select the database and table that contains the data with the newlines and click 'Finish'.
Select the destination worksheet and click 'Ok'.

Excel will now import the complete table with the newlines intact.

answered Jun 06 '14 at 08:50

THelper

15,333
6
64
104

1

In other words, SQL Server Management Studio 2012 is broken, don't use it, and just use Excel to import the data leaving SSMS out of the loop. That's not a solution, that's simply restating the problem. Obviously there are numerous ways to get data out of SQL Server and into excel. The problem is that SSMS 2012 is screwing things up so you can't simply copy/paste data to Excel like you could with SSMS 2008. – Triynko Jul 01 '14 at 01:50
8

@Triynko Yes, that's why I called it 'a workaround'. – THelper Jul 01 '14 at 05:59
1

It's not a workaround; it's restating the problem. The problem isn't just that SSMS doesn't work... it's that SSMS doesn't work, *and I need to use SSMS*. A solution that involves abandoning SSMS entirely is no solution at all, and certainly not a workaround. A workaround would involve doing something differently within SSMS, such as replacing cr-lf characters with other characters or altering some options to make it work. Excel's data import from SQL Server is broken too, btw, but I'm not getting into that. – Triynko Sep 30 '14 at 17:39
6

@Triynko If the goal is to get data from a table to excel, then this IS a workaround. Heck, writing a c# application for it would be called a workaround. – Edwin Stoteler May 29 '15 at 14:37
4

But what if the "table" I want to export is the result of a query instead of a database table? – Jed Schaaf May 09 '16 at 22:09
1

Responding to Jed, I created a new table specifically to take advantage of this workaround. Once the data was in Excel, I deleted the new table. (e.g., SELECT * INTO NewTable FROM OriginalTable1 JOIN OriginalTable2). Thanks to THelper for the great tip. – cjo30080 Sep 23 '16 at 20:21
Thankyouthankyouthankyou - this enabled me to export concatenated data to Excel, with CR/LF created by the query intact, so I could then import into a proprietary DB. I've spent many hours on this. It's like magic. lol. And thanks to @cjo30080 for testing, and Jed, for asking. – Julie Nov 15 '16 at 23:39
If you want to export the result of a query instead of a database table: Follow steps 1, 2, 3 in this answer. This takes you to the "Import Data" dialog where you could click OK, but click the Properties button instead. This opens the "Connection Properties" dialog where you can click on the "Definition" tab. In that tab, you can change the "Command type" to SQL and paste any query you want into the "Command text" box. – NJS Aug 04 '17 at 22:08

Ronald · Answer 4 · 2016-11-23T10:22:18.227

15

The best way I've come up to include the carriage returns/line breaks in the result (Copy/Copy with Headers/Save Results As) for copying to Excel is to add the double quotes in the SELECT, e.g.:

 SELECT '"' + ColumnName + '"' AS ColumnName FROM TableName;

If the column data itself can contain double quotes, they can be escaped by 'double-double quoting':

 SELECT '"' + REPLACE(ColumnName, '"', '""') + '"' AS ColumnName FROM TableName;

Empty column data will show up as just 2 double quotes in SQL Management Studio, but copying to Excel will result in an empty cell. NULL values will be kept, but that can be changed by using CONCAT('"', ColumnName, '"') or COALESCE(ColumnName, '').

As commented by @JohnLBevan, escaping column data can also be done using the built-in function QUOTENAME:

 SELECT QUOTENAME(ColumnName, '"') AS ColumnName FROM TableName;

edited Nov 23 '16 at 10:22

answered Mar 10 '15 at 14:24

Ronald

1,795
14
17

2

NB: To ensure any quotes in the text are correctly escaped, you may want to use: `SELECT quotename(ColumnName,'"') AS ColumnName FROM TableName;` – JohnLBevan Nov 02 '16 at 13:45
1

@JohnLBevan That's a great addition and makes the code even more readable! Full documentation of the `QUOTENAME` function can be found on: https://msdn.microsoft.com/nl-nl/library/ms176114.aspx?f=255&MSPPError=-2147217396. – Ronald Nov 23 '16 at 10:16
2

Watch out for limitations of QUOTENAME (128 characters). – Fruitbat Mar 24 '17 at 17:26
Thank you. QUOTENAME seems to be the perfect and most elegant solution. – Segmentation Fault Dec 04 '18 at 10:34

score 6 · Answer 5 · edited Sep 22 '15 at 18:18

6

@AHiggins's suggestion worked well for me:

REPLACE(REPLACE(REPLACE(B.Address, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ')

edited Sep 22 '15 at 18:18

josliber

43,891
12
98
133

answered Sep 22 '15 at 17:46

Sunde

61
1
1

score 5 · Answer 6 · edited Oct 12 '19 at 16:29

5

Line Split Issues when Copying Data from SQL Server to Excel. see below example and try using replace some characters.

SELECT replace(replace(CountyCode, char(10), ''), char(13), '') 
FROM [MSSQLTipsDemo].[dbo].[CountryInfo]

edited Oct 12 '19 at 16:29

Juan Caicedo

1,425
18
31

answered Oct 12 '19 at 13:37

Jaydeep Patel

121
1
8

score 3 · Answer 7 · answered Jun 14 '13 at 18:02

3

This sometimes happens with Excel when you've recently used "Text to Columns."

Try exiting out of excel, reopening, and pasting again. That usually works for me, but I've heard you sometimes have to restart your computer altogether.

answered Jun 14 '13 at 18:02

Alex

39
1

score 3 · Answer 8 · answered Jul 28 '15 at 11:09

Seeing as this isn't already mentioned here and it's how I got around the issue...

Right click the target database and choose Tasks > Export data and follow that through. One of the destinations on the 'Choose a destination' screen is Microsoft Excel and there's a step that will accept your query.

It's the SQL Server Import and Export wizard. It's a lot more long-winded than the simple Copy with headers option that I normally use but, save jumping through a lot more hoops, when you have a lot of data to get into excel it's a worthy option.

score 1 · Answer 9 · edited Sep 12 '13 at 19:57

1

The following "work-around" retains the CRLF and supports pasting data with CRLF characters into Excel without breaking column data into multiple lines. It will require replacing "select *" with named columns and any double-quotes in the data will be replaced with the delimiter value.

declare @delimiter char(1)
set @delimiter = '|'

declare @double_quote char(1)
set @double_quote = '"'

declare @text varchar(255)
set @text = 'This
"is"
a
test'

-- This query demonstrates the problem.  Execute the query and then copy/paste into Excel.
SELECT @text

-- This query demonstrates the solution.
SELECT @double_quote + REPLACE(@text, @double_quote, @delimiter) + @double_quote

edited Sep 12 '13 at 19:57

cheesemacfly

11,622
11
53
72

answered Sep 12 '13 at 19:39

Jim Simoens

11
1

1

That's nice, except it fails if your data has '|' in it already, which mine does. It's online educational software, and the Response field in the database uses the '|' character to delimit separate text fields or lists of words in activities where students need to identify and click on a variable number of words. The problem is that SSMS is not formatting the clipboard data properly using a CSV format, with properly quoted fields and escaped quotes. Furthermore, Excel doesn't read CSV data properly, ignores escape sequences, and uses some simplistic delimiter format that is not to CSV spec. – Triynko Sep 30 '14 at 17:34
The only real workaround is to use SSMS 2008, and use Open Office CALC for CSV loading and saving, since it works flawlessly. – Triynko Sep 30 '14 at 17:35

score 1 · Answer 10 · edited Jun 20 '20 at 09:12

1

In order to be able to copy and paste results from SQL Server Management Studio 2012 to Excel or to export to Csv with list separators you must first change the query option.

Click on Query then options.
Under Results click on the Grid.
Check the box next to:

Quote strings containing list separators when saving .csv results.

This should solve the problem.

edited Jun 20 '20 at 09:12

Community

1
1

answered Sep 10 '14 at 17:04

Paul McLeod

11
1

2

Nope. It actually replaces quotes with double single quotes, which is entirely unacceptable and does not follow the CSV spec. I even mentioned it in the comments of the original post on April 9th, 2013: "LOL, even WORSE... with that option checked, instead of turning double quotes into pairs of double quotes like the CSV specification says, it converts double quotes into two single quotes. This is utterly, completely unacceptable." – Triynko Sep 30 '14 at 17:45
This actually solved the issue I was facing, which was multiple CRLF in a very large comment field, where in 2012 to Excel the field would wrap to the next record, messing everything up. (This worked for me in SSMS 2005) – JasonH May 01 '15 at 17:37

score 1 · Answer 11 · answered Dec 23 '14 at 14:10

One less than ideal workaround is to use the 2008 GUI against the 2012 database for copying query results. Some functionality like "script table as CREATE" does not work, but you can run queries and copy paste the results into Excel etc from a 2012 database with no issues.

Microsoft needs to fix this!

score 1 · Answer 12 · answered Apr 25 '16 at 21:13

1

Instead of copying & pasting into excel you could export to Excel. Right click the database -> Tasks -> Export Data...

Source: SQL Server Native Client
Destination: Excel
Specify Table Copy or Query: pick query and enter your query

CR/LF retained in the data.

BONUS(nulls are not copied as 'NULL').

answered Apr 25 '16 at 21:13

Eric Labashosky

29,484
14
39
32

This one worked for me BUT I had to select the Excel 97-2003 version. 2016 resulted in errors. Didn't try other versions than those two. – mdc Sep 18 '18 at 12:13

score 1 · Answer 13 · answered Sep 29 '17 at 12:22

As many times I have to copy data from SQL to excel, I've created function to deal with with new line and also tab characters (which make shifts in columns after pasting to Excel).

CREATE FUNCTION XLS(@String NVARCHAR(MAX) )

RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @String = REPLACE (@String, CHAR(9), ' ')
    SET @String = REPLACE (@String, CHAR(10), ' ')
    SET @String = REPLACE (@String, CHAR(13), ' ')
    RETURN @String
END

CREATE FUNCTION XLS(@String NVARCHAR(MAX) )
RETURNS NVARCHAR(MAX)

AS
BEGIN
    SET @String = REPLACE (@String, CHAR(9), ' ')
    SET @String = REPLACE (@String, CHAR(10), ' ')
    SET @String = REPLACE (@String, CHAR(13), ' ')
    RETURN @String
END

Example usage:

SELECT dbo.XLS(Description) FROM Server_Inventory

score 0 · Answer 14 · answered Nov 20 '12 at 17:44

You could try save the query results as excel, change the file extension to .txt. Open using excel (open with...) then use text to columns (formatting as text). Not sure if this will work for this situation, but works well for other formatting issues that excel auto-strips off.

score 0 · Answer 15 · answered Nov 21 '12 at 20:12

you really could find out which rows / data has carriage returns and fix the source data.. instead of just put a bandaid on it.

UPDATE table Set Field = Replace(Replace(Field, CHAR(10), ' '), CHAR(13), ' ') WHERE Field like '%' + CHAR(10) + '%' or Field like '%' + CHAR(13) + '%'

score 0 · Answer 16 · edited Mar 30 '14 at 00:52

If your table contains an nvarchar(max) field move that field to the bottom of your table.
In the event the field type is different to nvarchar(max), then identify the offending field or fields and use this same technique.
Save It.
Reselect the Table in SQL.
If you cant save without an alter you can temporarily turn of relevant warnings in TOOLS | OPTIONS. This method carries no risk.
Copy and Paste the SQL GRID display with Headers to Excel.
The data may still exhibit a carriage return but at least your data is all on the same row.
Then select all row records and do a custom sort on the ID column.
All of your records should now be intact and consecutive.

score 0 · Answer 17 · answered Oct 17 '14 at 19:35

0

I ran into the same issue. I was able to get my results to a CSV using the following solution:

Execute query
Right click in the top left corner of the results grid
Select "Save Results as.."
Choose csv and viola!

answered Oct 17 '14 at 19:35

Scott Thornton

136
1
7

This does not put dates/datetimes in a useful format for a spreadsheet nor does it include the headers. It does keep the multi-line data in the same row, which answers the original question, but because it potentially introduces other problems, I can't feel good about upvoting it. – Jed Schaaf May 09 '16 at 22:20
2

Sorry, this does not work, the newlines/carriage return are still processed splitting the row into several rows – Geek Mar 22 '17 at 13:04

score 0 · Answer 18 · answered Feb 10 '15 at 20:03

Changing all my queries because Studio changed version isn't an option. Tried the preferences mentioned above to no effect. It didn't put the quotes in when there was a CR-LF. Perhaps it only triggers when a comma happens.

Copy-paste to Excel is a mainstay of SQL server. Mircosoft either needs a checkbox to revert back to 2008 behavior or they need to enhance the clipboard transfer to Excel such that ONE ROW EQUALS ONE ROW.

score -2 · Answer 19 · answered Jun 30 '16 at 16:51

-2

Once Data is exported to excel, highlight the date column and format to fit your needs or use the custom field. Worked for me like a charm!

answered Jun 30 '16 at 16:51

user6534600

1

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

19 Answers19

Linked