CODEWITHSUNDEEP
sql-servercsvbulkinsert
feetwet
feetwet

Reputation: 3446

BULK INSERT fails with row terminator on last row

I'm importing a CSV compiled using cygwin shell commands into MS SQL 2014 using:

BULK INSERT import
from 'D:\tail.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\r', FIRSTROW = 1)
GO

I have confirmed that each row contains a \r\n. If I leave a CR/LF on the last row the bulk import fails with Msg 4832:

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

If I end the file at the end of the last data row then the bulk import succeeds.

For very large CSVs a kludgy way around this problem is to find the number of rows and use the LASTROW setting for the BULK INSERT. Is there a more graceful way to let MS SQL know that, yes, the last row is terminated and not to complain about it or fail on it?

Upvotes: 2

Views: 12038

Answers (1)

Hamdi
Hamdi

Reputation: 41

Use the following : 

BULK INSERT import
from 'D:\tail.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a', FIRSTROW = 1)
GO

Upvotes: 4

Related Questions