question

Tom Thomson avatar image
Tom Thomson asked

bulk insert row terminator

This is a curiosity question, not something that is holding me up, because I have a perfectly good workaround. But I would be surprised to find that bulk import in SQL 2008 is as inflexible on row terminators as it appears to be.

Is there any straightforward way of getting Bulk Insert to recognise a single LF character as row terminator? According to BoL http://msdn.microsoft.com/en-us/library/ms188365.aspx the default is '\r\n', but ROWTERMINATOR = '\n' doesn't do the trick, it still expects '\r\n'. Bol muddies the waters somewhat on page http://msdn.microsoft.com/en-us/library/ms191485.aspx by saying that \n (newline character) is the default terminator, and that \r is the escape for CRLF, and of course the first reference says that \r\n is newline character (how can two characters be a character?). It isn't possible to use char(10) or a local variable instead of a string literal here, so I can't see any obvious way of doing it.

I've previously imported files using RS (0x1E) and FF (0x0C) as row terminators, US (0x1F) and NUL (0x00) as field separators into databases, but I can't see any obvious way to do this with SQLS 2008.

I imagine I might be able to get round it using exec (string) with a string like 'bulk insert blah blah... ROWTERMINATOR =''_char(10)+''...' but I feel as if that would be giving up on finding the right solution. Currently I just edit any file that uses LF as line delimiter instead of CRLF to replace LF by DRLF - with a tool like XVI32 this is trivial even for large files - but this too feels like cheating.

t-sqlbulk-insert
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
TimothyAWiseman avatar image
TimothyAWiseman answered

You can change the row terminator as an option on the bulk insert command. The full details are on MSDN at http://msdn.microsoft.com/en-us/library/ms188365.aspx .

I would point out that bulk insert is one of the less flexible importing methods though and if you need a high degree of customization you may be better of using an SSIS package.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.