Introduction
Migrating data is a task that always seems harder than it should be – until now! Esri developed a python package add-in for ArcGIS Pro which helps streamline the process of migrating data from a source schema to a target schema. The Data Loading Tools (DLT) provides a new Excel-based process for data loading, by harnessing Excel workbooks to map fields and transform attributes values from source to target.
By harnessing the set of Excel workbooks that are generated using the toolbox, time spent migrating data can be reduced. The workbook-based solution also presents a more user-friendly interface (zero code!) than some other Extract, Transform, and Load (ETL) options. The set of workbooks also makes the trial/iteration process a breeze.
Getting Started
There are a few software requirements that must be met before diving into the data loading process. The first two big requirements are having Excel 2016 or later, and ArcGIS Pro 2.4 or later on the computer used for migration. Another crucial requirement is installing the “dltsolutions” python package into a cloned ArcGIS Pro Python Environment. For assistance with these requirements visit this link. After installing the required python package, a new map in ArcGIS Pro can be created with the source and target geodatabases added to the database list for the project.
Execute the Data Transformation
To get started with the mapping of data, open the Create Data Loading Workspace tool nested within the Data Loading Tools toolbox. For this blog I’ll demonstrate migrating a sewer cleanout layer into the Sewer Device layer as part of migrating data into the Utility Network schema. After opening the Create Data Loading Workspace tool, the source layer will be the “wwCleanouts” layer within the currently maintained file geodatabase and the target layer will be the “SewerDevice” layer in the Sewer Asset Package file geodatabase.
After mapping one or more layers and executing the tool, a new folder named DataLoadingWorkspace will be created with the generated set of Excel workbooks. Within the folder matching the geometry of any of the mapped layers is an Excel workbook which can be used for specific field/domain conversion.
When migrating data from one feature class to another using the Data Loading Tools, there are three options: Field-To-Field (without transformation), Field-To-Field (with transformation) and static value calculation. Expressions can be used to populate values from a source field to a target field without transformation (see “globalid” and “assetid” fields in picture below). The Lookup columns (LookupSheet, LookupKeys, and LookupValue) can be used to map source to target fields with some transformation of domain values (see “ownedby” and “maintby” field in picture below). Another option is to write a default value into that field for every feature being migrated and skip using a source field to populate values (see “assetgroup” field in picture below).
Watch our video to see the three options available for mapping values.
After filling out the workbooks for one or more of the layers, the Execute Data Load tool within the Data Loading Tools toolbox can be used to perform the final loading and transformation of the data. The Data Reference workbook within the DataLoadingWorkspace folder that hold all the parameters of the various layer/mapping workbooks will be the only file needed to run the tool. Upon successful execution of the Execute Data Load tool the target layer(s) will be populated and ready for some QA/QC checks. If any transformation errors are observed, modify various portions of that layer’s corresponding workbooks then re-run the Execute Data Load tool. During the iterative process of QA/QC for the truncating the previously migrated data can be done manually or automated during the load using the TargetDeleteQuery column in the Data Reference workbook (more information can be found here).
The Data Loading Tools provided by Esri offer an array of simple to complex options for data transformation, all the while keeping the workflow user-friendly by harnessing Excel workbooks for execution. Today we’ve covered some of the basic requirements and uses for the ETL workflow with DLT, give it a try and transform your data migration workflows!
Have more questions or need help with the Data Loading Tools? Give us a call at 877.377.8124 or drop us a line on our Contact page.