Need to export fields containing linebreaks as a CSV from SQL Server

Question

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 

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.

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.

Some ideas:

1. Might it be possible to append quotation marks w/ the SQL?

2. Is there some T-SQL equivalent to the WITH CSV HEADER commands that are possible in other dialects?

Answer 1

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.

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), '') + '"'

If your html value could have double quotes in it, you would need to escape those.