Post reply

bcp error on NULL: "Unexpected EOF encountered in BCP data-file"

  • February 5, 2020 at 3:05 pm

    #3723327 

    IF OBJECT_ID('table1') IS NOT NULL
    BEGIN
      DROP TABLE table1;
    END;
    GO
    CREATE TABLE dbo.table1(
    column_id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    column_txt varchar(3) NULL
    );

     

    format file table1.fmt:

    11.0

    2

    1 SQLCHAR 0 12 "|\"" 1 sup_id ""

    2 SQLCHAR 0 3 "\"\r\n" 2 sup_name_fr SQL_Latin1_General_CP1_CI_AS

     

    data file data_file.txt:

    1|"abc"

    2|

    bcp command: bcp "TEST.dbo.table1" in data_file.txt -f table1.fmt -T -E

    Starting copy...

    SQLState = S1000, NativeError = 0

    Error = [Microsoft][SQL Server Native Client 11.0]Unexpected EOF encountered in BCP data-file

    1 rows copied.

    How to insert the second row with bcp?

    Thank you

    Attachments:
    You must be logged in to view attached files.

  • February 5, 2020 at 8:16 pm

    #3723434

    Hi Dmitriy.  I worked with your example a little today, but I'm short on time.  The problem may be that bcp is not handling the NULL properly based on your command.

  • February 6, 2020 at 12:39 am

    #3723481

    You BCP format file says that the delimiter for the end of the 1st field should start with a pipe and end with a quote.  It doesn't for the 2nd row.  The delimiter for the end of the 2nd filed says that it must end with a quote and then a CrLF combo.  It doesn't.  And so it doesn't find the delimiter for the 2nd field of the second row before it hits the end of the file and that's why it gives you the error it did.

     

    A possible work around would to be to NOT define the quotes as a part of the delimiters and simply remove them after the data is imported.

     

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

  • February 6, 2020 at 2:32 pm

    #3723639

    Jeff Moden wrote:

    You BCP format file says that the delimiter for the end of the 1st field should start with a pipe and end with a quote.  It doesn't for the 2nd row.  The delimiter for the end of the 2nd filed says that it must end with a quote and then a CrLF combo.  It doesn't.  And so it doesn't find the delimiter for the 2nd field of the second row before it hits the end of the file and that's why it gives you the error it did.

    A possible work around would to be to NOT define the quotes as a part of the delimiters and simply remove them after the data is imported.

    1. We must use quotes for varchar fields. There are description columns in the database and sometime users put field separator character in the text.
    2. How bcp can process NULL? For example  bcp "SELECT 1 AS column_id, NULL AS column_txt" queryout data_file2.txt -T -c -t"|"

    You can see 2 columns in the select statement and in the output file is only 1|

  • February 6, 2020 at 3:31 pm

    #3723655

    Looks like it is old bug in bcp command.

    https://github.com/MicrosoftDocs/sql-docs/issues/2689

     

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

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