Unable to import a duration field in seconds from Excel to a custom table duration field.

[Mishu]

I am trying to import a column from Excel to a duration field in ServiceNow using below code(as suggested in other articles ) but it is giving some different values :

var duration = source.u_avg_speed_of_answer.toString();

duration = duration.replace(":"," ");

var gd = new GlideDuration(duration);

target.u_average_speed.setDateNumericValue(gd.getNumericValue()*1000);

After Import :

Please advice how to make it work

[amaradiswamy]

Hi,

I tried with below in my PDI and it is working for me.

In the transform map, I have selected "Run script" as true

Code to use:

var durationVal = source.u_speed_of_answer.toString();
durationVal = durationVal.replace(':',' ');

var gd = new GlideDuration();
gd.setDisplayValue(durationVal);
var durNumericVal = gd.getNumericValue();


target.u_speed_of_answer.setDateNumericValue(durNumericVal);

Looking at your code, please check below:

1. Make the field name is correct in import set table and target field. In the spread sheet, I see the column name as "Speed of answer" but the field name you mentioned is u_avg_speed_of_answer, so please double check the field name

2. getNumericValue() will give you the milli seconds so you don't need to multiply with 1000 when setting the duration field

[Mishu]

Hi,

I have corrected the field name, but issue still persist..I am still getting the same value in my target field..However in my staging table  field i can see the below value :

[amaradiswamy]

Got it, this is because of the data type of the field in Import set table.

I think you can delete this duration type field by going into that import set table, create a new field of string type with same name and adjust the transform map

[amaradiswamy]

@Mishu Just wanted to check if your issue is corrected after changing the field data type to String.