Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    492

    Unanswered: bcp out empty column

    I'm bcp-ing a table to a file. I've noticed that if a varchar column is empty, that both "bcp -c" and "bcp -c -k" export the empty value as #$00. Instead, I'd like to export the value as if it were null.

    Code:
    use monkey
    go
    
    create table tab1 (
      myColumn varchar(10) null
    )
    go
    
    insert into tab1 (myColumn) values ('')
    go
    
    exec master..xp_cmdshell 'bcp monkey..tab1 out D:\data.csv -c -T'
    go
    
    drop table tab1
    go
    I've been fiddling about with other switches but so far haven't come up with a solution other than writing out the full SELECT-statement or updating the original. Anyone with an easier solution?

  2. #2
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    try changing the extn of file to '.txt' and check it, its working fine for me using the command prompt as well as query analyser.

    try as
    exec master..xp_cmdshell 'bcp <db-name>.<table-owner>.tab1 out D:\data.csv -c -T'
    or
    exec master..xp_cmdshell 'bcp <db-name>.<table-owner>.tab1 out D:\data.txt -c -T'

    cheers,
    pavan.

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    Changing the file's extension didn't do it for me, perhaps it's an OS or SQLServer configuration issue? It's a Windows 2000 Server running SQL2000. I'm not sure where to start.

    I don't have the issue with the analyzer either, only when exporting to a file.

  4. #4
    Join Date
    Mar 2003
    Location
    Reading, UK
    Posts
    70
    Mine is windows 2000 professional runnung sqlserver 2000.
    I dont think thats the problem.

    Try the same in others machine,
    i had a similar problem for exports and Imports of oracle,they never worked in my colleagues PC and worked fine from my pc..

Posting Permissions

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