Bulk insert not recognizing end of file
-
I have data in input file given below --> 1,abc 2,xyz ------- End of data---- Notice i have an empty rows after second row. I m doing bulk insert with FIELDTERMINATOR = ',',ROWTERMINATOR = '\n'. It works perfectly fine when bulk inserting after removing the empty records. But when there is empty record at end of file, it is throwing error given below and no records are inserted in db. I cannot ask the user to remove empty line at EOF. 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)".
-
I have data in input file given below --> 1,abc 2,xyz ------- End of data---- Notice i have an empty rows after second row. I m doing bulk insert with FIELDTERMINATOR = ',',ROWTERMINATOR = '\n'. It works perfectly fine when bulk inserting after removing the empty records. But when there is empty record at end of file, it is throwing error given below and no records are inserted in db. I cannot ask the user to remove empty line at EOF. 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)".
searching for solution..... wrote:
I cannot ask the user to remove empty line at EOF.
Then you'll have to write something that removes that line for the user. As the example, I'd expect it to be invalid, as an empty row would consist of field-separators and a row-separator. Like this:
1,abc
2,xyz
,An empty line does not equal an empty row. It equals a row without fields.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]