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?

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

2 Answers 2

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.

3
  • 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, 2013 at 12:44
  • 2
    libiconv also has a command line program: iconv, should be included in the Win32 download. Jan 17, 2013 at 12:49
  • 3
    And be warned that SQL Server uses UCS2 Little Endian (UCS-2LE). Jan 17, 2013 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.

4
  • 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, 2013 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, 2013 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, 2013 at 12:34
  • Well, it appears that Remus's answer is already clear enough :-).
    – Marian
    Jan 17, 2013 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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