Search code examples
sql-serverunicodecharacter-encodingbcp

character encoding issue with the BCP and ó

I have a file that needs to go to Poland. In my data I have an ó. When I output the data to a table the ó is still there as the field is NVARCHAR. When I cut and paste from the table into Excel or Notepad++ the ó stays

When I use the BCP utility to export the data, the ó is changed to a ¢ if I open the resulting text file in Excel or Notepad++. If I change the encoding sometimes the character becomes a ˘.

I have tried the following command line switches, one at a time. -n -N -w

Solution

  • Have you looked into -C?

    -C { ACP | OEM | RAW | code_page }

    Specifies the code page of the data in the data file. code_page is
    relevant only if the data contains char, varchar, or text columns
    with character values greater than 127 or less than 32.

    Given your table's data type is Unicode (NVARCHAR), you also have to use -c (which may lose some Unicode characters)

    -c
    Performs the operation using a character data type. This option
    does not prompt for each field; it uses char as the storage type,
    without prefixes and with \t (tab character) as the field
    separator and \r\n (newline character) as the row terminator. -c
    is not compatible with -w.

    For more information, see [Use Character Format to Import or
    Export Data (SQL Server)].

    CREATE TABLE [dbo].[Test] (
    [test] [nvarchar](100) NOT NULL
);  

INSERT [dbo].[Test] ([test]) VALUES (N'helló wórld');

    bcp dbo.Test out dbo.Test.dat -c -T -C ACP

    -w without -c and -C ACP should also work as long as Notepad++ can detect the encoding (UCS-2 Little Endian). I had no problem with my simple example using Notepad++ 6.7.7.

    Notepad++

    Source: https://msdn.microsoft.com/en-us/library/ms162802.aspx