Using BCP and formatting Data

• weldy

  SSC-Addicted

  Points: 410

  More actions

  October 30, 2002 at 8:33 pm

  #158858

  I've recently exported into a txt file using BCP. I used the following:

  exec master..xp_cmdshell 'bcp "select * from acsvapp..XExportTable" queryout "c:\testing.txt" -t, -c'

  I got the result:

  000396 ,718 ,Bayer ,2001-01-10

  Is there any way to get rid off the spaces by trimming? Or change the format date to 20020110 by getting rid of the "-"? I need to separate by quotations as well. Thanks for any advice!

• Oleg D

  SSC Veteran

  Points: 279

  More actions

  October 31, 2002 at 9:28 am

  #439938

  I guess the easiest way would be to create a view or SQL statement which trims all those spaces and formats date like you want.

  After that specify it like a source for bcp.

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  November 1, 2002 at 7:53 am

  #440046

  I agree with Oleg.

  I do not why you get spaces, I do not (SQL 7) unless it is part of your data.

  If you want to format the data then you will have to do this in TSQL.

  For non datetime columns

  '"'+rtrim(convert(varchar,field1))+'"'

  For datetime

  '"'+left(replace(convert(varchar,date1,120),'-',''),8)

  Far away is close at hand in the images of elsewhere.
  Anon.