bulk insert eof error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

bulk insert eof error

Author

Topic

matty1stop
Starting Member

37 Posts

Posted - 2007-09-06 : 11:50:12

I am attempting to bulk insert a comma delimited text file with double quotes as the text qualifier but I keep getting an error message(EOF) on the bulk insert.

I think the problem lies in my format file (see below)

Please take a look and let me know what I am missing?

Thanks,
Matt

Error message:
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)".

Format File:
8.0
19
1 SQLCHAR 0 0 "\"" 0 first_quote SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 0 "\",\"" 1 nt_id SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "\",\"" 2 first_name SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 0 "\",\"" 3 last_name SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 0 "\",\"" 4 department SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 0 "\",\"" 5 phone SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 0 "\",\"" 6 mgmt_level SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 0 "\",\"" 7 emp_id SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 0 "\",\"" 8 rc SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 0 "\",\"" 9 subrc SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 0 "\",\"" 10 location SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 0 "\",\"" 11 floor SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 0 "\",\"" 12 supervisor_id SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 0 "\",\"" 13 status SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 0 "\",\"" 14 hiredate SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 0 "\",\"" 15 jobtitle SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 0 "\",\"" 16 paygrade SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 0 "\",\"" 17 id SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 0 "\"\r\n" 18 email SQL_Latin1_General_CP1_CI_AS

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 12:00:15

"An unexpected end of file was encountered in the data file"

Is there a Control-Z embedded in the data file?

I think something like this came up just the other day, so might be worth a search for "BCP" within the last 4 weeks in case you can find the thread

Kristen

sbalaji
Starting Member

48 Posts

Posted - 2007-09-06 : 12:18:20

Problem lies in the file...
The first and last record is not having comma delimiter,Try to insert a comma,in both record and try
It will be better if u can post the query what u r trying.

matty1stop
Starting Member

37 Posts

Posted - 2007-09-06 : 12:32:55

Sample line of file to be imported:
"","Joe","Mooney","Accounting","","9","JM1234","1234","A","","","MB99999","A","04/30/2007","Associate","","JM1234","me@here.com"

SQL Statement:
Bulk Insert mytable From '\\myserver\myshare\myfile.dat'
WITH (FORMATFILE = '\\myserver\myshare\formatfile.fmt')

sbalaji
Starting Member

48 Posts

Posted - 2007-09-06 : 13:05:36

All the above errors u have mentioned in the first post will be thrown,only if the terminator is not available in the file record.

Check the u r format file,'\\myserver\myshare\formatfile.fmt' for the terminators

matty1stop
Starting Member

37 Posts

Posted - 2007-09-06 : 13:54:15

The format file is included in my original post. As I mentioned I believe it may contain an error but I don't know what it is. That is why I was asking for some help.

Kristen
Test

22859 Posts

Posted - 2007-09-06 : 15:02:03

Is there a line break on the last line of the format file? (I don't believe this should be important, so just belt-and-braces question really)

matty1stop
Starting Member

37 Posts

Posted - 2007-09-06 : 15:39:27

There is a new line at the end of the file. Do you know any way of converting the unreadable characters (say to hex or decimal) in a text file so I can be sure what they are?

matty1stop
Starting Member

37 Posts

Posted - 2007-09-06 : 15:52:32

I tried changing the last line of the format file to:
19 SQLCHAR 0 0 "\"\n" 18 email SQL_Latin1_General_CP1_CI_AS

and got this error message:
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 02:51:56

Do you know that there aren't any "\r" at the line ends?

That suggests that a value in the file is too long for the target column. Try extracting the first few lines of the import file to a new file, and importing that.

If that works the problem is somewhere within the file.

You could make a staging table with much wider columns (set them all to varchar(8000) perhaps?) and importing to that, then looking at

SELECT MAX(LEN(Col1)), MAX(LEN(Col12), ...

to see which ones have over-size rows, and then something like

SELECT *
FROM MyTable
WHERE LEN(Col1) > 123 -- Whatever the actual limit is

to see which rows are at fault

Or split the file into two halves, try processing each half. Any that fail split into two again. Eventually you will have some files just containing the failures. This isn't going to work if there are lots of rows with duff data!

Kristen

matty1stop
Starting Member

37 Posts

Posted - 2007-09-07 : 13:32:58

Arghhh! The last field of the last record did not contain the " text qualifier.

Sorry to have bothered you. That's a day I won't get back.

Kristen
Test

22859 Posts

Posted - 2007-09-07 : 14:50:25

Glad you got it sorted, a right PITA those types of issues.

And why can't SQL provide a better error?

"The incorrect record is on line 1234, here is a copy of it: xxxxxx,yyyyy,..."

would be just dandy.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources