"Unexpected EOF encountered in BCP data file"

Question

Hi,

 

 I have a question.

 

I'm performing some bulk import/export using the bcp(8.0) utility, but there is a problem when working with it using a format file(.fmt file) .

 

I executed the following command:

bcp tablename in mydatfile.dat -f myformatfile.fmt -e myerrorfile.err -S myserver\myinstance

 

And I got the following error:

Password:

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP dat
a-file

1 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total       16

 

I had compared the format file, dat file and the table design also. All are matching.

The format file is as follows:

4.2
24
1       SYBCHAR       0       2       ""       1       field1
2       SYBCHAR       0       2       ""       2       field2
3       SYBCHAR       0       12      ""       3       field3
4       SYBCHAR       0       2       ""       4       field4
5       SYBCHAR       0       4       ""       5       field5
6       SYBCHAR       0       4       ""       6       field6
7       SYBCHAR       0       11      ""       7       field7
8       SYBCHAR       0       15      ""       8       field8
9       SYBCHAR       0       3       ""       9       field9
10      SYBCHAR       0       3       ""       10      field10
11      SYBCHAR       0       1       ""       11      field11
12      SYBCHAR       0       1       ""       12      field12
13      SYBCHAR       0       6       ""       13      field13
14      SYBCHAR       0       2       ""       14      field14
15      SYBCHAR       0       5       ""       15      field15
16      SYBCHAR       0       5       ""       16      field16
17      SYBCHAR       0       2       ""       17      field17
18      SYBCHAR       0       1       ""       18      field18
19      SYBCHAR       0       9       ""       19      field19
20      SYBCHAR       0       1       ""       20      field20
21      SYBCHAR       0       5       ""       21      field21
22      SYBCHAR       0       2       ""       22      field22
23      SYBCHAR       0       7       ""       23      field23
24      SYBCHAR       0       23      "\r\n"   24      field24

Please help me to understand and fix the problem.

 

Thanks in advance.

Answer 1

Make sure that there are no hidden control characters or unintentional space anywhere in the input file. Use a hex editor to inspect the data.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 2

Thanks for your response.

 

Here is the whole data in the data-file:

 

63200884411341913D0252000001286000000               JI800000165758DS2002500000  N000004320+05                                  Z

 

 

Answer 3

>> unexpected EOF encountered in BCP data-file

 

Dont worry,, Me too facing the same problem.

 

You chek for [uniqueidentifier] may be wrong in create table.

 

Cheers!

 

 

Answer 4

2+2+12+2+4+4+11+15+3+3+1+1+6+2+5+5+2+1+9+1+5+2+7+23=128

The data line provided in you other post is also 128 chars, so no problem there, the only thing that is left that could be wrong is that the carriage return \r or linefeed \n that may be missing. This may be an issue with data populated on a Un*x system into a text file. My bet: one or more control chars are missing or reversed order.  

Also I believe that even if you have just a single line in the file the CRLF (\r\n) also needs to be present otherwise it reaches the EOF marker unexpectedly

Answer 5

Hi, Me to face the same problem.. while coping the data from txt file to the table,  any idea how to resolve this issue

Answer 6

Did you add a CR and LF at the end of the last record. If you have one record then make sure its present

Answer 7

There are around 108 rows in the inpout file,  below is the error received while coping BCP in the table

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

108 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 110    Average : (981.82 rows per sec.)

FMT file is as below, in the input file only first 4 rows,

 

8.0
6
1       SQLCHAR       0       3       "\t"     1     filed1               SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       16      "\t"     2     field2              SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       1       "\t"     3     filed3               SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       255     "\r\n"   4   field4              SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR       0       0       ""       0     field5              SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR       0       0       ""       0     field6              ""

Please help..

 

Answer 8

If you don't want to import into 4,5,6 then why do you include the in the format file? If you don't have them in the data file then remove them and reduce the number near the top(6 -> 4).  If you have data for thiese fields in the datafile, but want to skip over them then don't remove the lines, only specifiy the size (you have 0 now, which looks wrong) and keep target column to 0 (you've already set the target to 0). Make sure your data file DOES NOT contain the CRLF, EXCEPT to delimit records. If you have it internally to a block of data (like the 255 char block) then your import will skew. having a CRLF somewhere other than at the end of a record is a very common problem

Answer 9

Hey, Issue got resolved.. Somehow space was added in the input file. After removing the space. BCP IN was successful.