1

I have a stored procedure that bulk imports a text file and inserts it into my database.

CREATE TABLE DBO.TEMP_STORE
(
            ID nvarchar(max),
            [MONTH] nvarchar(max),
            [YEAR] nvarchar(max),
            STORE nvarchar(MAX),
            SUBMITTAL nvarchar(MAX),
            ENTITY nvarchar(MAX),
            SUBMITTAL_TYPE nvarchar(MAX),
            iCOUNT nvarchar(MAX),
            STATE nvarchar(MAX),
            COUNTRY nvarchar(MAX),
            REGION nvarchar(MAX),
            GLOBAL_REGION nvarchar(MAX),
            LOCAL_CURRENCY nvarchar(MAX)

 )
--SELECT * FROM DBO.TEMP_STORE
--prepare bulk insert query to load data from file to temp table
SET @SQL='BULK INSERT DBO.TEMP_STORE FROM '''+ @FilePath+''''
SET @SQL=@SQL+'WITH ('
SET @SQL=@SQL+'DATAFILETYPE = ''char'',' 
SET @SQL=@SQL+'FIELDTERMINATOR = ''|'','
--SET @SQL=@SQL+'ROWTERMINATOR = ''\n'','
SET @SQL=@SQL+'ROWTERMINATOR = ''' + nchar(10) + ''','
SET @SQL=@SQL+'FIRSTROW =2)'

--print @SQL
EXEC (@SQL)

For instance, importing Update ROLLING_CONE_SHOP_DETAIL set SHOP_STATE = 'São Paulo' results in storing the data as'S+úo Paulo' when I select the same row that was imported. I can update the row and explicitly fix the fix the issue:

Update TEMP_STORE set STATE = 'São Paulo' where STATE= 'S+úo Paulo'

But when I do my Bulk insert, it does not retain the special character. I have confirmed the text file I am importing is saved at utf-8 and contains the correct character.

How can I make sure my bulk inset retains the special character properly?

4

SQL Server 2008 does not support UTF-8 natively prior to SQL Server 2019.

So how can you import your data correctly?

  1. Convert your data to UTF-16 prior to your insert
  2. Upgrade to SQL Server 2014 (SP2) or above and take advantage of code page 65001 in the bulk insert statement as below

    BULK INSERT #table
    
    FROM  'C:\MyFile.csv'+ WITH 
    
    ( 
        CODEPAGE = '65001',
        FIELDTERMINATOR = ',',
        ROWTERMINATOR ='\n'
    );
    
  3. Since UTF-8 is an encoding style, not a collation, store the data as VARBINARY and decode it in the front-end rather than the database.

  • I'm not necessarily saying that it needs to be stored at utf8. I know the db is capable is storing the data as I need it because the update statement I am running in the initial question works properly. I am trying to find out how to store 'S+úo Paulo' as 'São Paulo' with the bulk insert in sql server 2008 – ninjasense Jan 25 at 16:22
  • @ninjasense as far I know the only way is utilising a later version of BCP. IE 2016 – George.Palacios Jan 25 at 16:29
  • 1
    @ninjasense and George: I would remove the first sentence about natively storing UTF-8 since this question is about importing, not storing, and those are 2 separate things. For item #1, please specify "Little Endian". I would remove suggestion #3 about using VARBINARY as there is no purpose or benefit in doing that. Finally, a newer version of BCP is an interesting idea. Certainly wouldn't hurt to try. You can download it from here: Microsoft Command Line Utilities 14.0 for SQL Server. Please let us know if it works or not. – Solomon Rutzky Jan 25 at 17:03
0

I ended up converting the UTF-8 File to ANSI and the special characters were retained.

  • Hi there. Did you mean "UTF-16 Little Endian" instead of "ANSI"? If you converted to ANSI / Code Page 1252 / Latin1_General, then you would lose the "special characters" as ã would be transformed into before you even did the import. – Solomon Rutzky Jan 27 at 22:36
  • @SolomonRutzky I opened my file in notepad++ and then selected "Convert to ANSI" and it worked. I just confirmed this again. I have no clue why it works and it doesn't make sense but it is working for me – ninjasense Jan 29 at 14:21
  • Ah, I just looked at the question again and see what is really going on. The ã character is available on code page 1252 (what Microsoft calls "ANSI"), so converting the file to ANSI is able to retain that character, which had been encoded as two bytes under UTF-8. However, this is not a good long-term solution since there are many other characters that will not cleanly convert when switching to ANSI. You just got lucky with this one. What I don't understand is how you got the as the replacement. What is the default collation of that DB, and what is the collation of the STATE column? – Solomon Rutzky Jan 29 at 20:38

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.