1

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?

  • çã is "Mojibake". It means that somewhere in the processing, you had not specified UTF-8. – Rick James Jan 5 '19 at 23:11
3

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.

|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
1

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.

|improve this answer|||||
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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