Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.

• Gerry Viator-350055

  SSC Veteran

  Points: 243

  More actions

  June 1, 2007 at 2:41 pm

  #98295

  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 = '\n'

  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\n" 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

• Jeff Moden

  SSC Guru

  Points: 1003851

  More actions

  June 1, 2007 at 4:28 pm

  #710439

  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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  June 1, 2007 at 5:37 pm

  #710447

  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.

• Sinshith S Anand

  SSChasing Mays

  Points: 605

  More actions

  April 12, 2010 at 8:08 am

  #1150069

  Please check this link...all your doubts will be cleared

  http://sinshith.wordpress.com/2010/04/12/bulkinsert-process/