Sql-server – Sql server bulk insert remove text qualifier

bcpbulk-insertsql server

I have a flat file having hundred thousand of records separated by special kind of character, I have used format file for removing that text qualifier. Works fine with comma separated file.

Note: I have changed column name and data for privacy.

My format file is as below.
demo format file path:
https://github.com/jenish1993/MyShares/blob/master/formatfile.fmt

12.0
    20
    1       SQLNCHAR            0       0       "þ"          1     Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    2       SQLNCHAR            0       0       "þþ"      2     Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    3       SQLNCHAR            0       0       "þþ"      3     Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    4       SQLNCHAR            0       0       "þþ"      4     Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    5       SQLNCHAR            0       0       "þþ"      5     Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    6       SQLNCHAR            0       0       "þþ"      6     Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    7       SQLNCHAR            0       0       "þþ"      7     Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    8       SQLNCHAR            0       0       "þþ"      8     Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    9       SQLNCHAR            0       0       "þþ"      9     Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    10      SQLNCHAR            0       0       "þþ"      10    Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    11      SQLNCHAR            0       0       "þþ"      11    Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    12      SQLNCHAR            0       0       "þþ"      12    Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    13      SQLNCHAR            0       0       "þþ"      13    Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    14      SQLNCHAR            0       0       "þþ"      14    Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    15      SQLNCHAR            0       0       "þþ"      15    Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    16      SQLNCHAR            0       0       "þþ"      16    Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    17      SQLNCHAR            0       0       "þþ"      17    Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    18      SQLNCHAR            0       0       "þþ"      18    Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    19      SQLNCHAR            0       0       "þþ"      19    Column_Name_Here                       SQL_Latin1_General_CP1_CI_AS
    20      SQLNCHAR            0       0       "þ\r\n"       20   Column_Name_Here                        SQL_Latin1_General_CP1_CI_AS

My Data file as below.

þColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_HereþþColumn_Name_Hereþ
þData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_Hereþ
þData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_Hereþ
þData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_Hereþ
þData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_Hereþ
þData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_Hereþ
þData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_Hereþ
þData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_Hereþ
þData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_Hereþ
þData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_Hereþ
þData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_HereþþData_Hereþ

My Command as below.

bcp.exe [Table_Name_Here] IN [Flat_File_Path] -f  [Format_File_Path] -F2 -k -C ACP –U [Username] -P [Password] -S [Server]

Bcp always fail for given command. gives below error.

Unexpected EOF encountered in BCP data-file

Best Answer

If this works with a comma but not with þþ, then the problem can be the character you are using. The issue, I'm guessing, is that þ (thorn) matches th in most Collations. Try something that is not a letter in some language (such as thorn þ) and not likely to exist in the data. I sometimes use a character with a value of 28, 29, 30, or 31 as those should be the same across all code pages and are designed to be "separators" (for fields, etc).

Related Question