“I have loads of extra time!”
Said no one—ever
The Data Loading Tools, a free ArcGIS Pro toolbox, streamlines loading data from a source schema to a target dataset and allows you to perform in-process data transformation. This capability is especially useful for use cases such as migrating water utility assets into an ArcGIS Utility Network management system.
his toolbox creates a Data Loading Workspace that contains a collection of Microsoft Excel workbooks used to simplify field mapping and data loading. The Data Loading Tools toolbox requires ArcGIS Pro and Microsoft Excel 2016 or later.
Who couldn’t use a little more time? In a nutshell, the Data Loading Tools, which are free with ArcGIS Pro, streamline the process of loading data from a source to a target dataset. These tools also allow you to transform your data at the same time. And best of all, the process is repeatable. You can transform your data once and reuse that logic again and again. Data Loading Tools can be used to migrate, consolidate, transform, clean, and archive data.
1. Migrate Data
Perhaps this has happened to you: A new data model or schema becomes available with improved features or functionality that your organization really needs. This is great. Then you realize that you will have to migrate your data to take advantage of that functionality. That is not so great. Data migration is famously time-consuming and difficult. That’s where Data Loading Tools comes in.
Let’s say that you’re a utility GIS specialist responsible for moving data from a geometric network to a utility network. Data Loading Tools will allow you to map the data logically from source to target datasets using Microsoft Excel workbooks.
Because this mapping is saved in a series of Excel workbooks, you can migrate data in smaller chunks. You’ll start with a pilot phase to confirm that the logic is appropriate for your migration, and then you’ll use the toolbox to migrate data incrementally into the new data model or schema. You could even apply filters to perform quality assurance checks before you move completely into production or split data from a single source into different target datasets.
2. Consolidate Data
Another great benefit of Data Loading Tools is that you get the tools that you need to consolidate data from multiple data sources into one workspace. Say you’re a data aggregator for your state. You regularly receive parcel data from each county that you must load into a statewide aggregated parcel layer. However, you may frequently receive data that is structured differently than your target workspace. With Data Loading Tools, you’ll define how data should be loaded to your target database, which helps you produce a seamless statewide dataset.
3. Transform Data
One of the greatest Data Loading Tools superpowers is the ability to transform data. More specifically, if you get data from one supplier, you don’t have to ask that supplier to modify the data to conform to your schema. Instead, you can use Data Loading Tools and an Excel workbook to conform the data on your end. The supplier formats the data as they like, you get what you need, and everyone wins.
Take address data, for example. If you’ve ever received address data from multiple suppliers, you know how messy it can be. One dataset may have the full address in a single field. A second dataset may separate the house number and street name from the rest of the address. A third dataset may store the house number and street name in one field, the city in a second field, the state or province in a third field, and the postal code in a fourth field.
To be usable, all datasets need to be transformed. The good news is that Data Loading Tools can help you define your data mapping using easy-to-configure Excel workbooks. You can even write calculation expressions to transform data, all within Excel.
4. Clean Up Data
Data is arguably the most important GIS component. Any analysis is only as good as the data that goes into it. However, clean and ready-to-use data is as elusive as a unicorn. With Data Loading Tools, cleaning up your data doesn’t have to be tedious or time-consuming.
Remember the address data that Data Loading Tools helped you transform? Your address fields are now in great shape, but your street suffixes need a little help. Some of them are abbreviated, some are all caps, and some are abbreviated and all caps.
No worries! Data Loading Tools can help you turn any st, St, or street into consistent all-caps STREET, or whatever format you prefer.
5. Archive Data
Archiving data can be complicated, but it is especially beneficial if you run analyses cyclically (for example, monthly, quarterly, or yearly). Fortunately, Data Loading Tools can help.
If you’re a utility GIS analyst, you’ll find this functionality useful for your yearly inspection cycle. You can use Data Loading Tools to define the mapping between your active workspace and your archive. The next time the analysis cycles back, you—or anyone else on your team—can clear the workspace and fill it with new, active data.
However, not all cycles are calendar based. If you’re an emergency management professional, you know that disasters can occur at any time and that the data from one emergency offers valuable insight that is vital to preparing for the next. After the first emergency passes, you can use Data Loading Tools to archive the old data, and then clear the workspace for new incident data.
These five uses are only some of the repeatable and time-saving ways to put Data Loading Tools to work for you.
Resources
For more information about Data Loading Tools, check out the following resources:
- ArcGIS Solutions help documentation: Data Loading Tools
- Esri Community: Data Loading Tools
- Esri video series: Introducing the Data Loading Tools
About the authors
Mindy Longoni
Joel Smith
