1 Reply Latest reply on May 14, 2018 12:04 PM by Patrick A Van Der Hyde

    How to change CHARSET from UTF8 to UTF16 for Custom SQL?

    Radu Mihai

      Hello my dear friends,

       

      I have a challenging question for you. Here are the facts:

      • My main data source is a Custom SQL that pulls data from a PostgreSQL database containing JIRA data (projects and their associated issues that track work progress in those projects etc.).

       

      • My JIRA data is encoded in UTF8, so the results from the Custom SQL are in UTF8. This is my must-have data source. It works OK.

       

      • I have a new, additional data connection to a Smartsheet. I have the ODBC connector installed and working without faults. I have the Smartsheet data LEFT OUTER JOINed to my Custom SQL connection

       

      • The Smartsheet data is encoded in UTF16.

       

      I did not know this, but UTF8 and UTF16 are not compatible, apparently you can't have a data connection of different encoding. Trying to pull data from the Smartsheet table throws the error below.

       

      As I understand, I need to convert either of those data sources to the other UTF encoding. Either convert the CustomSQL source to UTF16, all of it. Or convert the Smartsheet to UTF8 somehow.

       

      I would like not to have an intermediary solution like putting the data in an Excel sheet, since that requires periodic manual maintenance. Or if you happen to have a fully automatable method, that would be great.

       

      I would appreciate any suggestions, I have not found a means to mass-convert these things, the CONVERT commands I found have only limited power to convert - one string at a time, but I need to convert everything.

       

      Thank you and god bless.

      error UTF8 UTF16.png