Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm inserting data (Bulk insert and merge update) for a Portuguese data source (CSV files) into my database. Although the DB collation is Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data and data stored in NVARCHAR columns, the portuguese charaters are not correctly being saved.

i.e : Original Data

Regularização de Importação de mercadorias das ZEEs com Pagamento  diferido, com Isenção

Data in database after insert

Regularização de Importação de mercadorias das ZEEs com Pagamento  diferido, com Isenção

Here the import statement:

CREATE PROCEDURE [dbo].[SP_Import_Temp_CPC] 
-- Add the parameters for the stored procedure here
@FilePath [nvarchar](1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

TRUNCATE TABLE Temp_CPC;

-- Insert statements for procedure here
EXEC ('BULK INSERT Temp_CPC
FROM ''' + @FilePath + '''
WITH ( FIELDTERMINATOR = ''<,>'', ROWTERMINATOR =''\n'', FIRSTROW = 2, KEEPIDENTITY, CODEPAGE = ''ACP'',TABLOCK,ORDER(ITEMID ASC) );');
END

I've tried with "ACP,RAW and OEM" but get the same result.

Am I doing something wrong here?

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

You're not going to get the correct result until you tell BULK INSERT the code page of your .csv file. You tried ACP, RAW and OEM but you did not try the correct one. Which one is the correct one you ask? That would be something only you can answer, since you have the file and you (should) know what code page was originally created as! Perhaps MS-DOS Portuguese?

the other alternative is to create the .csv file itself as an Unicode file, but that goes back to your source of the .csv file and depends how that file is created (you bcp out, you have it from a friend, was always used by the original program author, etc etc).

the file is an Unicode one (UTF8 without BOM)

SQL Server does not support UTF-8 files for bulk insert. You must save your file as UCS-2LE or as ASCII with codepage 860. You can use libiconv to do the conversion if you cannot save the file again in the proper encoding.

share|improve this answer
    
Thanks, for the reminder. Unfortunately the file is produced at the client location on a linux box using oracle. And that was default setting i found in used when taking on the project. But im grateful for the conversion library you did provide. –  Raymond Jan 17 '13 at 12:44
2  
libiconv also has a command line program: iconv, should be included in the Win32 download. –  Remus Rusanu Jan 17 '13 at 12:49
3  
And be warned that SQL Server uses UCS2 Little Endian (UCS-2LE). –  Remus Rusanu Jan 17 '13 at 12:51
add comment

The BULK INSERT statement is using char datatype by default.

You should be using the widechar data type, as mentioned in: Using Unicode Character Format to Import or Export Data.

Mainly, the change should be:

  • DATAFILETYPE = 'widechar' if you're using BULK INSERT
  • w if you're using BCP

But the original CSV file has to be UNICODE.

share|improve this answer
    
Thanks for the feedback. Will test it. –  Raymond Jan 17 '13 at 11:44
    
I did apply the changes to the script but got an error 'Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.' although the file is an Unicode one (UTF8 without BOM), i later converted it to UTF-8 but still got the same results. –  Raymond Jan 17 '13 at 12:10
    
Well, at least you have the evidence that by default it uses char instead of nchar. I'm curious what is a Unicode signature, though.. Maybe I can help with an example. –  Marian Jan 17 '13 at 12:27
    
A few Googling and it appears that the SQL 2008 support the UTF-16 encoding with the DataType='Widechar'. Upon converting it to UTF-16 (UCS-2 Big Endian) it did worked. –  Raymond Jan 17 '13 at 12:34
    
Well, it appears that Remus's answer is already clear enough :-). –  Marian Jan 17 '13 at 12:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.