How to import data from excel sheet to a table on servicenow

[Souvick A]

Hello All,

I have an excel sheet where I have phone numbers which I need to update on the sys_user table business phone field.

I need to update only the users whose business phone field in blank with the phone numbers from excel sheet matching against their user id. 

I need to know the whole process to import the data in sys_user table.

Thanks in Advance

Regards

Souvick 

[Saurav11]

Hello,

You can create a Data source and transform map for this.

Have the field map for user id as coalesces so it will work as primary key to identify the unique users.

Then have a field map for phone number as to map it use the source script so with the condition that the target phone is empty then only return the value and it will work.

You an check the below article on how to create a transform map.

https://docs.servicenow.com/bundle/tokyo-platform-administration/page/script/server-scripting/task/t...

Please mark my answer as correct based on Impact.

[Peter Bodelier]

Hi @Souvick A 

1. go to system import sets-->load data module
2. enter table name..chose file(.xls file) make sure 1st row in excel match fields in your destination table.so it will automap.
3. then click on create transform link
4. give it a name,select destination table(table in which u want to upload data)
5. click on auto map matching fields..if required fields do not match then click on mapping assist and match fields.
6. set coalesce true to any one field from related list. it will be your unique field
7. click transform

[AnveshKumar M · Accepted solution]

Hi @Souvick A ,

What field you have in excel to identify the user? Is it email, user_name or something else?

You can use Load data module with transform maps to import this data.

1. Load data: Navigate to System Import Sets -> Load data

2. Sample Data

3. Fill in the details as shown below and select your excel file then click submit.

4. You will get the following page, then select create transform map.

5. You will get the following page, fill in the data as shown then save (not submit).

6. Once the form is saved, in the related links section click on Mapping Assist and in the page opened map the fields from excel to fields in user table as shown below and save.

7. Then open the email mapping record from the Field Map  related list and in the page opened, enable the Coalsce check box to true and click on update.

8. In the Transform map page set the Run Script  check box to true and use the code below in the Script field and save.

(function transformRow(source, target, map, log, isUpdate) {
    
	if(!isUpdate){
		ignore = true;
	}

	if (target.phone == '') {
        ignore = true;
    }


})(source, target, map, log, action === "update");

9. Now from the transform map related links page click on transform and in the page opened select the import set (that you loaded in the first two steps) and select the transform which we created now (auto selected) and click on Transform.

10. Now check your user records after the transform is completed.

Please mark my answer helpful and accept as solution if it helped you 👍✔️

[Souvick A]

Thank you Anvesh for explaining in so much details. 

Regards

Souvick