Importing Duration from Excel Spreadsheet

[Brian S1]

Hi Everyone,

I am attempting to import call duration from an Excel spreadsheet to a custom table within ServiceNow. I created the transform map to import the records once I load the data, but the duration column is populating as '(empty)'. After searching through many of the forums, I have found others have been able to successfully import data by utilizing the script 'target.duration.setDateNumericValue(source.u_duration*1000);' but I'm unsure where I need to place this within my transform map or if I should create a business rule. I'm very new to JavaScript so any help would be greatly appreciated.

Thank you,

Brian

[Brian S1]

Hey Abhishek,

Thank you for your continued assistance. I've inserted the script below to my Transform map:

var inc = new GlideRecord('u_call_logger');
inc.get('17c90efb13418700cc36b1422244b05d');
var timems = inc.u_callduration.dateNumericValue();
timems = timems + 11*1000;
inc.u_callduration.setDateNumericValue(timems)
gs.info(inc.u_callduration.getValue());

I am now receiving '(empty)' in my duration column. I've tried formatting my excel column as an integer, date/time format, DD HH:MM:SS, HH:MM:SS, and SS and still receiving the same results.

Thank you,

Brian

[Abhishek Pidwa]

Nope sir this was just an example of how to use setDateNumericValue . you still want to use your original code. Try this code:

var timems = source.u_callduration.dateNumericValue();

timems = timems + 11*1000;

target.u_callduration.setDateNumericValue(timems*1000);

Couple of points:

1) make sure that both source and target table has a field named as u_callduration

2) In your excel sheet, change the format of u_callduration field to DATE.

3) make sure that the u_callduration on the target table is a Date format

3) Try this code and see what output are you getting ? 

Good luck .

Please mark this as helpful/correct if this solves your problem.

[Brian S1]

Hi Abhishek,

I have inserted the script you had recommended, set the u_callduration field to a date/time in the target table, and set the field to a date within my spreadsheet. I am now receiving a date with a time associated, but all are 12-31-1899 12:00:00 AM. We were hoping to get this field calculated in seconds.

Thank you,

Brian

[Abhishek Pidwa]

Ahhhh now I see your point. OK question for you :

Do you want SNOW to do the calculation or is that something already done in your excel sheet ?

If that is already done in your excel sheet then you are fine with just not using any transform Script and just do the original mapping provided that you have the callDuration column as an integer with precalculated seconds.

If you want SNOW to do it then it becomes tricky since you have to first convert your excel sheet to include date in the CallStart and CallEnd column of your excel sheet and then write this following code:

var timemsStart = source.u_callstart.dateNumericValue();

var timemsEnd = source.u_callend.dateNumericValue();

var diff = timemsEnd - timemsStart;

target.u_callduration.setDateNumericValue(diff*1000);

Hope this helps. 

Please mark this as helpful/correct if this solves your problem.

[Brian S1]

Hey Abhishek,

The calculation is already done in my excel spreadsheet (Please see the below screenshot). I have attempted to import this field as is, converted it to text, and tried converting it to a standard integer in the spreadsheet and it imports as '(empty)' in SNOW.

Thank you,

Brian