Post reply

BCP with special characters

  • August 4, 2008 at 8:14 am

    #190441

    Hi,

    I am trying to upload file having special characters (Turkish).

    The column contains data like -> aşağıdaki

    When I run bcp, it gets converted to -> asagidaki

    format of the input file is unicode.

    1. What changes do i need to do in format file to get actual value in database?

    2. Are there any links I can refer for - uploading data from various languages (french, Greek, Turkish) in database?

    I have also tried -w option, but i get an error -

    Error = [Microsoft][SQL Native Client]Invalid character value for cast specification

    Really need help on this..

    Thanks!

  • August 4, 2008 at 10:02 am

    #852768

    does your database's collation support the characters?

  • August 5, 2008 at 8:48 am

    #853230

    I guess yes, collation is SQL_Latin1_General_CP1_CI_AS which is database default.

    I'll explain the current situation.

    This is my table ->

    CREATE TABLE [dbo].[Emp](

    [EmpID] [int] NOT NULL,

    [EmpName] [nvarchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

    ) ON [PRIMARY]

    This is my file to upload->

    asagi1

    anotheruser2

    user33

    (Notice that EmpName appears before EmpID in input file.)

    This is my format file ->

    9.0

    2

    1 SQLNCHAR 0 800 "\t\0" 2 EmpName SQL_Latin1_General_CP1_CI_AS

    2 SQLINT 0 12 "\r\0" 1 EmpID ""

    When I run command ->

    bcp database1.dbo.Emp in c:\EmpData.txt -f c:\EmpFMT.fmt -S dbserver -U sa -P pass

    This is what I get in database ->

    EmpIDEmpName

    852017?asagi

    Unicode characters are preserved but,

    There are following issues ->

    1. I get only one record instead of all three.

    2. Only record inserted has wrong EmpID. (EmpID is NOT identity/primary key)

    I wonder whats going wrong here.

    Please help.

  • August 20, 2008 at 11:22 am

    #860791

    Have you resolved this issue ? I am experiencing a similar situation with french characters

    SQL Server 2005

    SQL_Latin1_General_CP1_CI_AS (DB Collation)

    SQL_Latin1_General_CP1_CI_AS (Column Collation, NVARCHAR(40) )

    same BCP Error with different values sometimes reaching the table.

  • August 21, 2008 at 12:19 am

    #861037

    Yes , the issue seems to be resolved, but in a crude way.

    First of all in the example i stated, we need to change format file like this - \r\0\0 instead of \r\0 for the last column of the line. -

    9.0

    2

    1 SQLNCHAR 0 800 "\t\0" 2 EmpName SQL_Latin1_General_CP1_CI_AS

    2 SQLINT 0 12 "\r\0\0" 1 EmpID ""

    Now all the records will get inserted, but first one has an extra character prefixed to it.

    I guess that it is because of first two bytes of unicode file which spoil the first column of first row.

    So I changed the source file to add a dummy 1st row (which is same as current first row of file) , so now I have duplicated first row of the source file.

    Then append -F2 to command so that bcp will start from line 2.

    After this, everything seems to work fine.

    I have tested this a couple of times and still testing it for various scenarios.

    I hope this helps.. Please let me know if there is any other alternative.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply