Search code examples
sqlsql-serverbulkinsert

Bulk insert, SQL Server 2000, unix linebreaks

I am trying to insert a .csv file into a database with unix linebreaks. The command I am running is:

BULK INSERT table_name
FROM 'C:\file.csv' 
WITH 
( 
    FIELDTERMINATOR = ',', 
    ROWTERMINATOR = '\n' 
)

If I convert the file into Windows format the load works, but I don't want to do this extra step if it can be avoided. Any ideas?

Solution

  • I felt compelled to contribute as I was having the same issue, and I need to read 2 UNIX files from SAP at least a couple of times a day. Therefore, instead of using unix2dos, I needed something with less manual intervention and more automatic via programming.

    As noted, the Char(10) works within the sql string. I didn't want to use an sql string, and so I used ''''+Char(10)+'''', but for some reason, this didn't compile.

    What did work very slick was: with (ROWTERMINATOR = '0x0a')

    Problem solved with Hex!