Additional characters are coming during bulk insert

Question

I am trying to bulk insert the first row from a csv file into a table with only one column. But I am getting some extra characters('n++') in the begining like this:

n++First Column;Second Column;Third Column;Fourth Column;Fifth Columnm;Sixth Column

CSV file contents are like:

First Column;Second Column;Third Column;Fourth Column;Fifth Columnm;Sixth Column

You can find the test.csv file here

And this is the code I am using to get the first row data in a table

declare @importSQL nvarchar(2000)
declare @tempstr varchar(max)
declare @path varchar(100)  

SET @path = 'D:\test.csv'    

CREATE TABLE #tbl (line VARCHAR(max))

SET @importSQL = 
'BULK INSERT #tbl 
FROM ''' + @path + ''' 
WITH ( 
LASTROW = 1,
FIELDTERMINATOR = ''\n'',
ROWTERMINATOR = ''\n''
)' 

EXEC sp_executesql @stmt=@importSQL 

SET @tempstr = (SELECT TOP 1 RTRIM(REPLACE(Line, CHAR(9), ';')) FROM #tbl)

print @tempstr
drop table #tbl

Any idea where this extra 'n++' is coming from?

Answer 1

It seems UTF-8 files are not supported by SQL Server 2005 and 2008, it will only be available in version 11!

https://connect.microsoft.com/SQLServer/feedback/details/370419/bulk-insert-and-bcp-does-not-recognize-codepage-65001

Answer 2

The extra charectors are caused by the encoding. You can use used notepad to change the encoding format from UTF-8 to Unicode. This removed the 'n++' on the first row.