Blank lines in a .csv files created through :OUT

[luisdev]

I'm using code like this in MSSQL 2008 R2 to pipe the results of a
select statement to a .csv file on a network share:

SET NOCOUNT ON;
GO

:OUT \\163.123.45.678\SomeFolder\myfile.csv

SELECT
id,
name,
surname
FROM
sometable;
GO

This creates the "myfile.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 pipe the output of a sql query to a text
file?

[Erland Sommarskog]

Post by luisdev
I'm using code like this in MSSQL 2008 R2 to pipe the results of a
SET NOCOUNT ON;
GO
:OUT \\163.123.45.678\SomeFolder\myfile.csv
SELECT
id,
name,
surname
FROM
sometable;
GO
This creates the "myfile.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 pipe the output of a sql query to a text
file?

Not really. BCP would be a better choice:

BCP "SELECT id, name, surname FROM db.dbo.sometable" queryout
\\163.123.45.678\SomeFolder\myfile.csv -T -t, -S YOURSERVER

-T is trusted connection. Replace with -U and -P for SQL authentication.

Note that the above is broken over two lines because of the newsgroup
medium. The command must be a single line.

[a***@webmail.co.za]

Post by Erland Sommarskog
BCP "SELECT id, name, surname FROM db.dbo.sometable" queryout
\\163.123.45.678\SomeFolder\myfile.csv -T -t, -S YOURSERVER
-T is trusted connection. Replace with -U and -P for SQL authentication.

Thanks Erland.

Any idea what could be creating the extra blank line at the end of the .csv when I use the file:OUT method that I posted?

[Erland Sommarskog]

Post by a***@webmail.co.za
Any idea what could be creating the extra blank line at the end of the
.csv when I use the file:OUT method that I posted?

That's the blank line you would get before (10 row affected).

SQLCMD is not intended to be an export tool.

[a***@webmail.co.za]

Post by Erland Sommarskog
That's the blank line you would get before (10 row affected).
SQLCMD is not intended to be an export tool.

True!

My MSSQL 2008 R2 table structure is:

CREATE TABLE [dbo].[myusertable](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[username] [nvarchar](100) NOT NULL,
[idnumber] [nvarchar](255) NOT NULL,
[firstname] [nvarchar](100) NOT NULL,
[surtname] [nvarchar](100) NOT NULL,
CONSTRAINT [myusertable_id_pk] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


I've created a BCP solution which seems to do what I want. With this BCP command:

BCP "SELECT id, username, idnumber, firstname, surname + '~' FROM mydatabase.dbo.myusertable" queryout myusers.csv -f bcp.fmt -U dbuser -P dbpass -S dbserver

and this bcp.fmt format file:

10.0
5
1 SQLCHAR 0 1 "~" 1 id ""
2 SQLCHAR 0 100 "~" 2 username SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "~" 3 idnumber SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "~" 4 firstname SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 "\r\n" 5 surname SQL_Latin1_General_CP1_CI_AS

I get the following .csv file:

1~0~abc1212~7813424323082~Adam~Josef~
2~0~efg1234~6702034345548~Andrew~Smith~
3~0~zyx9876~753534544543~Fiona~Freeda~
3~0~ppx1289~6656565646085~Peter~Pan~

That .csv file "looks" perfect - when I open it in Windows Notepad (WinXP SP3) it displays correctly. But when I open it in a real text editor (UltraEdit) it opens as a hex file. What is causing this? Other "normal" .txt files open correctly in UltraEdit.

[a***@webmail.co.za]

Post by a***@webmail.co.za
1~0~abc1212~7813424323082~Adam~Josef~
2~0~efg1234~6702034345548~Andrew~Smith~
3~0~zyx9876~753534544543~Fiona~Freeda~
3~0~ppx1289~6656565646085~Peter~Pan~

Correction, that should be:

1~abc1212~7813424323082~Adam~Josef~
2~efg1234~6702034345548~Andrew~Smith~
3~zyx9876~753534544543~Fiona~Freeda~
4~ppx1289~6656565646085~Peter~Pan~

[Erland Sommarskog]

Post by a***@webmail.co.za
That .csv file "looks" perfect - when I open it in Windows Notepad
(WinXP SP3) it displays correctly. But when I open it in a real text
editor (UltraEdit) it opens as a hex file. What is causing this? Other
"normal" .txt files open correctly in UltraEdit.

Seems like a question for an UltraEdit forum.

I tried your example, albeit with my own data, and the file opened as
a text file in EditPlus.

I don't know about UltraEdit, but both EditPlus and TextPad permits the
user to choose whether to open a file as text or binary. As this is
an important feature, I would be surprised if UltraEdit does not have it.

[luisdev]

Post by Erland Sommarskog
Seems like a question for an UltraEdit forum.

Thanks. But I'm more worried that I've done something wrong when
creating the .csv files via BCP. In other words, that I am creating a
valid .csv file that other users will be able to open.

UltraEdit has the option to "open as ASCII Escaped Unicode". When I
select that option the file opens properly as a "text file" (as
opposed to a hex file). But, what is a "ASCII Escaped Unicode" text
file? And how can BCP create the text file as a "ASCII Escaped
Unicode" file?

[Erland Sommarskog]

Post by luisdev
Thanks. But I'm more worried that I've done something wrong when
creating the .csv files via BCP. In other words, that I am creating a
valid .csv file that other users will be able to open.

What appears strange to me is that you use ~ as a delimiter. For a CSV file
I would expect a comma (that's after all what the C stands for) for files
created in the US and probably a few more countries. Home in Sweden and
in some other European countries, I expect to see a semicolon. But I
note that your address ends in .za, and maybe some South African regional
settings uses ~ as List Separtor? (But when I look in the Control Panel
and try different regional settings for South Africa, they all use comma.)

Post by luisdev
UltraEdit has the option to "open as ASCII Escaped Unicode". When I
select that option the file opens properly as a "text file" (as
opposed to a hex file). But, what is a "ASCII Escaped Unicode" text
file? And how can BCP create the text file as a "ASCII Escaped
Unicode" file?

You will have to ask UltraEdit what they mean with "ASCII Escaped Unicode" -
I have never heard of that term before. (And when I search the term on
Google, the top edits are from www.ultraedit.com.) But maybe they have
some special convention which you happen to mimic.

[rpresser]

Post by Erland Sommarskog
You will have to ask UltraEdit what they mean with "ASCII Escaped Unicode" -
I have never heard of that term before. (And when I search the term on

ASCII Escaped Unicode appears to mean the sort of escaping you customarily
see in XML files: ' for CHR(39), ♥ for a heart symbol. I don't
know whether UltraEdit handles both decimal and hex formats.

http://tools.ietf.org/html/rfc5137#section-5.2