主页 / dba / 问题 / 190898
Accepted
Jonesome Reinstate Monica
Asked: 2017-11-15 08:54:37 +0800 CST

SQL Server：SSMS“将结果另存为”创建 Excel 破坏的 CSV

  • 772

使用将结果另存为选项，如下所示。CSV 看起来不错！

但是当 Excel 打开/导入它时：

  • 长字符串被破坏为数字（如果数据主要是数字）
  • 未正确检测日期
  • 我的用户对这一切都很不满

如何在不跳舞的情况下查询干净的 excel 文件（真正的 xls/xlsx）？（我们喜欢 SQL 文件本身中的所有内容......我希望我们可以执行“SELECT INTO FILE:C:\SQLOUTPUT\DATA.XLS ...”）

在此处输入图像描述

sql-server ssms

6 个回答

  1. 首先，确保您已按照您想要的方式设置了查询选项。

    转到查询菜单，然后选择查询选项：

    查询选项对话框

    两个突出显示的选项中的第一个告诉 SSMS 在您的 CSV 文件中包含列标题。第二个告诉 SSMS 在包含逗号的列周围加上单引号。

    我怀疑你已经这样做了，但我认为为了完整性我应该提到它。

    如果这不足以将您的数据转换为 Excel 将正确解释的格式，我的下一步可能是（如评论中所述）使用导入/导出向导生成执行此操作的 SSIS 包。然而，向导并不总是得到正确的引用——特别是，它可以把东西放在双引号中，但不会“加倍”嵌入在字符串中的双引号。

    通常在这一点上，我诉诸蛮力。

    我创建了一个修改后的查询：

    • 将数据中的双引号 ( ) 替换"为双双引号 ( "")；
    • 将数字/日期值转换为字符串，以及
    • 将所有数据放入双引号中，并且（最后）
    • 输出连接在一起的所有列，用逗号分隔。

    所以，而不是

     ID | First Name | Last Name |      Address      
----+------------+-----------+-------------------
  1 |  John "JT" |   Smith   |  123 Wayne's Way

    我生成：

    One_Big_Column
------------------------------------------------
"1","John ""JT""","Smith","123 Wayne's Way"

    如果我要定期使用该查询，它将包含一sortOrder列（设置为 1），并使用创建标题行UNION ALL的 a 进行编辑。SELECT然后我包装UNION ALLed 查询（使它们成为子查询），所以我可以只选择One_Big_Column，并按sortOrder.

    SELECT One_Big_Column
  FROM (
        SELECT '"Id","First Name","Last Name","Address"' as One_Big_Column
              ,0 as sortOrder
        UNION ALL
        SELECT '"' + CAST(Id as varchar(30)) + '","'
                    + REPLACE(firstname, '"','""') + '","'
                    + REPLACE(lastname, '"','""') + '","'
                    + REPLACE(address, '"','""') + '"'
              ,1 AS sortOrder
          FROM myTable
       ) sq
 ORDER BY sortOrder
;

    你可以在这里看到这个运行。

    注意：如果您手动格式化 CSV，您可能希望关闭上述两个复选框。

    • 5

  2. 我在字段上遇到了同样的问题，并且限制输出的长度根本没有帮助，因为它与回车有关。

    在运行要输出到 Excel 的数据之前，请更改对这些自由文本字段的查询以处理这些返回：

    SELECT REPLACE(REPLACE(Description, CHAR(10), ''), CHAR(13), ''), ...

    一旦我这样做了，Excel 输出就如预期的那样。这是让我正确移动的链接。

    • 3
  3. Best Answer

    唯一真正有效的答案是：使用 SSIS

    我想避免使用 SSIS，因为我喜欢简单的 SQL。简单的 SQL 是：

    • 易于测试
    • 易于版本控制
    • 轻松投入 SQL 代理作业
    • 轻松将 sql 结果写入电子邮件

    我可以在五到二十分钟内完成整个事情并完成。我还可以轻松地将其复制到多个 SQL Server。

    我本来很想使用SQL#，（一直想找个借口来使用它！）但是它缺乏原生的 excel 导出功能，而且我今天必须扑灭一些大火，所以不能乱学。

    当我必须将 SSIS、SQL 代理等部分链接在一起时，需要 1 个或更多小时的修补时间，并且在我们移动 SQL Server（每 16-24 个月执行一次）时都会分崩离析。碎片很糟糕。

    但我为这个被困住了。

    遗憾。我希望 SQL Server 在元级别上会更强大。

    • 1

  4. 这不是 SQL Server 故障，也不是 SSMS。

    Excel 通过设计以错误的方式打开 CSV，多年来这一直是一个众所周知的问题。您甚至可以轻松地从 Excel 导出 CSV 文件，然后该文件无法被同一个 Excel 正确打开。

    CSV 和 Excel 的唯一解决方案是不要双击文件或使用 Excel 内部的打开命令（否则它将自行决定破坏它们），而是打开一个空工作表并使用Import-> Import from text。有了它，您可以告诉它如何管理每个不同的列。

    • 0

  5. 我将导出中的 SQL 数据更改为 Excel 公式。Excel Trim() 函数将数字显示为没有科学记数法的数字。不适用于保持前导零。

    由于如果在 TRIM() 函数中使用非数字会爆炸，我必须进行检查：

    '=if(isnumber('+POH.[BEG_03]+'),TRIM('+POH.[BEG_03]+'),"'+POH.[BEG_03]+'")' AS 'TP_PO',

    这是我用来传递客户订单号的公式，可以是字符串或数字。

    在示例中，如果它是一个数字，则公式如下所示：

    =IF(ISNUMBER(100000657618),TRIM(100000657618),"100000657618")
which will look like this in the Spreadsheet: 100000657618

    如果是字符串：

    =if(isnumber(C22686608),TRIM(C22686608),"C22686608"), it will look like this: C22686608

    因此，即使它在 csv 文件中看起来很乱，但在 Excel 中打开时，它看起来是正确的。如果用户不想要电子表格中的公式，则用户可以复制该列并将其作为值粘贴回去。

    • 0

  6. 将 TOAD 用于 SQL Server。免费版包括导出到 excel，即使在试用期结束后也是如此。

    • -2

相关问题