SQL Server Management Studio data extract with tabs

Question

One one of my projects I am currently working with Microsoft SQL Server Management Studio and came across the issue when saving file in .csv format. In particular some of the columns that I am extracting are of data type nvarchar and includes multiple lines within 1 cell.

When exporting data into a .csv file, Excel automatically drops the line no matter which delimiter I select (,, tab, or |). I assume this is due to CHAR(13) and/or CHAR(10) being present within some of the cells.

What already works for me is replacing CHAR(10)/CHAR(13) with some other characters but that is too much of the intervention into data integrity.

I was able to get what I need in SSRS tool, however given the large number of data i am wondering if there is a way to set up SQL Server Management Studio so it would "lock" cell contain and in case cell contains multiple lines/rows, it would still be shown in one cell.

I have tried a few articles on here and a few other resources but non of them seem to be answering this specific question.

Any help would be highly appreciated.

In the notepad it does not seem to be dropping the line, but when copied from notepad into excel it does drop the line again. — Sky21.86, Aug 24 '15 at 13:28

score 1 · Answer 1 · edited May 23 '17 at 10:26

1

When you export the data from SSMS, putting quotes around the data from every string column will cause the data between delimiters to be treated as a single field of data.

In SSMS there's an option for this, in Tools -> Options -> Query Results -> SQL Server -> Results to Grid, it's called "Quote strings containing list separators when saving .csv results".

Sorry the above does not work for newline characters. I read a SO here which had some useful suggestions.

The most relevant being:

for a one of need, just cut an paste from SSMS to excel.
Use SSIS if you want to do it in a more automated way.

edited May 23 '17 at 10:26

Community

1
1

answered Aug 24 '15 at 13:45

HarveyFrench

4,440
4
20
36

Thanks for your reply, Harvey. I had this option ticked initially so unless i need to do something specific after i re tick it, it does not work for me for some reason. – Sky21.86 Aug 24 '15 at 13:55
When you open the file with notepad, are the string fields surrounded by " ? – HarveyFrench Aug 24 '15 at 14:02
Nope. Could that be because file is exported as .csv not .txt? – Sky21.86 Aug 24 '15 at 14:04
see extra bit to my answer above – HarveyFrench Aug 24 '15 at 14:22

SQL Server Management Studio data extract with tabs

1 Answers1