Bulk Insert Unexpected EOF

stanky23
stanky23 used Ask the Experts™
on
I need some help with this problem it's driving me crazy.  I have a Bulk Insert statement which uses a format file.  Everything works fine except the application I'm exporting from creates a eof marker at the end of the text file.  This causes the Bulk Insert in SQL Server to fail with an unexpected end-of-file.   I know it's the eof marker because if I remove it everthing works fine.  I cannot edit the file every time and remove the eof marker because I need to execute this statement several hundred times.  It may not necessarily be an eof marker it may just be a blank line but it shows up in notepad like an eof marker/black square.  Does anybody know of a way for SQL BULK INSERT to deal with this?  
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I'm loading a comma delimited text file not a large image file or word doc.  I saw this article but I don't think it refers to my problem.

Author

Commented:
I'm loading a comma delimited text file not a large image file or word doc.  I saw this article but I don't think it refers to my problem.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

This won't avoid the error, and it will slow down processing, but it SHOULD work.

(1) Create a temporary table to hold the raw data.  One varchar field should be enough.

(2) do a BULK INSERT into this temporary table, with no formatting

(3) Get the # of rows in the table.  A simple:

Declare @ct int
SELECT @ct = Count(*) From MyTempTable

(4) Drop the temp table

(5) Repeat the BULK INSERT with your format file, and set the LASTROW option to equal @Ct - 1 (skipping the terminator at the end of the text file).

Author

Commented:
Inserting into a temp table causes the same error.  The Bulk Insert fails on the last row.  Thus, nothing is loaded and there is no count to get.  

Author

Commented:
My Bad, bhess, I see what you are saying.  Load all the columns from each row into one varchar.  This is a creative work around but I won't be able to use it because it will kill me on time.  I can't afford to load twice.  Maybe I'm asking for the impossible but I want to believe that there is a way to handle this on one load.  I'm surprised no one has run into this before...

Author

Commented:
Inserting into a temp table causes the same error.  The Bulk Insert fails on the last row.  Thus, nothing is loaded and there is no count to get.  
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
do you use the ERRORS flag to ignore that error eventually?

Author

Commented:
I was hoping I would be able to do that but I may be doing something wrong but here's my situation.  I use dynamic SQL to generate the Bulk Insert statement, the use an EXEC (@SQL).  Every time I try to trap the error the unexepected eof error occurs before it and the stream fails.  I'm not sure how this error could be caught and then ignored.  I'm assuming the problem is because the Exec statement causes the dynamic SQL to operate in its own space...
did you try

--drop table t
create table t
(
     line varchar(8000)
)

insert into t execute master.dbo.xp_cmdshell 'type <file_absolute_path>'

--Cheers

Author

Commented:
I actually think I may have found a solution to this problem using Bulk Insert and one load.  In my format file for the last column I use a delimiter ",/r/n" because all the lines in the csv are finished with a comma.  I'm assuming a new line character is generated after all data lines including the last line.  Thus, creating a new and final blank line.  I have tested this a couple of times and it seems to work.  I'm not sure this will work in every situation but I think it might...
stanky23:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
Commented:
PAQed, with points refunded (100)

modulo
Community Support Moderator

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial