Thursday, September 01, 2011

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

I recently encountered a error in a BULK INSERT operation. The BULK INSERT was using a non-xml format file to skip some columns in the csv file and also to map the fields in the csv file with the columns in the table. Of course, I was importing into a temp table that servers as a staging environment. And wwhile doing so I got the foll. error:

Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error.


On further investigation, I figured out that the issue was with the "Number of columns" in the format file. My csv file had 91 columns. The temp table had 104 columns. The format file was used to skip 15 columns. Initially I had "Number of Columns" at 46 (the value was hard coded from earlier days). This caused the "An unexpected end of file" error.

default non-XML format file for myTestSkipCol



While researching this error, this is what I found:
  • When "Number of columns" is less than the actual columns mapped, the error is:
Msg 4832, Level 16, State 1, Line 2Bulk load: An unexpected end of file was encountered in the data file.Msg 7399, Level 16, State 1, Line 2The 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 2Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
  • When "Number of columns" is more than the total number of columns in table or in format file, the error is:
Msg 4823, Level 16, State 1, Line 2Cannot bulk load. Invalid column number in the format file
  • When "Number of columns" is any number between the number of mapped columns and the total number of columns in the table, the BULK INSERT worked fine.
Apart from the above mentioned cause, there are many other causes for the "unexpected end of line" error. Some of these are:

  • In the data file, one or more rows have less columns that the expected number of columns.
  • Wrong field terminator mentioned in format file or the BULK INSERT command
  • Wrong row terminator mentioned in format file or the BULK INSERT command




No comments: