How to Modify data before inserting them into the target table using Transform Map Script.

[Riya25]

Hello Experts,

I have started an integration with the external database,

When i have loaded the data into my staging table i found some of the data are in different format than the ones present in the Target table.

So,I want to modify the data .

For ex- at present in staging table my data format is "1234567891011....32" that is 32 alphanumerical data ,now i want to include "-" in between after 8th character,after 12th,16th etc.

Is it possible ?

if yes and then how?

Regards,

Riya

[Ct111]

Hello,

it is possible , you need to code something like below in your transform script , I means codewise...

https://stackoverflow.com/questions/4364881/inserting-string-at-position-x-of-another-string

Here it is given with string you can replace it by numbers and crosscheck.

I think you are aware about how to write transform scripts , if not then check the OOB scripts for examples once.

https://docs.servicenow.com/bundle/orlando-platform-administration/page/administer/import-sets/task/...

Mark my ANSWER as CORRECT and HELPFUL if it helps

[Kieran Anson]

Hi Riya,

Within your transform map, you'd use a scripted field map by ensuring 'use source script' is checked and the below script.

Replace u_field_name with the field name of the staging table holding the number. The below will insert a dash after every 4th character.

answer = (function transformEntry(source) {

	// Add your code here
	var number = source.u_field_name //Name of field on staging table
	var newval = number.match(/.{1,4}/g).join('-');
	return newval; // return the value to be put into the target field

})(source);

[Jaspal Singh · Accepted solution]

Hi Riya,

Yes, you can. Considering you have created field mapping for the data to be transformed from staging table to target table you need to set 'Use Source Script' to True & use below script.

Ignore the Map, source table, field from below as its just an example. You need to use the script as below & also, map the field correctly.

Script:

answer = (function transformEntry(source) {
	
	
	var str=source.yourfieldname; //replace yourfieldname correctly here
	var addinghyphen= str.substring(0, 8)+'-'+str.substring(8,12)+'-'+str.substring(13,17)+'-'+str.substring(18,32);
	//suppose source is 12345678912345678912345678912345 32 characters target will be 12345678-9123-5678-12345678912345

	return addinghyphen; // return the value to be put into the target field

})(source);

[Hitoshi Ozawa]

1. From Application Navigator, search "system import sets"

2. Open "Administration" -> "Tranform Maps"

3. Select the transformation map that you're using

4. Go to the bottom of the page and select "Field Maps" tab

5. Double click on the field that you want to convert

6.  Check "Use source script"

7. Enter the following script replacing "u_field_name" in the second row with the name of the field.

answer = (function transformEntry(source) {
  var tmpdata = source.u_fieldname; // change u_fieldname with the your field name
  var newdata = null;
  if (JSUtil.notNil(tmpdata)) {
    newdata = tmpdata.toString().match(/.{4}/g).join('-');
  }
  return newdata; // return the value to be put into the target field
})(source);

8. Click "Update" button