472,131 Members | 1,419 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,131 software developers and data experts.

BULK INSERT row terminator issue

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
2 5748
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
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.

Similar topics

3 posts views Thread by Jim Geissman | last post: by
2 posts views Thread by php newbie | last post: by
5 posts views Thread by me | last post: by
1 post views Thread by joshsackett | last post: by
6 posts views Thread by pk | last post: by
2 posts views Thread by Mike Husler | last post: by
11 posts views Thread by Ted | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.