将数据从 SQL Server 2012 复制到 Excel 时出现换行问题

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

提问by MrPink

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.

我最近升级到 SQL2012 并且正在使用 Management Studio。我在数据库中的一列中CHAR(13) + CHAR(10)存储了一个。

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.

当我使用 SQL Server 2008 时，这可以完全复制并粘贴到 Excel 中。但是，现在复制和粘贴相同的数据会在 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.

是否有我在 SQL2012 中遗漏的设置可以解决此问题？我不想简单地REPLACE(CHAR(13) + CHAR(10))选择每个数据库，因为我必须从使用SELECT *到定义每个单独的列。

回答by Robert Jeppesen

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.

我最好的猜测是，这不是错误，而是 Sql 2012 的一个功能。;-) 在其他情况下，您会很乐意保留您的 cr-lf，就像复制一大块文本时一样。只是它在您的情况下效果不佳。

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

回答by THelper

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.

我找到了解决该问题的方法；而不是手动复制粘贴，使用 Excel 连接到您的数据库并导入完整的表。然后删除您不感兴趣的数据。

Here are the steps (for Excel 2010)

以下是步骤（适用于 Excel 2010）

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

1. 进入菜单 Data > Get external data: From other sources > From SQL Server
2. 键入 sql 服务器名称（如果您的服务器上没有 Windows 身份验证，则输入凭据）并连接。
3. 选择包含带有换行符的数据的数据库和表，然后单击“完成”。
4. 选择目标工作表，然后单击“确定”。

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

Excel 现在将导入完整的表格，换行符完好无损。

回答by Charles Gagnon

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

这是通过在Tools -> Options...菜单，Query Results -> SQL Server -> Results to Grid下添加一个新选项Retain CR\LF on copy 或 save来解决的。

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.

默认值为未选择 (false)，这意味着从网格复制/保存将复制显示的文本（用空格替换 CR\LF）。如果设置为 true，则文本将在实际存储时从网格中复制/保存 - 无需替换字符。

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.

如果人们错过了连接项目链（导致https://connect.microsoft.com/SQLServer/feedback/details/735714），此问题已在 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.

您可以从https://msdn.microsoft.com/library/mt238290.aspx免费下载它，它是一个独立下载，因此不再需要完整的 SQL 媒体。

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

（注意 - https://msdn.microsoft.com/library/ms190078.aspx 上的页面当前未更新此信息。我正在跟进此信息，因此它应该很快反映新选项）

回答by Ronald

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

我想出的在结果中包含回车符/换行符（复制/使用标题复制/将结果另存为）以复制到 Excel 的最佳方法是在 中添加双引号SELECT，例如：

 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. NULLvalues will be kept, but that can be changed by using CONCAT('"', ColumnName, '"')or COALESCE(ColumnName, '').

空列数据将在 SQL Management Studio 中仅显示为 2 个双引号，但复制到 Excel 将导致空单元格。NULL值将被保留，但可以通过使用CONCAT('"', ColumnName, '"')或更改COALESCE(ColumnName, '')。

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

正如@JohnLBevan 所评论的，转义列数据也可以使用内置函数来完成QUOTENAME：

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

回答by Sunde

@AHiggins's suggestion worked well for me:

@AHiggins 的建议对我很有效：

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

回答by A. Murray

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 dataand 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.

右键单击目标数据库并选择Tasks > Export data并执行该操作。“选择目的地”屏幕上的目的地之一是 Microsoft Excel，并且有一个步骤可以接受您的查询。

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.

它是 SQL Server 导入和导出向导。它比我通常使用的带有标题的简单 Copy 选项要冗长得多，但是，除了跳过更多的箍之外，当您有大量数据可以进入 excel 时，这是一个值得的选择。

回答by Alex

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

当您最近使用“文本到列”时，Excel 有时会发生这种情况。

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.

尝试退出excel，重新打开并再次粘贴。这通常对我有用，但我听说您有时必须完全重新启动计算机。

回答by Jaydeep Patel

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

将数据从 SQL Server 复制到 Excel 时的行拆分问题。请参阅下面的示例并尝试使用替换一些字符。

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

回答by Jim Simoens

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.

以下“解决方法”保留了 CRLF 并支持将带有 CRLF 字符的数据粘贴到 Excel 中，而无需将列数据分成多行。它将需要用命名列替换“select *”，并且数据中的任何双引号都将替换为分隔符值。

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

回答by Paul McLeod

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.

为了能够将结果从 SQL Server Management Studio 2012 复制并粘贴到 Excel 或导出到带有列表分隔符的 Csv，您必须首先更改查询选项。

1. Click on Query then options.
2. Under Results click on the Grid.

3. Check the box next to:

   Quote strings containing list separators when saving .csv results.

1. 单击查询，然后单击选项。
2. 在结果下单击网格。

3. 选中旁边的框：

   保存 .csv 结果时引用包含列表分隔符的字符串。

This should solve the problem.

这应该可以解决问题。