BULK INSERT row terminator issue

[zswanson]

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):

 
    -- Generate query
    SET @Query = 'BULK INSERT #FileBase FROM "' + @FILE_PATH + '"'    
    SET @Query = @Query + ' WITH ('
    SET @Query = @Query + 'CODEPAGE = ''RAW'', ROWTERMINATOR = ''' + @ROW_TERM + ''''
    SET @Query = @Query + ')'
 
    EXEC(@Query)
 

Thank you,

Zach

[zachster17]

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

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.

[shiming927]

this works out in my case too... strange~