Collation on Bulk Insert

Question

I´ve been traying to fix a process for my company and I've found the source of my problem.

I have a CSV file from where I have to import some data, but some fields contains words with de Ñ word and SQL Server keeps replacing it with characters like this ╤ I changed my column collation to COLLATE Modern_Spanish_CI_AS but when I perform the Bulk Insert It doesn't care about the specified collation and replaces it anywhere.

Do you guys know a way where bulk inster respects the given collation ?

The code I'm using is below.

EXEC ('BULK INSERT #TMPP FROM ''' + @PATH +
    ''' WITH (FIELDTERMINATOR = ''|'', FIRSTROW = 1, ROWTERMINATOR = ''\n'', KEEPNULLS);');

what's the DDL of #TMPP look like? You'll want that column to be a `NVARCHAR()` — S3S, May 22 '17 at 18:39

score 0 · Answer 1 · answered May 23 '17 at 00:03

I have found the solution, I needed to specify CODEPAGE = 1252 in the WITH clause. The code ended been up like this.

EXEC ('BULK INSERT #TMPP FROM ''' + @PATH +
        ''' WITH (FIELDTERMINATOR = ''|'', FIRSTROW = 1, ROWTERMINATOR = ''\n'', KEEPNULLS, CODEPAGE = 1252);');

I hope you find it useful in the future.

Collation on Bulk Insert

1 Answers1