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

how to find conversion error row in sql
try_convert
error in row
sql skip error rows
sql server error row
how to capture error records in sql server
sql convert error handling
get error line in sql server

i am doing a bulk insert:

DECLARE @row_terminator CHAR;
SET @row_terminator = CHAR(10); -- or char(10)

DECLARE @stmt NVARCHAR(2000);
SET @stmt = '
  BULK INSERT accn_errors
   FROM ''F:\FullUnzipped\accn_errors_201205080105.txt''
   WITH 
      (
        firstrow=2,
FIELDTERMINATOR = ''|''  ,
ROWS_PER_BATCH=10000
   ,ROWTERMINATOR='''+@row_terminator+'''
   )'
exec sp_executesql @stmt;

and am getting the following 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. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

is there a way to know on which ROW this error occurred?

i am able to import 10,000,000 rows without a problem and error occurs after that

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

Determine which row is causing error – SQLServerCentral, I have a stored procedure that has several update statements in it and when I run it against a table with 27,000 rows it stops with an error. In other cases where data conversion problems occur, Db2 returns a negative SQLCODE for that row. Regardless of the SQLCODE for the row, no new values are assigned to the host variable or to subsequent variables for that row. Any values that are already assigned to variables remain assigned.

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.

Identify the row / value that caused a conversion / out-of-range error , Identify the row / value that caused a conversion / out-of-range error? – Learn more on the SQLServerCentral forums. Most of the time, if you need a row number or count, you can get it with a click or two. But when you want to use that information in a formula, you need a function. And that’s where the functions ROW and ROWS get into the picture 🙂 ROW gives you the row number of a specific cell. ROWS gives you the number of rows in a range.

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

How can I get the row causing a conversion failure in SQL Server , This will help you figure out what value is causing your error. The example below shows a table with a NULL, a word, and a time stamp. A criminal investigation is underway after the warehouse explosion in downtown Los Angeles that injured 12 firefighters and left several buildings damaged, law enforcement sources said.

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'

MySQL INSERT IGNORE Statement Explained By Examples, to insert rows with valid data into a table while ignoring rows that cause errors. you will find that only one row was actually inserted and the row that causes� Range.Row property (Excel) 05/11/2019; 2 minutes to read +1; In this article. Returns the number of the first row of the first area in the range. Read-only Long. Syntax. expression.Row. expression A variable that represents a Range object. Example. This example sets the row height of every other row on Sheet1 to 4 points.

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.

Transaction Locking and Row Versioning Guide, Missing and double reads caused by row updates every row or page lock on the table to determine if a transaction can lock the entire table. Gas is a normal part of the digestion process. If you're unable to expel gas, you may start to feel pain, discomfort, and bloating. Gas can be a result of certain foods, but sometimes may be

How to correct a #REF! error - Office Support, This happens most often when cells that were referenced by formulas get deleted , or pasted over. Example - #REF! error caused by deleting a column delete a referenced row or column, Excel can't resolve it, so it returns the #REF! error. Ch. 10- 3. Determine the amount of total profit generated by the book purchased on order 1002. Display the book title and profit. The profit should be formatted to display a dollar sign and two decimal places. Take into account that the customer might not pay the full retail price, and each item ordered can involve multiple copies.

MySQL 5.6 Reference Manual :: 14.11 InnoDB Row Formats, The row format of a table determines how its rows are physically stored, which Tables with many BLOB columns could cause B-tree nodes to become too full,� Tillering is important in establishing the yields potential of spring barley (Hordeum vulgare L.). Two field experiments were conducted for 3 yr to test if early season shifts in the red/far‐red ra

Troubleshooting Row Size Too Large Errors with InnoDB, The root cause is that InnoDB has a maximum row size that is roughly The following shell script will read through a MariaDB server to identify every table that� To determine the number of the current row in the result set, an application can call SQLGetStmtAttr with the SQL_ATTR_ROW_NUMBER attribute. The contents of the rows fetched buffer are undefined if SQLFetch or SQLFetchScroll does not return SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, except when SQL_NO_DATA is returned, in which case the value in the

Comments
  • I would suggest it is easier to first try different row terminators. char(10) like that isn't a common one. Try '\n' or '\r\n' - also it might be useful to investigate the program and operating system that is producing the file.
  • @AaronBertrand so it's not possible to identify the row?
  • Not that I know of, no. If you have already ruled out line 2/3 (by setting LastRow as @Gordon suggested), you can use binary division to narrow it down quickly - take the number of lines in the file and set LastRow = <half that number> - if the error still happens, divide it in half and try again. Chances are it's the very first line of data, and also might be caused simply because you haven't matched your ROWTERMINATOR with the actual line terminator in the file. Did you try \n or \r\n yet?
  • Too bad that the Bulk insert does not work in azure. I have to fall back to the bcp command. docs.microsoft.com/nl-nl/azure/sql-database/…
  • @JPHellemons:it works, see my answer here ,fyi only :stackoverflow.com/questions/44065643/…
  • What does the offset mean? how do I use the pointer from HRESULT to correct my problem?
  • Thanks, Артём. I didn't realize you could also handle char(10) that way! It does work, and I should have tested it before commenting.
  • you can still be a good person even if you don't' know about char 10
  • This little trick saved my project. It works from tsql (part of freetds) on the linux command line, where all of the other solutions didn't. Booyah.
  • Thank you for contributing to SO. Question was about finding failing row and your answer does not answer it. Also there is already accepted answer. Probably it makes sense to delete your answer.