How to update records from Excel sheet

[Sunny45]

Hello Team,

I have some 12K records which needs to be updated on cmdb_ci_computer table. They belong to various classes (e.g. Servers, Desktops, Laptops). I have host name, and status (Status Needs to be updated) in Excel. What is my best option to update these records? 

Can this be done using script?

Please advise with all steps.

Thanks. 

[Prasad Dhumal]

Yes you can use Transform Script

refer sample code and screenshot :

// reclassify to server based on operating system, you may use any criteria that is suitable
if (source.operating_system == "AIX" ||
    source.operating_system == "HP/UX" ||
    source.operating_system == "Solaris" ||
    source.operating_system == "AIX")
  target.sys_class_name = "cmdb_ci_unix_server";

if (source.operating_system == "Windows 2000 Server" ||
    source.operating_system == "Windows 2003 Server" ||
    source.operating_system == "Windows NT 4.0")
  target.sys_class_name = "cmdb_ci_win_server";

if (source.operating_system == "OS/400" ||
    source.operating_system.toString().indexOf("Linux") >= 0)
  target.sys_class_name = "cmdb_ci_server";

Modify the code according to your requirement.

[Sunny45]

Thanks Prasad for your response.

Do I need to create staging table first by uploading Excel sheet? 

Could you please elaborate on all steps. I am pretty novice on this. 

Thanks.

Sunny.

[Prasad Dhumal · Accepted solution]

I Will suggest you to execute this on 10-15 Records first, to see that you are getting your perfect output or not. DO NOT PERFORM THIS ON 12K RECORDS DIRECTLY.

1) Navigate to System Import Sets > Load Date. Create Staging Table and Upload Excel sheet > Submit

2) Navigate to "Create Transform Map" 

3) Select Target table is Computer[cmdb_ci_computer] and save. So you will get option for Field Mapping.

4) Make sure you are mapping fields correctly, Use Field Maps

5) Run Script should be checked

6) Configure the script according to your requirement. Refer the script which I've provided.

7) Click on Transform

Please tell us if you face any issues.

Thanks

[Sunny45]

Thank you so much Prasad.

I'll give it a try and let you know how it goes. 

Thanks a lot.

Sunny