bcp output - integers as characters and strange other characters

Question

bcp output - integers as characters and strange other characters

Robert Mair

SSC Veteran

Points: 216
More actions
June 21, 2006 at 10:52 am

#168855

Hi all,
I'm trying to get bcp to output a CSV file but I'm have a bit of bother.
1. The integers are converted to unicode characters. (I told it they were int)
2. There are strange characters between every letter. In Notepad++ these take the form of black rounded rectangles with white text inside. The text reads BEL, NUL, EOT, STX or FF (there are others but I'm sure you get the idea)
The command string I am using is
bcp {dbname}.dbo.tblUsers out c:\data.csv -S{Server/instance} -U{uname} -P{pword} -T
Can you tell me where I'm going wrong and how to get back on track, please?
Thanks
Robert

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 1

I suggest that you add the the parameters -c (for character) or -w (for unicode), with either you need to add the -t"," to change from the default /t (tab) column delimiter to comma since you are looking for a CSV file.

You also do not need the -U{uname} -P{pword} since you specified -T (SSPI), make sure that the account has permission to SELECT from {dbname}.dbo.tblUsers.

One trick I found for optional double quotes around a field that contains an embedded comma is to change the SELECT expression for those columns to:

CASE WHEN CHARINDEX(',',{field_name}) > 0 THEN QUOTENAME({field_name},'"') ELSE {field_name} END

Since you specified a table as the source you might need to create a view to handle the embedded quote and use it instead of your table in the BCP command line.

Note the paramater '"' is {single quote}+{double quote}+{single quote} characters.

Andy

Robert Mair SSC Veteran Points: 216 More actions · Answer 2

Cheers Andy, that did the trick. Here is the command string I ended up using...

bcp {dbname}.dbo.tblUsers out c:\data.csv -S{Server/instance} -U{uname} -P{pword} -w -t, -r/n

I dropped the -T since the db uses SQLServer authentication, used -w in case we are storing foreign languages (e.g.Cyrilic Mandarin) and although the row terminator is \n by default I put the -r in just to make sure.

Now to get on with the real reason for the bcp operation - bulk insert with the format file that was produced.

Thanks for the inverted commas inserter, I'll keep a hold of that. I am sure I will need it soon. I agree about the views. That is what I will probably use for the actual output method.

You can't input using a view can you?

(I'm 99% sure the answer is no but I might as well ask while I'm on )

Robert

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 3

Robert,

You can insert with a view, if it is for a single table and you grant Insert permission, or if it is a multi-table view you can use instead of triggers in SQL Server 2000, but no instead of trigger in SQL7.

If you need to revert back to SQL7, I suggest using SQL 2000 Backup of your databases, there are some good discussions for this:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=183697

The MS links provided by Rudy Komacsar from:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=259951

Here's everything you need to know for SQL 2000:

http://support.microsoft.com/default.aspx?kbid=314546#top

http://support.microsoft.com/default.aspx?kbid=246133

http://support.microsoft.com/default.aspx?scid=kb;en-us;253817

http://support.microsoft.com/default.aspx?scid=kb;EN-US;240867

http://support.microsoft.com/kb/224071/en-us

http://support.microsoft.com/kb/304692/EN-US/

There is a really good post by devereauxj:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=24&messageid=202168&p=2

Good luck.

Andy