SQL Server

Convert utf-8 Text File to Windows-1252

Demonstrates how to convert a text file using the utf-8 byte representation to windows-1252. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- Converts a file containing the following to windows-1252:

    -- <greetings>
    --     <message>Hello, world!</message>
    --     <message>¡Hola, mundo!</message>
    --     <message>Bonjour, le monde!</message>
    --     <message>Hallo, Welt!</message>
    --     <message>Olá, mundo!</message>
    --     <message>Привет, мир!</message>
    --     <message>你好，世界！</message>
    --     <message>こんにちは、世界！</message>
    --     <message>안녕하세요, 세계!</message>
    --     <message>😊🌍</message>
    -- </greetings>

    -- --------------------------------------------------------------------------------------------------------------------------
    -- Note:
    -- Windows-1252 is an 8-bit single-byte encoding. It can only encode:
    -- 
    --     The basic ASCII set (0x00–0x7F).
    --     Latin-1 Supplement (0xA0–0xFF), plus some extra printable characters (like curly quotes, €, etc.).
    --     In total: 256 possible code points, covering most Western European languages but nothing outside of Latin script.

    -- --------------------------------------------------------------------------------------------------------------------------
    -- Characters in your XML that are representable
    -- 
    --     Hello, world! ✅ (ASCII only)
    --     ¡Hola, mundo! ✅ (inverted exclamation mark U+00A1 is in Windows-1252)
    --     Bonjour, le monde! ✅
    --     Hallo, Welt! ✅
    --     Olá, mundo! ✅ (U+00E1 á and U+00F3 ó are in Windows-1252)

    -- --------------------------------------------------------------------------------------------------------------------------
    -- Characters that break conversion
    -- 
    --     Russian / Cyrillic: Привет, мир!
    --     → These are Cyrillic characters (U+041F … U+0440). Not representable in Windows-1252. Conversion would require replacement (e.g. with ? or XML character references).
    --     Chinese: 你好，世界！
    --     → CJK ideographs (U+4F60, U+597D, etc.). Not in Windows-1252.
    --     Japanese: こんにちは、世界！
    --     → Hiragana + CJK. Not in Windows-1252.
    --     Korean: 안녕하세요, 세계!
    --     → Hangul syllables. Not in Windows-1252.
    --     Emoji: 😊🌍
    --     → Unicode Supplementary Multilingual Plane (U+1F60A, U+1F30D). Windows-1252 cannot encode any emoji.

    DECLARE @bd int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @bd OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Load the utf-8 bytes.
    EXEC sp_OAMethod @bd, 'LoadFile', @success OUT, 'qa_data/xml/utf8test.xml'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @bd, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @bd
        RETURN
      END

    -- If allOrNone = 1, then the conversion fails and the contents of the BinData
    -- are left unchanged if any char is unconvertable.

    -- If allOrNone = 0, then non-convertable chars are discarded.
    DECLARE @allOrNone int
    SELECT @allOrNone = 0
    DECLARE @fromCharset nvarchar(4000)
    SELECT @fromCharset = 'utf-8'
    DECLARE @toCharset nvarchar(4000)
    SELECT @toCharset = 'windows-1252'
    EXEC sp_OAMethod @bd, 'CharsetConvert', @success OUT, @fromCharset, @toCharset, @allOrNone

    -- The return value will be 0 if any utf-8 chars were discarded because of non-convertability.
    IF @success = 0
      BEGIN

        PRINT 'Some utf-8 chars could not be converted to windows-1252'
      END
    ELSE
      BEGIN

        PRINT 'All utf-8 chars were converted to windows-1252'
      END

    EXEC sp_OAMethod @bd, 'WriteFile', @success OUT, 'c:/temp/qa_output/out.xml'

    -- The output file contains the following, where all non-convertable chars were discarded

    -- <greetings>
    --     <message>Hello, world!</message>
    --     <message>¡Hola, mundo!</message>
    --     <message>Bonjour, le monde!</message>
    --     <message>Hallo, Welt!</message>
    --     <message>Olá, mundo!</message>
    --     <message>, !</message>
    --     <message></message>
    --     <message></message>
    --     <message>, !</message>
    --     <message></message>
    -- </greetings>

    EXEC @hr = sp_OADestroy @bd


END
GO