Post reply

bcp import data from text file to sql server table - german accent

  • October 14, 2017 at 5:46 am

    #329163

    I have a text file with german data which I want to bcp using format file to sql server table.

    CREATE TABLE [dbo].[germandata](
        [name] [varchar](13) NULL,
        [ID] [varchar](5) NULL
    ) ON [PRIMARY]

    This is my format file
    13.0
    3
    1   SQLCHAR     0   24  ""   0  ignorecolumn         SQL_Latin1_General_CP1_CI_AS
    2   SQLCHAR     0   13  ""   1  name         SQL_Latin1_General_CP1_CI_AS
    3   SQLCHAR     0   5   "\r\n" 2  ID          SQL_Latin1_General_CP1_CI_AS

    This is my text file

    UG (haftungsbeschränkt) Föhrenweg 1a 83064
    UG (haftungsbeschrankt) Fohrenweg 1a 83065

    note: I will attach text file to this post also.

    I used this command to bcp
    bcp.exe "[mydb].[dbo].[germandata]" in "C:\german.txt" -f "C:\german.fmt" -T -S "mydatabasename\dev01" -e "C:\error.log" -o "C:\output.log" -C ACP

    Secong row with ID=83065 got inserted. for 1st row I get an error in error.log
    #@ Row 1, Column 3: String data, right truncation @#

    I am not quite sure how to handle this?

  • October 14, 2017 at 6:39 pm

    #1963709

    Change the length of the 3rd field in the BCP Format File from 5 to 7.  The reason is that you're trying to import by fixed field length, which also means that you're counting every character including the 2 end of line characters.

    Everything else looks perfect provided that your server default collation is the same as what you've listed in the format file.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 14, 2017 at 6:42 pm

    #1963710

    p.s.  You do realize that you don't need to use BCP just because you're using a format file, correct?  BULK INSERT in T-SQL will work just fine if you don't mind a bit of dynamic SQL.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 16, 2017 at 2:58 am

    #1963768

    I changed the length of the last field to 7. It helped me get in all rows that had only english characters correctly.  So I increased the size of 1st column and tried to import that column too. Looks like the width of that column is more 
    Here's what I did
    CREATE TABLE [dbo].[germandata](
      [name] [nvarchar](38) collate Latin1_General_CI_AS,
      [ID] [varchar](7) NULL
    ) ON [PRIMARY]
    I changed the collation to match that of database.
    13.0
    2
    1 SQLCHAR  0 37 "" 1 name   SQL_Latin1_General_CP1_CI_AI
    2 SQLCHAR  0 7 "\r\n" 2 ID    SQL_Latin1_General_CP1_CI_AI

    This is what got inserted into table

    INSERT INTO dbo.germandata('UG (haftungsbeschränkt) Föhrenweg 1',' a 83064');
    INSERT INTO dbo.germandata('UG (haftungsbeschrankt) Fohrenweg 1a '  ,'83065');

    Row#1 has german data and 
    For row #1 ,  ID has 'a 83064'.  Not sure how to handle this shift of characters.

    Thanks
    rashMR

  • October 16, 2017 at 6:21 am

    #1963798

    rash3554 - Monday, October 16, 2017 2:58 AM

    I changed the length of the last field to 7. It helped me get in all rows that had only english characters correctly.  So I increased the size of 1st column and tried to import that column too. Looks like the width of that column is more 
    Here's what I did
    CREATE TABLE [dbo].[germandata](
      [name] [nvarchar](38) collate Latin1_General_CI_AS,
      [ID] [varchar](7) NULL
    ) ON [PRIMARY]
    I changed the collation to match that of database.
    13.0
    2
    1 SQLCHAR  0 37 "" 1 name   SQL_Latin1_General_CP1_CI_AI
    2 SQLCHAR  0 7 "\r\n" 2 ID    SQL_Latin1_General_CP1_CI_AI

    This is what got inserted into table

    INSERT INTO dbo.germandata('UG (haftungsbeschränkt) Föhrenweg 1',' a 83064');
    INSERT INTO dbo.germandata('UG (haftungsbeschrankt) Fohrenweg 1a '  ,'83065');

    Row#1 has german data and 
    For row #1 ,  ID has 'a 83064'.  Not sure how to handle this shift of characters.

    Thanks
    rashMR

     You changed the collation to something that isn't going to work for you.  You need to have the collation both in the table and in the format file as "SQL_Latin1_General_CP1_CI_AS".  The "AS" in that is "Accent Sensitive" and you need that, in this case.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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