luisdev
luisdev

Reputation: 568

Blank lines in a .csv files created through SSMS :OUT command

I'm using code like this in MSSQL 2008 R2 management Studio to write the results of a SELECT statement to a csv file on a network share:

SET NOCOUNT ON;   
GO    
:OUT \\163.123.45.678\SomeFolder\mydata.csv    
SELECT id, name, surname FROM sometable;    
GO

This creates the mydata.csv file at the correct location but there is an extra blank line at the end of the csv file. How do I prevent that blank line from being created in the csv file?

Is the above the best way to write the output of a sql query to a text file? I can't use BCP.

Thanks.

Upvotes: 5

Views: 3616

Answers (3)

luisdev
luisdev

Reputation: 568

In the end using a SSIS package was a much better solution.

Upvotes: 0

milivojeviCH
milivojeviCH

Reputation: 1608

Can you try the following from the command prompt?

C:\>sqlcmd -S SQLServerHost -W -Q "SET NOCOUNT ON; SELECT TOP 5 date_id, previous_date, next_date FROM dates WHERE month_key = 201212" > testNoLine.txt

The result:

enter image description here

The generated file has no extra line at the bottom (unlike the SSMS execution).

Upvotes: 0

user1931857
user1931857

Reputation: 21

I think you can use SSIS package to get the query result in .CSV format. sometime you may get blank line or space in between two lisne, in that case 'Derived Column' transformation task and remove or trim the unwanted spaces and lines.

Upvotes: 1

Related Questions