Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Determine ROW that caused "unexpected end of file" error in BULK INSERT?

To locate the troublesome row use the errorfile specifier.

BULK INSERT myData
FROM 'C:\...\...\myData.csv'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
ERRORFILE = 'C:\...\...\myRubbishData.log' 
);

myRubbishData.log will have the offending rows and a companion file myRubbishData.log.txt will give you row numbers and offsets into the file.

Companion file example:

Row 3 File Offset 152 ErrorFile Offset 0 - HRESULT 0x80004005
Row 5 File Offset 268 ErrorFile Offset 60 - HRESULT 0x80004005
Row 7 File Offset 384 ErrorFile Offset 120 - HRESULT 0x80004005
Row 10 File Offset 600 ErrorFile Offset 180 - HRESULT 0x80004005
Row 12 File Offset 827 ErrorFile Offset 301 - HRESULT 0x80004005
Row 13 File Offset 942 ErrorFile Offset 416 - HRESULT 0x80004005

Fun, fun, fun. I haven't found a good way to debug these problems, so I use brute force. That is, the FirstRow and LastRow options are very useful.

Start with LastRow = 2 and keep trying. Load the results into a throw-away table, that you can readily truncate.

And, you should also keep in mind that the first row could be causing you problems as well.


I have a csv file that i import using Bulk

BULK INSERT [Dashboard].[dbo].[3G_Volume]
FROM 'C:\3G_Volume.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '","',
ROWTERMINATOR = '\n'
)
GO

Usually I used this script and it has no problems but in rare occassions.

I encounter this error..

"The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error."

Usually, this happens when the last row have blank values(null).

You need to link your csv file in MS access db to check the data.. (If your csv is not more than 1.4million rows you can open it in excel)

Since my data is around 3million rows I need to use access db.

Then check the number of the last row with blanks and subtract the number of null rows to your total rows for csv.

if you have 2 blank rows at the end and the total number of rows is 30000005 The script will become like this..

BULK
INSERT [Dashboard].[dbo].[3G_Volume]
 FROM 'C:\3G_Volume.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '","',
ROWTERMINATOR = '\n',
Lastrow = 30000003
)
GO

Cheers... Mhelboy


If CHAR(10) is the row terminator, I don't think you can put it in quotes like you are trying to in BULK INSERT. There is an undocumented way to indicate it, though:

ROWTERMINATOR = '0x0A'

Yeah - BULK INSERT would have done will with a bit more detail in its error messages, and the only way around this is to use the brute force approach, as Gordon rightly pointed out. First, though, based on the error you're getting, it is either not understanding your row terminator, or there is a row terminator missing at the end of the file. Using FIRSTROW and LASTROW will help to determine that.

So, you need to do the following:

  1. Check that there is a row terminator at the end of the file. If not, put one in and try again. Also make sure that the last row contains all of the necessary fields. It it says 'EOF', then that is your problem.
  2. Are you sure there's a LF at the end of each line? Try a CR (\n, 0x0D) and see if that works.
  3. Still not working? Try setting LASTROW=2 and try again. Then try LASTROW=3. If you have more than three rows in your file and this step fails, then the row terminator isn't working.

I ran into the same issue. I had written a shell script to create a .csv in Linux. I took this .csv to Windows and tried to bulk load the data. It did not "like" the commas.... Don't ask me why, but I changed to a * as a delimiter in the bulk import and performed a find and replace for comma with * in my .csv .. that worked.. I changed to a ~ as a delimiter, that worked... tab also worked- it didn't like the comma.... Hope this helps someone.