Updating data in ServiceNow using XML Export – Import

General Add comments
by:

When working with databases it is common practice to import data into the system coming from a CSV or Excel formatted file.
ServiceNow has the tools to effectively import new data.
No problem.
But what if the data delivered in the Excel sheet has to be merged with data that is already in the system?!

At that point there are two options. One is to define functionality in ServiceNow to merge data.
But is also possible to export the data in XML-format from ServiceNow, merge the data in Excel and perform a simple XML import (right click on a list view header).

The steps needed to perform this export-import is discussed below.

In this case a list of new phone details are provided via Excel and have to be loaded into ServiceNow.
The only additional attribute in the Excel is the email address.
The email address will be needed to link the data in ServiceNow and the Excel together.

Step1

Create XML export of users to be modified.

Step2

  • Open an empty Excel document
  • Import XML in Excel. The trick is to preserve the XML formatting coming from Service-Now.
  • Remove columns that are not relevant. In this case only the following fields are relevant:

                    -email (column A, needed for VLOOKUP within Excel)
                    -sys_id (column B, needed for import into ServiceNow)
                    -phone (column C)
                    -mobile_phone (column D)

Step3

Add sheet “phoneNumbers” that contains the data from the provided Excel:
     -email (column A)
     -phone (column B)
     -mobile_phone (column C)

Step4

  • Go back to the first sheet.
  • Set the cell format of the columns to General.
  • Define look up formulas to get the phone data into the first sheet.
  • For the phone field in cell C2 the formula would look like: =(VLOOKUP(A2;phoneNumbers!A$2:B$500;2; FALSE))
  • For the mobile_phone field in cell D2 the formula would look like: =(VLOOKUP(A2;phoneNumbers!A$2:C$500;3; FALSE))
  • (Where 500 is the max number of users that have to be updated).

Step 5

File/Save as/ XML data

Step 6

Import in ServiceNow by right clicking on any list view header.
Since the sys_id is in the XML, the existing records will be updated.
Note that only fields not provided by the XML will be updated, other field values will be preserved.

If you have any question, you can contact me by mail on KP.Meinesz@2e2.nl

3 Responses to “Updating data in ServiceNow using XML Export – Import”

  1. Antony Says:

    Dear KP,

    I am new to ServiceNow and while googling, come across http://www.snc-blog.com and found it very-2 informative.

    It’s a nice article. While practicing it, I am unable to accomplish Step 6 as I don’t see Import option in any list view header.

    Please let me know how to import the ‘Save As’ XML file in ServiceNow.

    Thanks a lot for your swift reply!

    Antony Mirza

  2. Lam Hoang Says:

    Hi, Antony Mirza

    As in the current ‘Berlin’ release you need to have security admin rights to import XML files. Please see: http://wiki.servicenow.com/index.php?title=High_Security_Settings

    regards,
    Lam

  3. Antony Says:

    Thank you Lam, I got it now!

Leave a Reply