BCP out

  • 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

  • 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?

  • 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

  • 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

  • Hi Steve,

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

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

  • 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

  • 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?

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

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

    update trlc

    set weight = ' '

    where weight = ''

  • 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.

  • 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.

  • 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

  • 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.

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

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