By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

BULK INSERT row terminator issue

P: 9
Hi everyone,

I receive a variety of text based files that seperates each row by a single delimiter, usually '~'. I use BULK INSERT to transfer the file from a text file to a temporary table. It works fine for most of the files (which use '~' as the delimiter for the rows), but for some reason I can't get it to work for a particular file.

The file looks like it uses a new line character as the row delimiter, so I put in '\n' as the row delimiter but it didn't work. I also tried '\r' and '\r\n' and '\n\r' and none of them work. I then ran a script to return every ASCII value in the file and for each break that starts a new row, it gave 2 ASCII values: 11 (tab) and 13 (carriage return) so I tried '\t\r' and '\t\n' and that didn't work. Any ideas on how to get it to split it up into rows? I can't think of any other solution except changing the file before but it seems like it should work somehow through BULK INSERT.

I don't know if it helps or not but below is part of the sproc I'm using (like I said before, it works fine with all other files):

Expand|Select|Wrap|Line Numbers
  2.     -- Generate query
  3.     SET @Query = 'BULK INSERT #FileBase FROM "' + @FILE_PATH + '"'    
  4.     SET @Query = @Query + ' WITH ('
  5.     SET @Query = @Query + 'CODEPAGE = ''RAW'', ROWTERMINATOR = ''' + @ROW_TERM + ''''
  6.     SET @Query = @Query + ')'
  8.     EXEC(@Query)
Thank you,

Feb 9 '09 #1
Share this Question
Share on Google+
2 Replies

P: 30
We ended up figuring out a fix. We just added:

Expand|Select|Wrap|Line Numbers
  1. IF @ROW_TERM = '\n' SET @ROW_TERM = CHAR(10)
I'm not exactly sure why \n doesn't work by itself in this instance but that fix seemed to work.
Feb 11 '09 #2

P: 1
this works out in my case too... strange~
Feb 12 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.