How to import excel document values to duration field type using Robust transform map

[Rajkumar Pendur]

How to import excel document values to duration field type using Robust transform map

We are using robust transform map to import data to multiple tables. when we are trying to import duration field it populates empty.

[Community Alums]

Hi @Rajkumar Pendur ,

You can use this code to convert this:

var duration = source.duration_day_hour_mm_ss.toString();
duration = duration.replace(":"," "); // replace the first occurrence of colon to blank space
var gd = new GlideDuration(duration);
target.duration_day_hh_mm_s.setDateNumericValue(gd.getNumericValue());

Your data should be in proper format. If you expect data in two different format then you can update your code as below:

Note: This works only if data is in either of dd-hh-mn-ss or dd:hh:mn:ss format

[Sayali Gurav]

Hello @Rajkumar Pendur ,

--> Navigate to "Data Import > Data Sources" and select the relevant data source.>Create a new Transform Map or use an existing one.
--> In the Transform Map, map the Excel columns to the corresponding fields in ServiceNow, including the duration field.
--> If necessary, set up Field Transformations to handle any basic data transformations.
--> In the Transform Map, find the field that corresponds to the duration in your target table. >Set the 'Transform Script' field to the script that will handle the more complex transformation.
--> Open the Script Editor for the Transform Map. > Write a script that extracts the duration information from the Excel column and transforms it into the format expected by the duration field in ServiceNow.

(function runTransformScript(source, map, log, target /*, transformer, helpers */) {

// Extract duration value from Excel column
var excelDuration = source.u_excel_duration_field; // replace with your actual Excel column name

// Convert Excel duration to ServiceNow duration format (e.g., '5 12:30:00')
var serviceNowDuration = convertExcelToServiceNowDuration(excelDuration);

// Set the transformed duration value in the target field
target.u_duration_field = serviceNowDuration; // replace with your actual target duration field name

})(source, map, log, target, transformer, helpers);

function convertExcelToServiceNowDuration(excelDuration) {
// Implement the logic to convert Excel duration to ServiceNow duration format
// Example: Convert '2 days 5 hours' to '2 05:00:00'
// ...

// Return the transformed duration
return transformedDuration;
}
--> Test the Transform Map with a small set of data to ensure that the script works as expected. > Use the log statements within the script to debug any issues.
--> Go to "Data Import > Loads" and create a new Data Import Load. > Select the source file (Excel document) and the target table. > Run the import to populate records in the target table with the transformed data using the script.

Please mark helpful and accept as solution if this will helps you.

Thanks & Regards,
Sayali Gurav

[Rajkumar Pendur]

Thanks for help. we got the answer.

[Rajkumar Pendur]

Hello everyone

From Robust transform map we are updating two table fields. From created record how set one table field value  to   another table field to using robust transform map