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
  • Is the format file saved in the same code-page as the source file? – Max Vernon Jun 1 '17 at 13:06
  • not sure buddy. I have created format file by below command. bcp.exe [Table_Name] format nul -f [Format_File_Path] -c –t -U [username] -P [password] -S [Server] – Jenish Zinzuvadiya Jun 1 '17 at 13:15
  • You used [Format_File_Path] twice. The first one should be the [DATA_FILE] and the second one should be the [Format_File_Path]. I guess you were overwriting the format file. – John aka hot2use Jun 1 '17 at 13:19
  • Sorry that was my spell mistake, 1st is: [Flat_File_Path] and 2nd [Format_File_Path] – Jenish Zinzuvadiya Jun 1 '17 at 13:27
  • 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 '17 at 15:25
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).

|improve this answer|||||
  • 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. – Robert Carnegie Jun 1 '17 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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