bcp export with spaces in columns

Question

bcp export with spaces in columns

jsheldon

SSCertifiable

Points: 5250
More actions
December 26, 2007 at 1:40 pm

#201953

I have to bcp data from a table to a tab delimited text file.
/****/
SET @PJCT = 'bcp DBSallc.dbo.HOPE_PJCT_LOAD out e:\Allocations\before_proj_jrnl_load\projload.txt -e e:\Allocations\before_proj_jrnl_load\projload.err -c -T -V 80'
EXECUTE master.dbo.xp_cmdshell @PJCT
/*****/
this works fine and creates the file projload.txt. However when I load it into our financial system it doesn't bulk load all the columns only Columns A-E (1-5)
I was pulling my hair out trying to figure out why the remain columns (F - R) were not being loaded...
It is because column F is blank as per the Financial system this column must be blank. If I take the output from bcp and simply open the file in Excel then close out..then it works!! it bulk copies into our financial database...
My question is this...what parameter can I use with bcp to load all columns A-R for all rows of my file? How can I get bcp to recognize that column F is a space?
Currently I use -c -T -V 80 (-c is character data, -T is trusted connection, - V is verify for SQL 2000 compatibility)

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

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

jsheldon SSCertifiable Points: 5250 More actions · Answer 1

Found it... the problem is not bcp or the BULK INSERT or any of that..our antiquated dinosaur of a ERP system likes to see a space for blank columns...I re-did my INSERT statement where '' is now ' ' and all is good!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Thanks for the feedback! Good to know that it wasn't BCP.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)