相关文章推荐
文质彬彬的拐杖  ·  2021年评分最高的五部古装剧，排名第一的已 ...·  3 月前    · 
威武的煎饼  ·  江苏省药品监督管理局 综合公告·  4 月前    · 
独立的手链  ·  嚴淑女 兼任助理教授·  7 月前    · 
捣蛋的松球  ·  Stata ...·  8 月前    · 
私奔的绿豆  ·  赶海人不顾阻拦疯狂拾贝 ...·  11 月前    · 
Code  ›  从SQL Server 2012复制数据到Excel时的新行问题
https://www.qiniu.com/qfans/qnso-13380779
豁达的单车
1 年前
七牛云社区 牛问答 从SQL Server 2012复制数据到Excel时的新行问题

从SQL Server 2012复制数据到Excel时的新行问题

69 人关注

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

当我使用SQL Server 2008时，这可以完全正常地复制和粘贴到Excel。然而，现在，复制和粘贴相同的数据会在我在Excel中的数据中产生一个新的行/回车键。

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

15 个评论
nutsch ：
如何复制和粘贴，只是选择查询结果，ctrl+c，ctrl+V？
nutsch ：
我用Management Studio 2008（没有新行）和Management Studio 2012（有新行）复制了这个错误，使用查询 select top 10 char(10) + char(13) as [struff] from dbo.tbEntries
Derek Kromm ：
所以你是说你不希望回车符显示在Excel中，尽管它在数据中？如果是这样的话，听起来他们只是简单地修复了一个从2008年到2012年的错误......如果你的数据是这样表示的，你需要把它处理成你想要的格式。
MrPink ：
@Derek Kromm 这正是我所说的。我打算再次尝试安装服务器管理器2008，看看问题是否仍然存在，如果是这样，那么很可能是Excel的问题或其他问题。
Triynko ：
这很糟糕。 我曾经能够从SSMS 2008中复制数千行，无论是否有回车，并将它们完美地粘贴到Excel中。 现在我升级到了SSMS 2012，它完全被搞砸了。 SSMS 2012非常糟糕，导出到CSV的选项在两个版本中都没有用，因为它完全没有遵循CSV文件格式规范。
Triynko ：
CSV格式是非常具体的，通过要求带有引号、逗号或换行符的字符串用双引号括起来，并将实际的双引号加倍，来说明所有可能的字符。 SSMS 2012（和2008）只是把所有东西都扔到一个文件里，并在单元格之间粘上逗号，完全是草率和无用的。 无论2012年将什么格式放在剪贴板上都是非常非常错误的，这与SSMS 2008不同。
Triynko ：
在 "工具">"选项">"查询结果">"结果到网格">"保存.csv结果时引用含有列表分隔符的字符串 "下有一个选项。 这个选项在默认情况下没有被选中，换句话说，完全违反了CSV文件的格式，这真是无稽之谈。
Triynko ：
LOL，更糟糕的是......勾选该选项后，它不是像CSV规范中所说的那样把双引号变成一对双引号，而是把双引号转换成两个单引号。 这完全是不可接受的。
Triynko ：
"RFC 4180。每一行，或元组，用一个换行符隔开，最后一行可以用换行符来结束。每行应包含相同数量的字段，这些字段由一个字符分隔，通常是一个逗号。 字段可以用双引号字符括起来。如果是这样，那么字段可以包含逗号或换行符。如果用第二个相邻的双引号字符 "转义"，它们也可以包含双引号字符。空 数据值由一行中的两个分隔符表示，它们之间没有数据。"
Triynko ：
有人已经在这里提交了关于这个问题的错误报告： connect.microsoft.com/SQLServer/feedback/details/783274/ ...肯定是SSMS 2012的一个错误。 我添加了一个解决方法，说只要使用SSMS 2008就可以了，并抱怨了CSV实现的不好。
Michael12345 ：
微软已经指导用户解决这个连接问题： connect.microsoft.com/SQLServer/feedback/details/735714 目前，它被列为 "活跃 "的已知错误，MS已经发布了一个解决方法，基本上是用空字符串替换CHAR(10)和CHAR(13)。 它可以工作，但这不是我们所要的修复方法。
dsa42 ：
这个主题有很多订阅的追随者。任何遇到这个问题的人都应该到这里投票，以便微软发布一个修复方案： connect.microsoft.com/SQLServer/feedback/details/735714
StayPuft ：
投了这个票--太烦人了......
tnktnk ：
呃，这太可怕了。我面临巨大的时间压力，没有时间做这个。
Tot Zam ：
这篇文章可能有帮助： mssqltips.com/sqlservertip/3416/...
sql
sql-server-2008
excel
copy-paste
sql-server-2012
MrPink
MrPink
发布于 2012-11-14
19 个回答
Robert Jeppesen
Robert Jeppesen
发布于 2016-07-01
0 人赞同

我的最佳猜测是，这不是一个错误，而是Sql 2012的一个特点。） 在其他情况下，你会很乐意保留你的cr-lf的，比如在复制一大块文本的时候。只是在你的情况下，它的效果并不好。

你可以随时在你的选择中把它们剥离出来。这将使你的查询在两个版本中都符合你的意图。 

select REPLACE(col, CHAR(13) + CHAR(10), ', ') from table
Triynko ：
由于Excel对剪贴板上数据的解释取决于 "文本到列 "操作中最后使用的分隔符，这个问题变得更加复杂。 默认是"\t"（tab），但现在的问题似乎是，SSMS 2012实际上正在改变单元格中的数据，将剪贴板上的"\r "转换为"\r\n"，这非常非常糟糕。 我知道这一点是因为我的基于Flash的客户端将换行符存储为"\r"（回车符），而当我将这些数据从SSMS 2008粘贴到Excel时，并没有像现在这样在单元格的中间触发一个新行。
Robert Fricke ：
我不得不运行 select REPLACE(REPLACE(col, CHAR(13), '') CHAR(10), ' ') from table 为了确保没有一个单独的 \n 或 \r 搞乱我的 Excel 表
GoalBased ：
@Robert Fricke select REPLACE(REPLACE(col, CHAR(13), ''), CHAR(10), ' ') -- 你那里漏了一个逗号。
AHiggins ：
@GoalBased，我的垃圾数据要求我使用 SELECT REPLACE(REPLACE(REPLACE(col, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ') AS Col FROM Table
Beytan Kurt ：
谢谢@RobertFricke，你的评论对我的情况有所帮助。
Charles Gagnon
Charles Gagnon
发布于 2016-07-01
0 人赞同

通过在 " 工具"->"选项 "...菜单，" 查询结果"->"SQL服务器"->"结果到网格 "下增加一个新的选项 " 在复制或保存时保留CR/LF " 来解决这个问题。

你需要打开新的会话（窗口）以使更改生效。

默认情况是不选择（false），这意味着从网格中复制/保存的文本将按显示的方式复制（用空格替换CR\LF）。如果设置为 "true"，则从网格中复制/保存的文本将按照实际存储的文本进行复制/保存--不进行字符替换。

如果人们错过了跟随连接项目的链条（导致 https://connect.microsoft.com/SQLServer/feedback/details/735714） ，这个问题已经在SSMS的预览版中被修复。

你可以从 https://msdn.microsoft.com/library/mt238290.aspx， 它是一个独立的下载，所以不需要完整的SQL媒体了。

(注意-- https://msdn.microsoft.com/library/ms190078.aspx 的页面目前还没有更新这个信息。我正在跟进这个问题，所以它应该很快反映出新的选项)

Ondrej ：
此外，你还需要打开新的会话，使修改生效。
theyetiman ：
这是个救星，应该得到更多的支持。然而，在当前版本的SSMS（17.8.1）中似乎有一个错误，即 在复制或保存时保留CR/LF的 复选框在默认情况下没有被选中，但该设置仍然被应用，所以它仍然保留了CR/LF字符。你需要勾选它，保存设置，然后取消勾选，这样它在复制/粘贴时就会忽略CR/LF。
Aamir ：
随着时间的变化，这应该是一个公认的答案
THelper
THelper
发布于 2016-07-01
0 人赞同

我发现了一个解决这个问题的方法；与其用手复制粘贴，不如用Excel连接到你的数据库并导入完整的表格。然后删除你不感兴趣的数据。

以下是步骤（针对Excel 2010）。

  • 进入菜单 Data > Get external data: From other sources > From SQL Server
  • 键入sql服务器名称（如果你的服务器上没有Windows认证，则键入凭证）并连接。
  • 选择包含有换行线的数据的数据库和表，然后点击'完成'。
  • 选择目标工作表并点击'OK'。

    • 现在，Excel将导入完整的表格，换行线保持不变。

    Triynko ：
    换句话说，SQL Server Management Studio 2012已经坏了，不要使用它，只需使用Excel导入数据，把SSMS排除在外。 这不是一个解决方案，这只是在重述问题。 很明显，有很多方法可以将数据从SQL Server中取出并导入Excel。 问题是，SSMS 2012把事情搞砸了，所以你不能像SSMS 2008那样简单地复制/粘贴数据到Excel。
    THelper ：
    @Triynko 是的，这就是为什么我称它为'变通办法'。
    Triynko ：
    这不是一个变通的办法；这是在重述问题。 问题不仅仅是SSMS不能工作......而是SSMS不能工作， 而我需要使用SSMS 。 一个涉及到完全放弃SSMS的解决方案根本不是解决方案，当然也不是一个解决方法。 解决办法是在SSMS中做一些不同的事情，比如用其他字符替换cr-lf字符，或者改变一些选项来使其发挥作用。 顺便说一下，Excel从SQL Server导入的数据也是坏的，但我不打算讨论这个问题。
    Edwin Stoteler ：
    @Triynko 如果目标是将数据从表格中获取到Excel中，那么这就是一种变通方法。糟糕的是，为它写一个c#应用程序也会被称为一个变通办法。
    Jed Schaaf ：
    但是，如果我想导出的 "表 "是一个查询的结果，而不是一个数据库表，怎么办？
    Ronald
    Ronald
    发布于 2016-07-01
    0 人赞同

    我想出的将回车/换行符包括在结果中的最好方法是在 SELECT 中加入双引号，例如：（复制/带标题的复制/将结果保存为）。 

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

    如果列数据本身可以包含双引号，可以用'双引号'来转义。
    

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

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

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

     SELECT QUOTENAME(ColumnName, '"') AS ColumnName FROM TableName;
    JohnLBevan ：
    注意：为了确保文本中的任何引号被正确转义，你可能想使用。 SELECT quotename(ColumnName,'"') AS ColumnName FROM TableName;
    Ronald ：
    @JohnLBevan 这是个很好的补充，使代码更有可读性! QUOTENAME 函数的完整文档可以在以下网站找到： msdn.microsoft.com/nl-nl/library/....
    Fruitbat ：
    注意QUOTENAME的限制（128个字符）。
    Segmentation Fault ：
    谢谢你。QUOTENAME似乎是一个完美的、最优雅的解决方案。
    Sunde
    Sunde
    发布于 2016-07-01
    0 人赞同

    @AHiggins的建议对我来说很有效。 

    REPLACE(REPLACE(REPLACE(B.Address, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ')
    Jaydeep Patel
    Jaydeep Patel
    发布于 2016-07-01
    0 人赞同

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

    SELECT replace(replace(CountyCode, char(10), ''), char(13), '') 
FROM [MSSQLTipsDemo].[dbo].[CountryInfo]
    Alex
    Alex
    发布于 2016-07-01
    0 人赞同

    当你最近使用 "文本到列 "时，Excel有时会出现这种情况。

    试着退出excel，重新打开，然后再次粘贴。这通常对我有用，但我听说有时你必须完全重新启动你的电脑。

    A. Murray
    A. Murray
    发布于 2016-07-01
    0 人赞同

    既然这里还没有提到这个问题，而且我就是这样绕过这个问题的...

    右键单击目标数据库，选择 Tasks > Export data ，然后按照这个步骤进行。在 "选择目的地 "屏幕上的目的地之一是微软Excel，有一个步骤可以接受你的查询。

    这是SQL Server的导入和导出向导。它比我通常使用的简单的带标题复制选项要啰嗦得多，但是，省去了更多的麻烦，当你有大量的数据需要输入Excel时，这是一个值得的选择。

    Jim Simoens
    Jim Simoens
    发布于 2016-07-01
    0 人赞同

    下面的 "解决方法 "保留了CRLF，并支持将带有CRLF字符的数据粘贴到Excel中，而不将列数据分成多行。 这需要用命名的列替换 "选择*"，数据中的任何双引号将被替换为分隔符值。 

    declare @delimiter char(1)
set @delimiter = '|'
declare @double_quote char(1)
set @double_quote = '"'
declare @text varchar(255)
set @text = 'This
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
    Triynko ：
    这很好，但如果你的数据中已经有'|'的话，它就会失效，我的数据就有。 这是一个在线教育软件，数据库中的 "响应 "字段使用"|"字符来分隔独立的文本字段或单词列表，在这些活动中，学生需要识别和点击不同数量的单词。 问题是，SSMS没有使用CSV格式正确地格式化剪贴板数据，没有正确的引号字段和转义引号。 此外，Excel不能正确地读取CSV数据，忽略了转义序列，并使用了一些不符合CSV规范的简单化分隔符格式。
    Triynko ：
    唯一真正的解决方法是使用SSMS 2008，并使用Open Office CALC进行CSV加载和保存，因为它可以完美地工作。
    Paul McLeod
    Paul McLeod
    发布于 2016-07-01
    0 人赞同

    为了能够将结果从SQL Server Management Studio 2012复制和粘贴到Excel，或者用列表分隔符导出到Csv，你必须首先改变查询选项。

  • 点击查询，然后点击选项。

  • 在 "结果 "下点击 "网格"。

  • 勾选旁边的方框。

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

    这应该能解决这个问题。

    • Triynko ：
    不是的。 它实际上是用双单引号替换了引号，这是完全不可接受的，也不符合CSV规范。 我甚至在2013年4月9日的原始帖子的评论中提到了这一点。"LOL，更糟糕的是......勾选该选项后，它不是像CSV规范所说的那样把双引号变成一对双引号，而是把双引号转化为两个单引号。这完全是不可接受的。"
    JasonH ：
    这实际上解决了我所面临的问题，即在一个非常大的注释字段中存在多个CRLF，在2012年的Excel中，该字段会被包裹到下一条记录中，把一切都搞乱了。 (这对我来说在SSMS 2005中是有效的。）
    Eric Steinmetz
    Eric Steinmetz
    发布于 2016-07-01
    0 人赞同

    一个不太理想的解决方法是使用2008年的GUI对2012年的数据库进行复制查询结果。 一些功能，如 "脚本表为CREATE "不工作，但你可以运行查询，并将结果复制粘贴到Excel等，从2012年的数据库没有问题。

    微软需要解决这个问题!

    Eric Labashosky
    Eric Labashosky
    发布于 2016-07-01
    0 人赞同

    你可以将数据导出到Excel，而不是复制和粘贴到excel。右击数据库 -> 任务 -> 导出数据...

  • 资源。 SQL服务器本地客户端
  • 目的地。 丞燕
  • 指定表复制或查询： 选择查询并输入你的查询。

    • 数据中保留的CR/LF。

    BONUS(nulls不会被复制为'NULL')。

    mdc ：
    这个对我有用，但我必须选择Excel 97-2003版本。2016年导致了错误。除了这两个版本，我没有尝试其他版本。
    essential
    essential
    发布于 2016-07-01
    0 人赞同

    由于很多时候我必须将数据从SQL复制到Excel，我创建了一个函数来处理换行和制表符（在粘贴到Excel后会使列发生变化）。 

    CREATE FUNCTION XLS(@String NVARCHAR(MAX) )
RETURNS NVARCHAR(MAX)
BEGIN
SET @String = REPLACE (@String, CHAR(9), ' ')
    SET @String = REPLACE (@String, CHAR(10), ' ')
    SET @String = REPLACE (@String, CHAR(13), ' ')
    RETURN @String
CREATE FUNCTION XLS(@String NVARCHAR(MAX) )
RETURNS NVARCHAR(MAX)
BEGIN
SET @String = REPLACE (@String, CHAR(9), ' ')
    SET @String = REPLACE (@String, CHAR(10), ' ')
    SET @String = REPLACE (@String, CHAR(13), ' ')
    RETURN @String

    使用实例。
    

    SELECT dbo.XLS(Description) FROM Server_Inventory
    rossmcbain
    rossmcbain
    发布于 2016-07-01
    0 人赞同

    你可以尝试将查询结果保存为excel格式，将文件扩展名改为.txt。用excel打开（用...打开），然后用文本到列（格式化为文本）。 不知道这对这种情况是否有效，但对其他格式问题，如excel自动剥离的问题，效果不错。

    Aaron Kempf
    Aaron Kempf
    发布于 2016-07-01
    0 人赞同

    你真的可以找出哪些行/数据有回车，并修复源数据......而不是只在上面贴个胶布。

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

    XGIS
    XGIS
    发布于 2016-07-01
    0 人赞同
  • 如果你的表包含一个 nvarchar(max) 字段，将该字段移到表的底部。
  • 如果字段类型与nvarchar(max)不同，那么要确定违规的字段，并使用同样的技术。
  • 在SQL中重新选择表。
  • 如果你不能在没有改动的情况下保存，你可以在 TOOLS | OPTIONS 中暂时关闭相关警告。这种方法没有任何风险。
  • 复制并粘贴带标题的SQL GRID显示到Excel。
  • 数据可能仍然表现为回车，但至少你的数据都在同一行。
  • 然后选择所有的行记录，对ID列做一个自定义排序。
  • 你的所有记录现在应该是完整和连续的。
    • Scott Thornton
    Scott Thornton
    发布于 2016-07-01
    0 人赞同

    我遇到了同样的问题。 我能够使用以下解决方案将我的结果转为CSV。

  • 在结果网格的左上角点击右键
  • 选择 "将结果保存为."
  • 选择csv和viola!
    • Jed Schaaf ：
    这并没有将日期/时间以电子表格的有用格式显示出来，也没有包括标题。它确实将多行数据保持在同一行中，这回答了最初的问题，但由于它有可能引入其他问题，我不能很好地给它投票。
    Geek ：
    对不起，这不起作用，换行符/回车符仍然被处理，将该行分割成若干行。
    user922020
    user922020
    发布于 2016-07-01
    0 人赞同
     
    推荐文章
    文质彬彬的拐杖  ·  2021年评分最高的五部古装剧，排名第一的已下架_大陆_电视剧_赘婿
    3 月前
    威武的煎饼  ·  江苏省药品监督管理局 综合公告
    4 月前
    独立的手链  ·  嚴淑女 兼任助理教授
    7 月前
    捣蛋的松球  ·  Stata 学习笔记（1）：数值型与字符串型变量的转换 - 知乎
    8 月前
    私奔的绿豆  ·  赶海人不顾阻拦疯狂拾贝 堪比熊猫珍稀鸟类被抢食_手机新浪网
    11 月前
    今天看啥   ·   Py中国   ·   codingpro   ·   藏经阁   ·   小百科   ·   link之家   ·   卧龙AI搜索
    删除内容请联系邮箱 2879853325@qq.com
    Code - 代码工具平台
    © 2024 ~ 沪ICP备11025650号