Can I use bulk insert for this text file?

• adonetok

  SSCrazy Eights

  Points: 8395

  More actions

  December 19, 2014 at 7:11 am

  #314008

  I have a text file and bulk insert code below:

  35*20120527*WQ*1900.38~

  220*20131020*QQ*454~

  BULK INSERT TEST

  FROM 'c:\test.TXT'

  WITH

  (

  FIELDTERMINATOR = '*',

  FIRSTROW = 1,

  ROWTERMINATOR = '~'

  )

  But got an error as below. How to fix it?

  Msg 4832, Level 16, State 1, Line 1

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

  Msg 7399, Level 16, State 1, Line 1

  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 1

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

• Robert Klimes

  SSCoach

  Points: 17865

  More actions

  December 19, 2014 at 9:00 am

  #1765733

  This example works fine for me. My guess is the actual file you are trying to import has some hidden characters which is causing this. See if you can import the file I attached. If this does work for you, you can check for hidden characters with notepad++. is it possible for you to attach you original file?

  Bob
  -----------------------------------------------------------------------------
  How to post to get the best help[/url]

• Lowell

  SSC Guru

  Points: 323506

  More actions

  December 19, 2014 at 9:07 am

  #1765735

  this looks like it's from an EDI file, and the problem with EDI, is fields are optional...i doubt very much that your file has exactly four fields through the whole thing.

  you've probably got some rows with 4 items, and others with 10, so it fails on a row that doesn't match the critieria.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!