Wednesday 31 July 2013

1.05 - Bulk Insert Row Terminators Gotcha

I recently stumbled across this issue when a dev asked me to have a look at a file that he was attempting to import. Basically, the bulk import or the bcp statement was not recognising the row terminator and appending all the data beyond the last column into that very same last column. Annoying, to say the least.
After some researching, I found that this is quite a common problem and most people suggest dynamically building the statement and that seems to work.
e.g.
DECLARE @Cmd NVARCHAR(MAX) = 'BULK INSERT [TableName] FROM ''[InputFile]'' WITH (FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''' +CHAR(10) +''')'

EXEC SP_executeSQL @cmd

However, I am not a fan of dynamic sql and tried to find out if it can be done without and ....woohoo! It can be - You just have to use the hex values for the row terminator and then the World falls back into a sensible orbit and life makes sense again.
e.g.
BULK INSERT [TableName] FROM '[InputFile]' WITH (FIELDTERMINATOR = '|', ROWTERMINATOR = '0xa')

I hope this helps someone out there as it saved me a lot of time after I sussed it.