Search code examples
sqlsql-servercsvbcp

bcp output from SQL Server replaces € symbol with an underscore

Hi Perhaps someone can shed some light on this for me , using a sql query i get a list of supplier invoice details and then using bcp output to a csv file for importing into an ERP system, with the results of this query there is some supplier codes that contain the € sign but when the csv file is created the € sign is replaced with _

the € sign appears fine in SQL server but not in the csv file below is my output command

set @bcpCommand = 'bcp "select Supplier_Code,DocType,Invoice_No,Description,Net_Total,Vat_Total,Total from [MyDatabase].[dbo].tempPurchInv order by Invoice_No asc" queryout  '+@filename+ '.csv' +' '+ '-c -t, -T -S' +' '+ @@SERVERNAME

exec master..xp_cmdshell @bcpCommand
Solution

  • If you don't specify a code page, the OEM code page is used for char/varchar columns. Try adding -C RAW to the BCP command, or a code page that supports the € symbol:

    SET @bcpCommand = 'bcp "select Supplier_Code,DocType,Invoice_No,Description,Net_Total,Vat_Total,Total from [MyDatabase].[dbo].tempPurchInv order by Invoice_No asc" queryout  '+@filename+ '.csv' +' '+ '-c -t, -T -C RAW -S' +' '+ @@SERVERNAME;

EXEC master..xp_cmdshell @bcpCommand;

    EDIT:

    Be aware the consuming system will need to know the code page of the non-Unicode file. With the RAW specification, that will be the code page of the column collation.