Exporting from Varchar(Max) field to csv file adds odd characters

• OldCursor

  SSC Eights!

  Points: 936

  More actions

  February 5, 2009 at 3:37 am

  #126198

  I have a sql 2005 db used to archive hundreds of csv files that i store in a varchar(max) field. I've written a sproc to get a csv out again to the file system using the following code:

  SET @cmd = 'bcp "SELECT Document FROM CSV_Archive.dbo.DocStore WHERE DocID ='+ CAST(@DocID AS VARCHAR)+'" queryout '+@oFile+' -S -T -N';

  EXEC master..xp_cmdshell @cmd;

  The resulting file contains some additional characters at the begining of the file - like so:

  Ü‚NUL NUL NUL NUL NUL NUL.

  Then the rest of the file is as it should be.

  Any idea how I can avoid getting these unwanted extra characters?

• Matija Lah

  SSCertifiable

  Points: 6429

  More actions

  March 6, 2009 at 4:11 am

  #954746

  The -N switch means that character data will be exported as Unicode (Unicode files begin with the declaration that you've noticed).

  If you don't use any ANSI extended characters you can omit this switch.

  Alternatively, you could try using the -w switch and see if that makes any difference.

  ML

  ---
  Matija Lah, SQL Server MVP
  http://milambda.blogspot.com