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

Answer 1

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

Answer 2

http://social.msdn.microsoft.com/Forums/en/sqltools/thread/d31db86a-4b11-4b41-b64f-0179e64625c5

Answer 3

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

Answer 4

 

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

Answer 5

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.

Answer 6

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