Importing values in a custom table

[micky09]

Hi,

I have a requirement where I need to import the data from an excel to a custom table in excel I am getting value of 3 fields for example email, UserID, Asset ID in on field after transforming it i need to push these three fields in 3 different fields at Target table lets say UID, Email & AssetID How can I achieve this any help will be appreciated. 

[Ahmmed Ali]

You can use source script in transform map field mapping for this. In the field mapping, you can read source (import set table) field value, do necessary data manipulation and then return value for required for target field.

For example: 

Assume SD field has value as "This is incident||3" and you can the this is incident part to be copied to short description on target table and 3 to be copied to priority.

Now in transform map, create two field maps as below:

1. Target field is short description, check use source script, write below script:

var val = source.getValue("u_sd"); //Replace your field name

valArray = val.split("||"); //check what separator you are having to in your sheet

return valArray[0]; //first part of the string

2. Target field is priority, check use source script, write below script:

var val = source.getValue("u_sd"); //Replace your field name

valArray = val.split("||"); //check what separator you are having to in your sheet

return valArray[1]; //second part of the string

Above is one example. you can correlate and create solution to your user case.

Thank you,

Ali

[kamlesh kjmar · Accepted solution]

Hi @micky09 ,

1. Use load data module of servicenow. In that specify the table name of your import set, if it's already not available then create a new one by selection create new option and upload your excel file.

2. Select the proper sheet number and header number normally it's 1.

3. Submit the form, a new page will open with related link, create transform map. Click on that, it will opena a transform map page. Select your desctination table here. In your case its your custom table.

4. Save the form and in related list do the proper mapping of your fields. Mapping of your source field to target field.

Below the link on how to create transform map

https://developer.servicenow.com/dev.do#!/learn/learning-plans/quebec/new_to_servicenow/app_store_le...

5. Finally when above steps are complete. Click on transform related link and then transform.

NOte : While mapping make sure to use any unique field as coalace, else as many time you will run the import, that many records will create as duplicates.

Following all the above steps will help you transformning all your excel sheet daya in servicenow.

I Hope this helps.

Please mark this helpful if this helps and mark as correct if this solves your issue.

Regards,

Kamlesh

[micky09]

This part i understood thankyou for the reply! Just wanted to check on if in source data as i said in excel lets say there is a column name short description, in that column it self i am value of 3 fields lets say Sd, Priority & category all are comin in this field but in my target table I need to import it by segregating the values how can i achieve that?

[kamlesh kjmar]

@micky09 

In the given scenario, what I would suggest is,

1Create 3 separate columns in your excel sheet and load this sheet in servicenow:

Note: We created three colomns in excel sheet so that it appears in our import set as a field where we will store the respective values after segregation. It will also help in tracking what data is populating in which field.

2. In your transform map, write a onBefore transform script, where you will split the short description column data based on any delimeter. In my example delimeter is ',' and then set it into the respective fields of import set. Below the script:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

	// Add your code here
	var data = source.u_short_description;
	var splitData = data.split(",");
	source.u_sd = splitData[0];
	source.u_priority = splitData[1];
	source.u_categry = splitData[2];

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

3. Now do the mapping of fields in you transform map like sd->short_description, priority->priority and categry->category:

4. As a final step now load the data again and transform. You will find all the data from your short description colomn will get segregated and mapped to the respective fields in your import set, which eventually will get mapped into the target table as well.

I Hope this helps.

Please mark this helpful if this helps and mark as correct if this solves your issue.

Regards,

Kamlesh