33

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?

John Horton
  • 4,122
  • 6
  • 31
  • 45

9 Answers9

29

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.

Ray
  • 21,485
  • 5
  • 48
  • 64
  • Thanks Ray - that's an interesting idea, though wouldn't that disturb the line breaks w/ in the HTML, which I'll ultimately want to know about? Agreed that CSV is not ideal - is there a way to export XML from SQL Server? That's not something I'm familiar with at all. – John Horton Feb 13 '10 at 23:28
  • Removing the line breaks dows not invalidate the html, though it certainly would make it harder to read. Take a look at the 'for xml' clause, using 'explicit' mode in the sql server help. It's a little weird, but you should be able to get your html into xml pretty easily. – Ray Feb 13 '10 at 23:43
  • You're right, new lines are not important to most HTML. But it would mess up `pre` and `textarea` as an example since line breaks *do* matter to those. Not sure if that matters to you, but it's worth noting in case others are interested in this solution. Cheers! – Eli Gassert Jul 06 '16 at 09:47
  • For an XML export, see **user1490835**'s answer. – Dominique Alexandre Jul 14 '16 at 15:27
  • Thanks very much! This solved my problem. I was having problems when copying SQL Server table results to excel as a result of a string field containing line breaks. – Willy Sep 05 '19 at 17:07
22

If you are using Visual Studio, Server Explorer is an alternative solution. You can correctly copy & paste the results from its grid.

Ufuk Hacıoğulları
  • 37,978
  • 12
  • 114
  • 156
  • 4
    This should **SO** be the correct answer (if you have VS2013 of course, but then if you're a MS stack developer using Sql Server you probably do anyway). Thanks @Ufuk - you saved the day! – theyetiman May 18 '15 at 08:34
  • This saved me so much trouble, I can't thank you enough. I mean, the formatting in Excel is still horrible this way, but it's good enough. – Christian Jun 10 '16 at 10:19
  • Works in current versions of Visual Studio nicely, VS17 for example – Daniel de Zwaan May 02 '17 at 06:59
  • Eight years after this answer and seems like it's still the easiest way to do it... – aterbo May 18 '23 at 05:02
18

According to the closest thing to a standard we have, a correctly formatted CSV-file should quote fields containing either the separator (most often ; or ,) or a linebreak.

SQL Server Management Studio can do that, but amazingly it doesn't have this option enabled by default. To enable, go to Tools → Options → Query Results → Results to Grid and check "Quote strings containing list separators when saving .csv results"

Community
  • 1
  • 1
Zano
  • 2,595
  • 27
  • 33
  • 17
    One note, is that SSMS will qualify a field containing a delimiter or qualifier, but it won't qualify a field that contains line breaks. So in this regard SSMS produces technically invalid CSV files. – Giscard Biamby Jan 05 '13 at 20:45
  • 7
    SSMS for 2008 R2 needs to be restarted for that setting to take effect. – Oliver Oct 30 '13 at 12:45
  • 4
    For this setting to take effect, SSMS doesn't need to be restarted, but it does only take effect with new query windows. – Michael J Swart Jul 28 '15 at 12:53
  • It actually works on sql management studio version 15.0, but it doesnt work well on 12.0 – Qba Aug 10 '21 at 20:52
14

I know how old this is, but I found it and others might, as well. You might want to take Martijn van Hoof's answer one step further and remove possible tabs (char(9)) in addition to carriage return(13) and line feed(10).

SELECT REPLACE(REPLACE(REPLACE(mycolumn, CHAR(9), ''), CHAR(10), ''), CHAR(13), '') as 'mycolumn' FROM mytable
RPh_Coder
  • 833
  • 8
  • 15
  • 1
    I was working with a text field and made some modifications to improve the output results: `REPLACE(replace(replace(cast([fieldname] as nvarchar(max)), char(13),'<|'),char(10),'|>'),CHAR(9),' ') as [fieldname]` The resulting fields will have <||> in place of line breaks and use 4 spaces instead of a tab which can also be treated as a delimiter by Excel. I could then copy the grid into Excel. Last step is doing a find/replace for <||> and pressing Ctrl+J in the replace box to insert a line break. Run the replace and your fields look like they did originally. Text isn't all run together now. – PC_Goldman - SE is rotting Mar 08 '19 at 21:41
8

column with line breaks: "mycolumn"

SELECT REPLACE(REPLACE(mycolumn, CHAR(13), ''), CHAR(10), '') as `mycolumn` FROM mytable

This replaces the linebreaks.

Martijn van Hoof
  • 740
  • 10
  • 28
6

i know this is very old question and has been answered now but this should help as well:

 SELECT html
 From table
 For Xml Auto, Elements, Root('doc')

it should spit out an xml and then you can import that xml into excel

Baahubali
  • 4,604
  • 6
  • 33
  • 72
3

found this useful but I was still hitting problems as my field was type text so I cast the text as varchar(8000) and above replace works like a charm

REPLACE(REPLACE(CAST([TEXT FIELD] AS VARCHAR(8000)), CHAR(10), ' '), CHAR(13), ' ') AS 'Field Name',
DanielBarbarian
  • 5,093
  • 12
  • 35
  • 44
Neil
  • 31
  • 1
3

SQL Server Import and Export Data tool, FTW!

Start -> Programs -> Microsoft SQL Server -> Import and Export Data

Sample query:

select *
from (
select 'Row 1' as [row], 'Commas, commas everywhere,' as [commas], 'line 1
line 2
line 3' as [linebreaks]

union all 

select 'Row 2' as [row], 'Nor any drop to drink,' as [commas], 'line 4
line 5
line 6' as [data]
) a

CSV output:

"row","commas","linebreaks"
"Row 1","Commas, commas everywhere,","line 1
line 2
line 3"
"Row 2","Nor any drop to drink,","line 4
line 5
line 6"

Disclaimer: You may have to get creative with double-quotes in the data. Good luck!

Joe Zamora
  • 186
  • 1
  • 4
  • It also helps with unicode characters that are incorrectly interpreted by Excel when exporting to CSV. – rotman Aug 31 '16 at 15:40
-1

I got around this limitation by creating an Access database, using the "Link to the data source by creating a linked table" feature, opening the "linked" table, then copy/paste to Excel from there. Excel can save the CSV data as needed. You should be able to connect directly from Excel too, but the "linked table" feature in Access let me set up several tables at once pretty quickly.

cbal
  • 1