BULK INSERT fails with row terminator on last row
Use the following :
BULK INSERT import
from 'D:\tail.csv'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a', FIRSTROW = 1)
GO
feetwet
profile for feetwet on Stack Exchange, a network of free, community-driven Q&A sites http://stackexchange.com/users/flair/3149072.png
Updated on June 04, 2022Comments
-
feetwet almost 2 years
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?