0

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
3
  • 1
    FYI - a "column delimiter" and a "text qualifier" are different things - in a CSV file, the comma is the column delimiter. Frequently, if comma is used inside some data fields, they'll be in double quotes, in which case the double quote is the "text qualifier".
    – RDFozz
    Jun 1, 2017 at 15:25
  • Just to ask the obvious - you do have a \r\n at the end of the file, right (can't tell based on the sample data)?
    – RDFozz
    Jun 1, 2017 at 15:26
  • @RDFozz Sorry That was my explanation mistake, I have file separated by a comma (,) and text qualified by quotes (") that work fine. but when I use special char þ as a qualifier and special char [DC4] as separator then it fails. Please see this format file. github.com/jenish1993/MyShares/blob/master/formatfile.fmt This file has separator. Jun 2, 2017 at 5:15

1 Answer 1

1

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).

1
  • 1
    The "pipe" symbol has some popularity in this role. As a side-thought, maybe related, I find it not easy to get BCP to do things that I want done. SSIS is friendlier to you and to your data. Fiddly to run on demand. Jun 1, 2017 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.