Need suggestions on how to handle Ascent characters while importing data

[Community Alums]

Hello,

Currently, for data import we are using a property 'charset=utf-8' for handling special characters in the data source. But we are facing an issue in handling ascent characters. Please find the screenshot below.

First name contains illegal characters which actually should have been as per data sheet: 'ORPHÉLIE'

i tried using 'charset=utf-16' as read in community suggestion which is not working here. Can anyone help?

[david631]

I had a similar problem, and I solved it.

My source data consisted of first and last names with illegal / international characters stored as NVARCHAR (UTF-16) values in an MS SQL database. Loading these directly into ServiceNow import sets resulted in bogus characters, regardless of whether I used String or String UTF-8 fields on my import set.

My solution was to store the characters in SQL Server as space-delimited Unicode integer values using the MS SQL UNICODE() function, load them into import sets as such, then convert them into the appropriate characters using String.fromCharCode(), as seen on MDN web docs.

Note: I had to enlarge the size of the first name and last name fields in my import set to accommodate the Unicode integers

MS SQL UNICODE function:

CREATE FUNCTION dbo.UnicodeList (@nstring NVARCHAR(MAX))  
RETURNS VARCHAR(MAX)
WITH EXECUTE AS CALLER  
AS  
BEGIN
    DECLARE @position INT = 1, 
            @out VARCHAR(MAX);  
 
    -- SET @nstring = N'Grønås';  

    WHILE @position <= LEN(@nstring)  
        BEGIN;  
        -- create space-delimited list of unicode integers
        SET @out = CONCAT(
            @out, UNICODE(SUBSTRING(@nstring, @position, 1)), ' '
        );  
        SET @position = @position + 1;  
    END;

    SET @out = RTRIM(@out);
    RETURN @out;
END;

SELECT statement:

SELECT
    dbo.UnicodeList(FirstName) "First Name",
    dbo.UnicodeList(LastName) "Last Name", -- UTF-16 nvarchar, docs.microsoft.com "collation-and-unicode-support"
FROM dbo.Contact;

ServiceNow transform map source script:

answer = (function transformEntry(source) {
	var result = '';
	
	try {
		// example u_last_name: '71 114 248 110 229 115'
		var codes = String.concat('[',source.u_first_name,']');
		codes = codes.replace(/\s/g,',');
		codes = JSON.parse(codes);
		for (var x in codes) {
			result = result + String.fromCharCode(codes[x]);
		}
		// example result: "Grønås"
	}
	
	catch (e) {
		error = true;
		log.error('error on target field "last name": ' + e.message);
	}
	
	return result;
})(source);

Field map config: