Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bulk Insert: Unexpected end-of-file (EOF) encountered in data file. Expand / Collapse
Author
Message
Posted Friday, June 1, 2007 2:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 8:43 AM
Points: 1, Visits: 39
Hi all,

Trying to bulk insert a csv file from a bcp command generated file but, get
this error:

Server: Msg 4839, Level 16, State 1, Line 3
Cannot perform bulk insert. Invalid collation name for source column 45 in
format file '\\MyServer\drive_e\temp\usage.fmt'.

First Column in Distination table is identity column and the table already
contains many rows. See below for DDL.

Bulk Insert Statment:

BULK INSERT Mytable FROM '\\MyServer\drive_e\temp\usage.csv'
     WITH (DATAFILETYPE = 'Native',
           TABLOCK,
           BATCHSIZE = 100,
    FORMATFILE = '\\MyServer\drive_e\temp\usage.fmt',
    ROWTERMINATOR = ''


This is the format file:

8.0
45
1 SQLINT 0 12 "/t" 2 UsageID SQL_Latin1_General_CP1_CI_AS
.......
.......
43 SQLCHAR 1 10 "/t" 44 api_BillDate SQL_Latin1_General_CP1_CI_AS
44 SQLCHAR 1 50 "/t" 45 api_FileName SQL_Latin1_General_CP1_CI_AS
45 SQLCHAR 1 10 "\r" 46 AcctCode_new SQL_Latin1_General_CP1_CI_AS


this is the distination table:

CREATE TABLE [Mytable] (
 [UsageRowID]int IDENTITY(1,1) NOT NULL ,
 [UsageID]int NOT NULL,
........
........
 [api_BillDate]char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [api_FileName]char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 [AcctCode_new]varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


This is the bcp command that created the csv file:

DECLARE @bcpCommand VARCHAR(2000)
SET @bcpCommand = 'bcp "SELECT ..,...,..,.,...,... from Sometable" queryout
E:\temp\usage.csv -SMyServer -T -n -b 100'

EXECUTE xp_cmdshell @bcpCommand
go


thanks
gv

Post #370793
Posted Friday, June 1, 2007 4:28 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:11 PM
Points: 35,777, Visits: 32,450

1.  Don't use anything but SQLCHAR when importing text files.
2.  Don't use anything but "" for coalation on non-character based columns.



--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #370811
Posted Friday, June 1, 2007 5:37 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 2:36 PM
Points: 2,850, Visits: 1,165

Does the last line of your format file have a trailing CR/LF?  Since it is complaining about the last field of the last line, I think this might be the problem.  The last line should be terminated with a CR/LF, the file should not just end after the collation field.




Post #370819
Posted Monday, April 12, 2010 8:08 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 3, 2014 9:37 PM
Points: 11, Visits: 151
Please check this link...all your doubts will be cleared

http://sinshith.wordpress.com/2010/04/12/bulkinsert-process/
Post #901640
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse