BCP out

Question

Post reply

BCP out

newbieuser

SSCrazy Eights

Points: 9423
More actions
March 7, 2011 at 8:39 am

#388992

Hi all,
I ran bcp out from sql server database for the table below:
TRLC table:
est_no varchar(10) default ' '
right_no int default 0
maj_auth varchar(15) default ' '
weight varchar(10) default ' '
idm_ht varchar(8) default ' '
c_date datetime
P_tkt varchar(5) default ' '
bcp db1.dbo.trlc out d:\trlc.csv -c -t "|" -sServer1 -T
Record from TRLC.csv:
11032|100|Wman|||2008-02-08||
I don't see a ' ' space for weight,idm_ht and p_tkt in the CSV file.. Please help me here.
Thanks

Viewing 14 posts - 1 through 13 (of 13 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

I ran this:

CREATE TABLE TRLC

(

est_no varchar(10) default ' '

, right_no int default 0

, maj_auth varchar(15) default ' '

, [weight] varchar(10) default ' '

, idm_ht varchar(8) default ' '

, c_date datetime

, P_tkt varchar(5) default ' '

)

GO

INSERT TRLC (est_no, right_no, maj_auth, c_date)

VALUES( 11032, 100, 'Wman', '20080208')

INSERT TRLC (est_no, right_no, maj_auth, c_date)

VALUES( 11034, 101, 'Test', '20080209')

SELECT * FROM TRLC

Then this from the command line:

C:\Users\Steve>bcp db1.dbo.trlc out c:\Users\Steve\Documents\trlc.csv -c -t "|"

-S SevenFalls -T

If I open the file in a text editor, there are spaces there. It looks funny in Excel, but I assume there are spaces there. How are you examining the file?

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 2

You are right, Steve. I can see the space in text editor.. I need to import this table into Oracle database I'm using SQl Loader:

TRLC.ctl:

load data

infile 'trlc.csv'

replace

into table trlc

fields TERMINATED BY '|'

TRAILING NULLCOLS

(est_no,right_no,maj_auth,weight,idm_ht,c_date,P_tkt)

sqlldr user/pwd@db02 control=trlc.ctl log=trlc.log

When I do a select in SQL Server,'select len(weight) from trlc;' , I get the length as 0. But when I do a select in oracle database, I get the length as 1.

Also, the result set varies for the query below:

select * from trlc where weight=' ';

(SQL Server returns 1 row but Oracle returns no rows)

Any suggestions please?

THanks

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 3

LEN ignores trailing spaces. A single space is a trailing space

http://msdn.microsoft.com/en-us/library/ms190329%28v=SQL.100%29.aspx

Use DataLength instead.

http://msdn.microsoft.com/en-us/library/ms173486%28v=SQL.100%29.aspx

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 4

Hi Steve,

Both 'select len(weight) from trlc' & select datalength(weight) from trlc' return 0 in SQL server..

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 5

They work for me. If they are returning 0, then you have 0 bytes, no space in the field/row.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 6

Thanks Steve. But if there is a space in the csv file, then isn't a space considered as a byte?

Record from TRLC.csv:

11032|100|Wman| | |2008-02-08| |

In the SQL Server database, when I run this:

select len(weight) from trlc; - Query returns 0 -which means 0 bytes..

But, when I run the below query:

select count(*) from trlc where weight = ' '; - Query returns 1 row.. How is it possible?

I guess this is the reason why I'm not getting the same results when I import in Oracle.. Any suggestions?

thanks

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 7

space is considered a byte.

Is this 0?

select datalength(weight) from trlc;

Is there more than one row, so you should be getting a whole series of values here. Are they all 0?

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 8

There is 15627 rows and select datalength(weight) from trlc - query returns 0 for all the rows..

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 9

Then something went wrong with your import somewhere and the spaces were lost. Can you not do this?

update trlc

set weight = ' '

where weight = ''

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 10

Yes I can run the update for this particular field.. But I'm importing a few more tables from SQL Server to Oracle & they have lot more character fields with the default as ' '. When I import using sql loader the data looks incorrect with some weird character.. Could it be the -c option that I'm using in bcp is causing the problem?

Thanks a lot.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 11

Possible that -c is causing an issue. I wouldn't expect that, but a quick test ought to fix it.

However if the data is wrong in the source tables, the bcp out doesn't matter. You won't get the correct data. If I understand this, in SQL Server, you don't have the spaces in the table. The default works on inserts, but it doesn't affect updates, or pre-existing rows. If you bcp out these fields, the default does not apply.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 12

I agree. But if the length is 0 for trlc.weight in SQL Server, it makes me wonder how this "select * from trlc where weight= ' '; - will return rows?? Also, in the bcp out text file I do see a space for this field...

Thanks

PhilPacha Hall of Fame Points: 3645 More actions · Answer 13

If ANSI_PADDING was set to 'OFF' when the table was created, trailing spaces are removed from varchar columns when values are inserted or updated. This also applies to varbinary columns and trailing binary(0) bytes.

A table can have mixed characteristics, as well. The ANSI_PADDING setting in effect when a column is added to a table can be different from the setting when the table was originally created. Therefore, the columns would be handled differently, depending upon the ANSI_PADDING setting.

I've been bitten by this more than once.