BUL INSERT WITH FILEFORMAT

• Ram:)

  SSCrazy

  Points: 2246

  More actions

  November 3, 2010 at 11:12 am

  #228717

  Hi,

  Can you please anybody help me to resolve the following error..

  I have attached Data file and Format file.

  Query is

  CREATE TABLE TEST

  (Client VARCHAR(100),[Site_Ref_No] VARCHAR(100),SITE VARCHAR(200),

  [Address 1] VARCHAR(200),[Address 2] VARCHAR(200),[Address 3] VARCHAR(200),

  City VARCHAR(200),State VARCHAR(200) ,[Zip Code] VARCHAR(200),

  Country VARCHAR(200),Account VARCHAR(200),[Account id] VARCHAR(200),

  Vendor VARCHAR(200),[Account Type] VARCHAR(200),[Group Bill] VARCHAR(200)

  )

  BULK INSERT TEST FROM 'C:\TEST\DATA.TXT'

  WITH

  (

  FORMATFILE='C:\TEST\FORMAT.TXT',

  FIRSTROW = 1

  )

  SELECT * FROM TEST

  DROP TABLE TEST

  I am getting the following error...

  Msg 4832, Level 16, State 1, Line 9

  Bulk load: An unexpected end of file was encountered in the data file.

  Msg 7399, Level 16, State 1, Line 9

  The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

  Msg 7330, Level 16, State 2, Line 9

  Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

  Regards

  Ram...

  🙂

• GSquared

  SSC Guru

  Points: 260824

  More actions

  November 3, 2010 at 11:14 am

  #1246181

  I've seen that in text files with blank lines in them. Try cleaning that up and see if it helps.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• Ram:)

  SSCrazy

  Points: 2246

  More actions

  November 3, 2010 at 11:22 am

  #1246187

  I tried but Getting the same issue..

  Here I felt the issue is with the terminators in the formatfile to import an integer value..

  🙂

• steve-893342

  SSCarpal Tunnel

  Points: 4439

  More actions

  November 3, 2010 at 2:34 pm

  #1246287

  Try this format file

  7.0

  13

  1SQLCHAR00"\""0Quote

  2SQLCHAR00"\",\""1Client

  3SQLCHAR00"\",\""2Site_Ref_No

  4SQLCHAR00"\",\""3Site

  5SQLCHAR00"\",\""4Address_1

  6SQLCHAR00"\",\""5Address_2

  7SQLCHAR00"\",\""6Address_3

  8SQLCHAR00"\",\""7City

  9SQLCHAR00"\",\""8State

  10SQLCHAR00"\",\""9Zip_Code

  11SQLCHAR00"\",\""10Country

  12SQLCHAR00"\","11Account

  13SQLCHAR00"\r\n"12Account_id

• Ram:)

  SSCrazy

  Points: 2246

  More actions

  November 3, 2010 at 4:45 pm

  #1246331

  Thanks Steve..

  It works for me..

  Regards

  Ram...

  🙂