Determine Row That Caused "Unexpected End of File" Error in Bulk Insert

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

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

You need the parameter KEEPIDENTITY in your bulk insert statement. This is required to retain identity values in the load.

BULK INSERT Clients2 FROM 'c:\Sample_Clients.txt'
    WITH ( KEEPIDENTITY, FIELDTERMINATOR = ',', ROWTERMINATOR = '\r\n'
)

I also think you will have a problem because you have no data or placeholder for the Notes column. A comma added to the end of the file should address this.

BULK INSERT error with unknown cause

Can you try to do a SAVE as on that file and see if the Encoding of the file is anything other than ANSI ? Me personally I have never had to use anything other than the standard '\n' ROWTERMINATOR . The '0x0A' sounds strange.

Edit :

Try these and see if any one of them succeeds:

1. SELECT A.* FROM
   OPENROWSET(BULK 'C:\source.txt', SINGLE_CLOB ) AS A

2. import using bcp and see if you get the same error

3. play with BATCH_SIZE , ROWS_PER_BATCH . Please go thru this link before you try as there are implications. https://technet.microsoft.com/en-us/library/ms188267(v=sql.105).aspx

4. Try importing from the Export/Import Wizard ( Right Click on DB --> Tasks --> Import and go thru the screens ). See if this works.

To try inserting using OPENROWSET

insert into dbo.Table SELECT A.* FROM OPENROWSET(BULK 'C:\source.txt', FORMATFILE='C:\source.Xml'

) as A

Now you need to generate the formatfile Source.xml using instructions from here --> https://msdn.microsoft.com/en-us/library/ms191516.aspx

bcp is a command line tool that ships with SQL Server. Unlike BULK Insert which runs from SSMS Query window this is a external tool. Plenty of help available on how to use it for formatfile generation (or otherwise) on that link above. Goodluck I will check back later.

---

Related Topics