SQL电子邮件转CSV，结果存在行拆分问题 [英] SQL Email to CSV, Results have Line Splitting issues

问题描述

该问题类似于我发现的以下问题:换行或回车会导致将SQL网格结果复制到Excel时出现行拆分问题，您的结果将被切断并在下一行开始.

This question is similar to several questions I've found where Line Feeds or carriage returns cause copying SQL grid results to Excel to have Line Splitting issues and your results will be cut off and started on the next row.

Name     Order#    Date        PartDescription     Store      Phone#
----     -------   ------      ----------------    -----      -----------
Tom's    3181      10/7/2017   SB4356 Artwork...   Downtown   208.452.6354

取而代之的是这样

Name     Order#    Date        PartDescription     Store      Phone#
----     -------   ------      ----------------    -----      -----------
Tom's    3181      10/7/2017   SB4356 Artwork...   Downt   
own      2080.452.6354

但是，当我将SQL网格结果复制到Excel时，不会发生此问题.当我将SQL作为动态sql运行并将结果通过CSV通过电子邮件发送给我时，对我来说会出现此问题.我不确定这有什么区别.可以将问题隔离到零件描述"字段，该字段通常是100多个用户输入的字符.如果我删除该字段，则不会发生.

However, this problem does not occur when I copy the SQL grid results to Excel. This problem occurs for me when I run the SQL as dynamic sql and email the results to myself via CSV. I'm not sure what the difference here is. The issue can be isolated to the Part Description field, its usually 100+ user-entered characters. If I remove that field this doesn't happen.

我尝试做:

LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE( PartDescription, CHAR(13), ''), CHAR(10), ' '), CHAR(9), ' '), ',', ' ')))

并尝试了较小的步骤，但没有任何效果.就像我说的，它可以将结果复制到Excel.当我通过电子邮件将其发送到CSV时，情况并非如此.我真的不太确定如何找出可能导致这种情况的独特字符.

and tried it in smaller steps and it hasn't had any effect. Like I said, it works copying the results to Excel. Just not when I email it to CSV. I'm not really sure how to isolate what inivisible charatcers might be causing this (if it is that).

我过去曾使用此方法来修复其他自动报告.这个人很固执. SQL在SQL Server代理中动态运行，然后以CSV格式将结果通过电子邮件发送出去.我还尝试将SQL保存在视图中，然后在动态SQL中调用该视图只是出于娱乐目的，没有更改.

I've used this method to fix others auto-reports in the past. This one is being stubborn. The SQL runs dynamically in SQL Server Agent, which then emails the results out in a csv format. I also tried saving the SQL in a view and called the view in dynamic SQL just for fun, no change.

推荐答案

遇到相同的问题，结果发现它遇到255个字符时是换行符.我想如果您的PartDescription字段包含许多字符，您可能会遇到相同的问题.假设您使用的是sp_send_dbmail，则添加以下选项:

Had the same issue, turns out it was line wrapping when it hit 255 characters. I'm thinking you may have the same issue if your PartDescription field has many characters. Assuming you are using sp_send_dbmail, if you add the option:

@query_result_width=500

或您需要的任何宽度，都应将其修复.

or whatever width you require, it should fix it.

这篇关于SQL电子邮件转CSV，结果存在行拆分问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！