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]

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.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[and...@webmail.co.za]

On Wednesday, November 28, 2012 5:44:22 PM UTC+2, Erland Sommarskog wrote:
>
> 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.

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]

(and...@webmail.co.za) writes:
> 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.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[and...@webmail.co.za]

On Nov 29, 12:19 am, Erland Sommarskog <esq...@sommarskog.se> wrote:

> (and...@webmail.co.za) writes:
>
> 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.

[and...@webmail.co.za]

> 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~

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]

(and...@webmail.co.za) writes:
> 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]

On Nov 29, 11:34 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
>
> 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]

luisdev (and...@webmail.co.za) writes:
> 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.)

> 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]

On Friday, November 30, 2012 3:54:01 PM UTC-5, Erland Sommarskog wrote:
> 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: &#39; for CHR(39), &#9829; 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