Special characters displaying incorrectly after BULK INSERT

Question

I'm using BULK INSERT to import a CSV file. One of the columns in the CSV file contains some values that contain fractions (e.g. 1m½f).

I don't need to do any mathematical operations on the fractions, as the values will just be used for display purposes, so I have set the column as nvarchar. The BULK INSERT works but when I view the records within SQL the fraction has been replaced with a cent symbol (¢) so the displayed text is 1m¢f.

I'm interested to understand why this is happening and any thoughts on how to resolve the issue. The BULK INSERT command is:

BULK INSERT dbo.temp FROM 'C:\Temp\file.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' );

@AaronBertrand, the Bulk Insert is: code BULK INSERT dbo.temp FROM 'C:\Temp\file.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ); The Collation is Latin1_General_CI_AS. How do I check if the file is marked as Unicode? — MrMatt
– MrMatt, Commented Dec 21, 2012 at 21:06
You can check if the file is Unicode by, for example, opening it in Notepad and then going to File > Save As. Depending on the version of Windows you should see an "Encoding" drop-down somewhere with options like ANSI, UTF-8, Unicode, Unicode big endian... — Aaron Bertrand
– Aaron Bertrand, Commented Dec 21, 2012 at 21:11

Aaron Bertrand · Accepted Answer · 2020-03-11 14:03:22Z

57

You need to BULK INSERT using the CODEPAGE = 'ACP', which converts string data from Windows codepage 1252 to SQL Server codepage.

BULK INSERT dbo.temp FROM 'C:\Temp\file.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', CODEPAGE = 'ACP');

If you are bringing in UTF-8 data on a new enough version of SQL Server:

[...] , CODEPAGE = '65001');

You may also need to specify DATAFILETYPE = 'char|native|widechar|widenative'.

edited Mar 11, 2020 at 14:03

answered Dec 21, 2012 at 21:22

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

monty Over a year ago

This was also what I needed to correctly bulk insert ansii text with accented characters, such as è, in it.

user1417294 Over a year ago

Thank you @aaron bertrand. It worked partially for me. I am facing issues while inserting accented characters. For example: ä is shown as Ã¤

Aaron Bertrand Over a year ago

@user1417294 What is the encoding of the file, the data type and collation of the column, and the collation of the database and instance?

Collectives™ on Stack Overflow

Special characters displaying incorrectly after BULK INSERT

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related