Bulk insert, SQL Server 2000, unix linebreaks

sql sql-server bulkinsert
John Oxley picture John Oxley · Jan 26, 2009 · Viewed 27.5k times · Source

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?

Answer

Randy J picture Randy J · Nov 17, 2010

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!

Hope this helps someone.

Related questions

Import CSV file into SQL Server

I am looking for help to import a .csv file into SQL Server using BULK INSERT and I have few basic questions. Issues: The CSV file data may have , (comma) in between (Ex: description), so how can I make import …

Read More
How to BULK INSERT a file into a *temporary* table where the filename is a variable?

I have some code like this that I use to do a BULK INSERT of a data file into a table, where the data file and table name are variables: DECLARE @sql AS NVARCHAR(1000) SET @sql = 'BULK INSERT ' + @tableName + …

Read More
Determine ROW that caused "unexpected end of file" error in BULK INSERT?

i am doing a bulk insert: DECLARE @row_terminator CHAR; SET @row_terminator = CHAR(10); -- or char(10) DECLARE @stmt NVARCHAR(2000); SET @stmt = ' BULK INSERT accn_errors FROM ''F:\FullUnzipped\accn_errors_201205080105.txt'' WITH ( firstrow=2, FIELDTERMINATOR = ''|'' , ROWS_PER_…

Read More