Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    4

    Question Unanswered: BCP puts nulls in output text file

    Hi,

    I have been trying to output varchar fields from a table to a text file using BCP. When the field in the table is an empty string (where I have specifically set it to '') BCP places an ascii null in the output text file.

    Is there a way I can tell it to just not place anything in the output for this field.

    Other inportant information: I am trying to use comma as the field terminator (-t,). If I use the default tab terminator, then things seem to be ok.

    Thanks for any help.
    Scott

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    What does your bcp command look like?

    with -c it should be OK.

    from
    create table bcp (s varchar(10), t varchar(10) null, u varchar(10) not null, v varchar(10))
    insert bcp select 'asd', 'asd', 'asd', 'asd'
    insert bcp select 'asd', '', '', 'asd'
    insert bcp select 'asd', 'asd', 'asd', 'asd'
    exec master..xp_cmdshell 'bcp test..bcp out c:\bcpfile.txt -S(local) -T -t, -c'

    I get
    asd,asd,asd,asd
    asd, , ,asd
    asd,asd,asd,asd

  3. #3
    Join Date
    Feb 2003
    Posts
    4
    The command I used was very similar to yours and when I ran yours, I got the same results you did. However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.

    If I allow nulls in the table, then the output file contains a null (ASCII 0) in that spot between the commas.

    Now, if I use DTS to output the file, then everything comes out as I want it to. I just wanted to avoid the use of DTS for something so simple.

    Thanks for your help.
    Scott

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    However, notice that where the fields in the table are empty, bcp puts a space between the commas (, ,). What I would like to see is just (,,) with no space between.
    Actually the fields are NOT empty, they contained a zero length string. This is not the same as empty. The ASCII 0 or (, ,) is a zero length string, if you insert a NULL then your file will only contain (,,).
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •