SQL Server - unexpected EOF encountered in BCP data-file

Asked By Chris on 11-Jun-07 01:17 PM
I keep getting this error when trying to import from a text file.
I've saved the file in tab-deliminated *.txt format, in CSV format, and
nothing seems to work.

an example of a row of data is...
49	GCMD	4	2007	Act	Total Business
Deprctn	1563	1595	1577	1550	418	0	0	0	0	0	0	0

What should I be looking at?




Chris replied on 11-Jun-07 02:21 PM
I userd the bcp format option to create the format file. checking the number
of columns and the values for the columns, everything seems to be right, so
what's the problem? I've included a copy from the command lines...

C:\>bcp dbo.GCG in C:\tmp\gcg\gcg1a.txt  -f C:\tmp\gcg\format_b
cp.fmt -UmyUserName -SmyServer\dbInstance
Password:

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

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1
Erland Sommarskog replied on 14-Jun-07 02:42 AM
Chris G. (ChrisG@) writes:

Please post the CREATE TABLE statement for the target table, and the
format file. Without that information it's possible to say.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Chris replied on 12-Jun-07 08:01 AM
Erland,
Thank you for your interest. Here's the CREATE statement...
USE [GM_DB]
GO
/****** Object:  Table [dbo].[GM]    Script Date: 06/12/2007 07:43:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GM](
[GM_ID] [uniqueidentifier] NULL,
[Source] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DataMonth] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DataYear] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Measure] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BusinessUnit] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LineItem] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Jan] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Feb] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Mar] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Apr] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[May] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Jun] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Jul] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Aug] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Sep] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Oct] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Nov] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Dec] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

*******************************************************
and here's the format file's contents...
9.0
19
1       SQLCHAR       0       37      "\t"     1     GCMD_ID
2       SQLCHAR       0       100     "\t"     2     Source
SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       7       "\t"     3     DataMonth
4       SQLCHAR       0       7       "\t"     4     DataYear
5       SQLCHAR       0       100     "\t"     5     Measure
SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR       0       100     "\t"     6     BusinessUnit
SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR       0       0       "\t"     7     LineItem
SQL_Latin1_General_CP1_CI_AS
8       SQLCHAR       0       21      "\t"     8     Jan
9       SQLCHAR       0       21      "\t"     9     Feb
10      SQLCHAR       0       21      "\t"     10    Mar
11      SQLCHAR       0       21      "\t"     11    Apr
12      SQLCHAR       0       21      "\t"     12    May
13      SQLCHAR       0       21      "\t"     13    Jun
14      SQLCHAR       0       21      "\t"     14    Jul
15      SQLCHAR       0       21      "\t"     15    Aug
16      SQLCHAR       0       21      "\t"     16    Sep
17      SQLCHAR       0       21      "\t"     17    Oct
18      SQLCHAR       0       21      "\t"     18    Nov
19      SQLCHAR       0       21      "\r\n"   19    Dec

******************end format file contents*****************
Chris replied on 12-Jun-07 08:11 AM
bcp was choking on the "uniqueidentifier" data type. Changing this to
something else solved the problem.

Thanks again for your interest!

CG.
Erland Sommarskog replied on 14-Jun-07 02:43 AM
Chris G. (ChrisG@) writes:

Looking at your sample data file, uniqueidentifier certainly looks like
the wrong type. But I would expect a conversion error in such case,
rather than "Unexpected EOF". But BCP is not always apparent.

I'm glad to hear that you were able to get it to work!



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx