(SQL Server) Convert a Text File from utf-8 to Windows-1252

Convert a text file from one character encoding to another.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- It's really simple:  Just load from one charset, save using another.

    DECLARE @sb int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sb OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- In this case, my test file has some norwegian chars in the utf-8 encoding
    DECLARE @success int
    EXEC sp_OAMethod @sb, 'LoadFile', @success OUT, 'qa_data/txt/norwegian_chars.txt', 'utf-8'

    -- We could just as well write over the file we just read in.
    -- (But I don't want to do that for my example..)
    EXEC sp_OAMethod @sb, 'WriteFile', @success OUT, 'qa_output/norwegian_chars.txt', 'windows-1252', 0

    -- Note:  Windows-1252 is a 1-byte per char encoding, which means it's only capable of representing
    -- those chars in Western European languages.   See https://en.wikipedia.org/wiki/Windows-1252
    -- 
    -- utf-8 is an encoding that can handle chars in any language, including Chinese, Korean, Japanese, Arabic, Hebrew, Greek, etc.
    -- If a text file contains chars in these other languages, obviously it cannot be converted to windows-1252 because
    -- there are only 256 chars that can possibly be represented in a 1-byte per char encoding.

    EXEC @hr = sp_OADestroy @sb


END
GO