Bulk insert rowterminator issue

Question

I have this csv named test.csv with the content below

1,"test user",,,4075619900,[email protected],"Aldelo for Restaurants","this is my deal",,"location4"
2,"joe johnson",,"32 bit",445555519,[email protected],"Restaurant Pro Express","smoe one is watching u",,"some location"

Here is my SQL FILE to do the BULK insert

USE somedb
GO

CREATE TABLE CSVTemp
(id INT,
name VARCHAR(255),
department VARCHAR(255),
architecture VARCHAR(255),
phone VARCHAR(255),
email VARCHAR(255),
download VARCHAR(255),
comments TEXT,
company VARCHAR(255),
location VARCHAR(255))
GO

BULK
INSERT CSVTemp
FROM 'c:\test\test.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '''+CHAR(124)+''+CHAR(10)+'''
)
GO
--Check the content of the table.
SELECT *
FROM CSVTemp
GO

but whats happening is its only inserting one record and all the info from the second record is getting inserted in the location field on the first record

  id,name,department,architecture,phone,email,download,comments,company,location
  1,"test user",NULL,NULL,4075619900,[email protected],"Aldelo for Restaurants","this is my deal",NULL,"""location4""2,""joe johnson"",,""32 bit"",445555519,[email protected],""Restaurant Pro Express"",""smoe one is watching u"",,""some location"""

I assume the problem is the ROWTERMINATOR but i tried all these

ROWTERMINATOR = '\n'
ROWTERMINATOR = '\r\n'
ROWTERMINATOR = '\r'

and all the same results ...any ideas on how to FIX this

I am creating the csv like this via PHP

Answer 1

I think problem is that your csv file uses \n as EOL (unix way). BULK INSERT in SQL Server is "smart" and even if you specify ROWTERMINATOR as \n, which in theory should resolve your problem, it prepends it with \r so you end up with \r\n as row terminator.

Try using ROWTERMINATOR='0x0A'. In this case SQL Server doesn't perform any magic tricks and just uses the value you've set as row terminator.
Works for me. :)

Answer 2

CHAR(124) is | and CHAR(10) is \n

You probably need CHAR(13) and CHAR(10) as Row Terminator

http://www.techonthenet.com/ascii/chart.php