There are a few times when I am working on a project in my test or sandbox environment. Part of that project involves loading or creating data into the instance that I only need to import one time. In those cases, I have just gone to the table, or record of interest and used the “Export –> XML” option. This will then download an XML Update Set file that can be loaded into another instance. This is great unless you are dealing with a project that involves many tables.
In order to solve this problem, I have created a library, a processor, and tool to make it possible for me to export all of the records (and their attachments) from all of the tables that are related to my project. Not only that, but I can set the export to use a filter on the data before it is exported.
All of this is included in a free, downloadable update set listed at the end of this article.
Upon installing the update set, you will be able to export table data in two different ways: 1) A GUI tool, and 2) A URL with GET parameters
Please Note: You must have the ADMIN role for any of these features to work properly.
The GUI Mutli-Table Data Export Tool
The update set will create a module inside of the “System Definition” application. The name of the module is: Export Table Data
This module will display a UI Page that allows you to select the tables you wish to export.
Simply enter the filename that you want to have generated by the export. The tool will automatically put the file extension on for you.
To select the tables you wish to export, use the Control or Shift key to select the tables you wish to export into the same file.
The limitation of this tool is that ALL of the data will be exported from these tables. You don’t have the ability to filter the data before the export takes place.
URL and GET Parameters
If you want to save a URL for future export use, you can export the table data via a simple link. The format for this is as follows:
Path:
export_table_data.do
Parameters:
filename: the desired filename. No file extension is needed.
tablesCSV: a comma separated list of table names and encoded queries. If you wish to have a filter on the table data, you include the encoded query with the table name. You need to separate the two with a | symbol. (eg. sys_users|active=true^country=US)
Example:
export_table_data.do?filename=usersInUS&tablesCSV=cmn_location,sys_users|active=true^country=US,
cmn_department
This will export all location and department records as well as all Users that are active and have a country code of “US”. The resulting filename will be: usersInUS.xml
Loading the data into the instance
Once you have a downloaded export file, you can log into your other instance and browser to the “System Update Sets” application and choose the module, “Retrieved Update Sets”.
Upload the xml file in the UI. The data will automatically be loaded into your instance.
Please be aware that there is no “Back Out” functionality for these types of uploads. Once you load this data, there is no easy way to back out of it.
Disclaimer / Caution
Please use caution when exporting data as this can put a heavy load on your instance if you export irresponsibly.
Please use at your own risk! Exporting data from certain ServiceNow tables could corrupt another ServiceNow instance. Only export data from tables that you fully understand.
Download the Update Set
TableDataExporter-0.0.2: removed Packages calls
Multi Table Data Export Tool
Fabulous work John, I can see many uses for this!
Good one John, Thank you!
Beautiful! 🙂
Would have needed something like this many times in the past, and as this seems to work even on exports exceeding the limited 50k records, it’s really useful in many cases.
Great work John!
Thanks John.
Does this tool still work? What builds of Service-Now does it work with? After I submitted the request to export through the UI, nothing happens. Shouldn’t the download of XML have kicked off?
How do you remove this tool if you decided to?
Thanks.
@Jay…I just tested this in my Calvary Instance and it works just fine. If you decide you want to remove the tool, just deactivate it from the System Definition application menu.
Hi James,
We need only the column name ,
Could you help me to get the Service-Now table in XML format like below with relationship,
INTEGER
CHAR(50)
CHAR(30)
COD_PER
Hi James,
I tried to make this working in Fuji build, but it seems its not working fine.
If you want i can give you new update set that has changes but the download file only contain same message.
”
”
—
Cheers,
AR
Akash, that would be great if you would be willing to share!
This has to be the most useful tool to-date….yes in 2017….I vote this as the most useful tool. Great work sir!
Replace the Packages call on line 1 of the “Export Table Data” Processor Script to make this work post-Calgary.
Find:
var tables = “”+Packages.com.glide.util.StringUtil.urlDecode(g_request.getParameter(“tablesCSV”));
Replace with:
var tables = “”+decodeURI(g_request.getParameter(“tablesCSV”));
Thanks Sheldon! You uncovered a linking problem I had on this blog post. It was pointing to an old update set. I have refreshed the link to point to a new file that contains updates that removes the Packages requirement. Thanks for your input!
Is this update set will work on Landon Release?
Yes, this works in London. It’s a fantastically useful tool!
Useful Tool… Thanks!