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

[Abhishek Pidwa]

Ahhh the problem is that SNOW doesn't know about your time (HH:MM:SS AM) and only cares about seconds. You can modify your callDuration field in excel to do something similar to this:

Total seconds between times:

To get the total seconds between two times, you multiply the time difference by 86400, which is the number of seconds in one day (24 hours * 60 minutes * 60 seconds = 86400).

=(End time - Start time) * 86400

In our example, the formula is as follows:

=(B2-A2)* 86400

https://www.ablebits.com/office-addins-blog/2015/06/24/calculate-time-excel/#calculate-time-difference

Then store that callDuration as an integer. 

I tried this and it worked in my Excel sheet :

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

[Abhishek Pidwa]

Hey,

Where you able to solve the problem ? 

Thanks.

[Brian S1]

Hello Abhishek,

I apologize for the delayed response as this project was put on a brief hold. I have gotten very close in getting the duration field after playing around with it.

I am able to get the duration imported into my import set table 'Call Logger', but when I run the transform to move the fields to the 'Call Log' table the duration does not get copied over. I have created a new Transform Map and verified both field types are Duration. I wouldn't think you would need a script to load the duration from the Call Logger table to the Call Log table since the duration populates fine in the Call Logger table, but I am getting an empty field. I then attempted to insert the target.duration.setDateNumericValue(source.u_duration*1000); script but I was only receiving '0 seconds' in my column. I have included screenshots below:

Spreadsheet format:

 

Call Logger Table - Import Set Table:

Transform Map:

Call Log Table:

We may be able to do the integer route, but we were hoping to obtain the duration format within the new table.

Thank you so much for your continued help, it has been greatly appreciated.

Thanks,

Brian

[Abhishek Pidwa]

Looks like you are getting closer. Hey one thing I noticed in your transform script is that you are using this :

target.duration.setDateNumericValue(source.u_duration*1000);

Shouldn't it be :

target.u_call_duration.setDateNumericValue(source.u_callduration*1000);

Try this. Also Can you post the screenshot of Call Duration field map ? 

[Brian S1]

Hi Abhishek,

You are correct, I have just modified the script within the Transform Map. I attempted to load the spreadsheet again and am now receiving '0 seconds' in each call duration:

Transform Map:

Call Duration Field Map:

Call Log Table:

Thank you,

Brian