需要从SQL Server导出包含换行符作为CSV的字段 [英] Need to export fields containing linebreaks as a CSV from SQL Server
问题描述
我正在从SQL Server Management Studio 2005运行一个查询，其HTML文件存储为aa字符串，例如：
I'm running a query from SQL Server Management Studio 2005 that has an HTML file stored as a a string, e.g.:
SELECT html
FROM table
这个查询很好地显示在 ，每行包含一个整个HTML文件的字符串，每行一个记录。
This query displays in the "Results" window nicely, i.e., each row contains a string of the whole HTML file, with one record per row.
但是，当我使用文件结果选项时，会将其导出为无法使用的CSV在字段中发生换行符（即，在HTML中），而不是根据需要每行一个。我尝试使用查询>查询选项为网格和文本结果，没有效果。导出到CSV 不的字段似乎包含在引号内。
However, when I use the "Results to file" option, it exports it as an unusable CSV with line breaks in the CSV occurring wherever line breaks occurred in the field (i.e., in the HTML), rather than one per row as needed. I tried experimenting with the Query>Query Options for both the "Grid" and "Text" results, to no avail. The fields exported to the CSV do not appear to be enclosed within quotes.
一些想法：
-
b $ b引号标记w / SQL？
Might it be possible to append quotation marks w/ the SQL?
是否有一些T-SQL等价于
WITH CSV HEADER命令，
可能在其他方言？
Is there some T-SQL equivalent to the WITH CSV HEADER commands that are possible in other dialects?
推荐答案
我不知道你会在哪里成功导出html到csv - 真的不是什么csv是为了。你最好使用xml格式，其中html代码可以包含在一个cdata元素中。
I don't see where you will have much success exporting html to csv - it's really not what csv is meant for. You would be better off using an xml format, where the html code can be enclosed in a cdata element.
也就是说，你可以尝试使用Replace函数来删除换行符，您可以手动添加引号 - 类似这样：
That said, you could try using the Replace function to remove the line breaks, and you could manually add the quotes - something like this:
select '"' + replace (replace (html, char(10), ''), char(13), '') + '"'
如果你的html值可以有双引号，你需要转义那些。
If your html value could have double quotes in it, you would need to escape those.
这篇关于需要从SQL Server导出包含换行符作为CSV的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
