none
BCP error: Unexpected EOF encountered in BCP data-file when importing a *.txt file

    Question

  • I try to import a text file to a sql table by BCP on sql 2008 server and login as Adminitrator.

    After I ran    bcp KDMSales.dbo.ADCImport in C:\addacd.txt -f adcfmt.fmt -T -S server2k8   I got errors:

    Starting copy...
    SQLState = S1000, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 10.0]Unexpected EOF encountered in
    BCP data-file

    0 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 1

    The command line I used to generate the format is: 

    bcp KDMSales

    .dbo.ADCImport format nul -T -n -f adcfmt.fmt

    The generated adcfmt.fmt is:

    10.0
    3
    1       SQLCHAR             0       10      "\t"     1     Recordid                     SQL_Latin1_General_CP1_CI_AS
    2       SQLCHAR             0       12      "\t"     2     oldmatchcode                 SQL_Latin1_General_CP1_CI_AS
    3       SQLCHAR             0       12      "\r\n"   3     newmatchcode                 SQL_Latin1_General_CP1_CI_AS

     

    and the table was created by

    CREATE

    TABLE [dbo].[ADCImport](

    [Recordid] [varchar]

    (10) NULL,

    [oldmatchcode] [varchar]

    (12) NULL,

    [newmatchcode] [varchar]

    (12) NULL

    )

    ON [PRIMARY]

    The text file addacd.txt (25k rows) looks like:

    17242     24451HN6082A           
    17243             0000000           
    17244     02139SK6002L           
    17245             0000000           
    17246     03836SO6217P03836SO6130P
    17247     06073ML6045H06073SE7045H
    17248     03031SA4489P03281SA4065B

    There is no any identity column in the table, and I tried creating a test table by not Null, char...I also created a test text file, and tried by using -U and -P in BCP command line.....none of these work. The server I used is on a 64bit operating system,  then we processed the exported text file to a 32bit server and imported it back to the sql table on 64 bit server.   I was able to export another sql table to a text file by BCP with a file format on 64 bit server.   I wonder how I failed with BCP importing....is it something to do with two different servers?  I'm convering the BCP from server 2000 (on another server) to server 2008 then will use it in a SSIS package.  I did check many sites for the solutions but no luck....

    Your help is highly appreciated.  

     

     


    Kathy
    Thursday, March 10, 2011 3:08 PM

Answers

  • Hi,

    I don’t think we can use BCP to generate a format file by using the existing fixed file. The following link describes how to use the BCP utility to create a format file for a particular table. Please see:
    Creating a Format File
    http://msdn.microsoft.com/en-us/library/ms191516.aspx

    Thanks,
    Ai-Hua Qiu


    Ai-hua Qiu[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Ai-hua Qiu Sunday, March 20, 2011 9:24 AM
    Tuesday, March 15, 2011 7:13 AM
  • Hi Ai-hua,

     Your reply "I don’t think we can use BCP to generate a format file by using the existing fixed file" answered my question.  Thank you.  

    According to your link, it looks like I'm on the right track by using:

    bcp KDMSales.dbo.ADCImport format nul -c -f adcfmt.fmt -T

    In this case, as long as I have a text file I should use bcp with c switch to create a format file for charater data (B option in your link).....since my file is a fixed text file so I should add t switch with delimiter "" to overide the default "\t"....the following command line works for me: 

    bcp KDMSales.dbo.ADCImport format nul -c -t "" -f adcfmt.fmt -T

    It looks like we need know in advance what kind of delimiter in the import text file before we can use the BCP to generate the format file.....


    Kathy
    • Marked as answer by Ai-hua Qiu Sunday, March 20, 2011 9:24 AM
    Tuesday, March 15, 2011 10:30 PM

All replies

  • try with

     bcp KDMSales.dbo.ADCImport in C:\\addacd.txt -f adcfmt.fmt -T -S server2k8 


    If you can't fix something try with bigger hammer
    Thursday, March 10, 2011 3:16 PM
  • Thank you, qoranzadro for the reply....my one is on the local c drive,  it doesn't work when I use C:\\....as you suggested.

    Denis, thank you too for the link.   Actually I read this link before but didn't get anything out from it.   This time I read it again, and it leads me solved the problem :-)  

    What happend is my text file is not a tab delimited file and it is a fix file.      The BCP that I used to generate the file format provided me a tab delimited format and I took it by granted that the text file is a tab delimited file since I exported it as a tab delimited file and expected to be returned with a same type of text file.   Perhaps I should add a parameter (?) in the BCP file format command to specify for the fix file format instead of default tab delimited file.  I wonder what I did wrong here:   

    bcp KDMSales

    .dbo.ADCImport format nul -c -f adcfmt.fmt -T

    Anyway after I read your link I paid attention to the text file and its format.... we found out it's not a tab delimited file but a fix file and we revised the format file manully by replacinge "\t" to "".   It works now.    Thank you! 

     


    Kathy
    Thursday, March 10, 2011 8:16 PM
  •  

    The following bcp command line generated a correct format file (adcfmt.fmt) for my import fixed text file (addacd.txt):

    bcp KDMSales

    .dbo.ADCImport format add.txt -S server2k8 -T -f adcfmt.fmt -c -t ""

    PS: switch t specifies the field delimiter and add.txt is an empty output file

    perhpas there is a better way to do it.....so far this is one that I could find.   Is it possible to use BCP to generate a format file by using the existing fixed (or any text) file instead of a sql table ? 

     

     


    Kathy
    Thursday, March 10, 2011 10:00 PM
  • Hi,

    I don’t think we can use BCP to generate a format file by using the existing fixed file. The following link describes how to use the BCP utility to create a format file for a particular table. Please see:
    Creating a Format File
    http://msdn.microsoft.com/en-us/library/ms191516.aspx

    Thanks,
    Ai-Hua Qiu


    Ai-hua Qiu[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Ai-hua Qiu Sunday, March 20, 2011 9:24 AM
    Tuesday, March 15, 2011 7:13 AM
  • Hi Ai-hua,

     Your reply "I don’t think we can use BCP to generate a format file by using the existing fixed file" answered my question.  Thank you.  

    According to your link, it looks like I'm on the right track by using:

    bcp KDMSales.dbo.ADCImport format nul -c -f adcfmt.fmt -T

    In this case, as long as I have a text file I should use bcp with c switch to create a format file for charater data (B option in your link).....since my file is a fixed text file so I should add t switch with delimiter "" to overide the default "\t"....the following command line works for me: 

    bcp KDMSales.dbo.ADCImport format nul -c -t "" -f adcfmt.fmt -T

    It looks like we need know in advance what kind of delimiter in the import text file before we can use the BCP to generate the format file.....


    Kathy
    • Marked as answer by Ai-hua Qiu Sunday, March 20, 2011 9:24 AM
    Tuesday, March 15, 2011 10:30 PM