469,150 Members | 1,973 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,150 developers. It's quick & easy.

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
  1.  
  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 + ')'
  7.  
  8.     EXEC(@Query)
  9.  
Thank you,

Zach
Feb 9 '09 #1
2 5613
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)
  2.  
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
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.