How to use the Data Translation Tools?
***This workflow works with etlsolutions v0.5.2***
Click Here to learn more about benefits and how to get started with the Data Translation Tools
We have had requests for additional documentation and samples at the UC. A zip pro package has been attached to the thread.
We want to bring data from one file geodatabase to another file geodatabase. This is also known as Extract, Transfer, and Load (ETL). Let me set the stage.
We have a file geodatabase or fgdb for short. Let's call this fgdb, our source. Inside this geodatabase, we have a feature class named Cars. We have three subtypes in this feature class: Red, Blue and Green. There are domains assigned to each of these subtypes. I want all my data in this feature class to shuttle over to my target file geodatabase. This target fgdb has a feature class called Trucks with different schema. Schema is a term that includes attributes, data types, domains, and other data management concepts. The Trucks feature class has three subtypes as well, but they are different than those found in Cars. They are One, Two, and Three rather than Red, Blue and Green.
There are three ways to translate data with the workbook. The first method does a straight field mapping without any domains. The second method uses a sheet as a lookup table for domains. Finally, the last method uses a hard coded value to ‘burn in’ values. I will cover each method in this blog.
The first tool in our Data Translation toolbox is Create Mapping Workbooks.
- Open the GP Tool Create Mapping Workbooks tool within Data Translation toolbox.
- Point each one of your source feature classes to its corresponding target feature class.
- Specify the output folder for your mapping workbooks.
- (Optional) Check the box to Calculate feature count statistics to generate information on what fields have populated data.
Once the tool has run, you will see a Points folder that contains a mapping workbook called Cars and a mapping.xlsx file which will be used later when using the Load Data from Workbook tool. Let's go into how to populate information into these excel workbooks.
Method One: Straight Field Mapping
Step 1:
Open the Cars workbook and navigate to the Mapping sheet. We have columns for targetField, sourceField, and fieldType which are all system derived. The columns, expression, sheet, sheetKeys, and sheetValue are used for methods two and three. This default workbook is configured to transfer data to your new file geodatabase without any translations.
What this means is the domains will be mapped using their codes. For example, Red will not be mapped as anything since there isn't a 0 code in the domain "Type" of our target feature layer. Blue will be mapped as One since they share the domain code of 1. Green will be mapped as Two since they share the domain code of 2.
Step 2:
Before we use the Load Data from Workbook tool to Extract, Transfer and Load our data, it's time to inspect our mapping.xlsx workbook.
Each row in this workbook directs to the tool to: set the source database, set the target database, and set the lookup workbook. This first row was created when we pointed our Cars feature class to our Trucks feature class during the Create Mapping Workbooks section at the beginning of this workflow.
Step 3:
Now that I have inspected everything, it's time to run Load Data from Workbook tool.
- Open the Load Data from Workbook tool in Data Translation toolbox.
- Specify the location of the mapping workbook created from Create Mapping Workbooks tool.
- (Optional) Truncate the target geodatabase before loading.
When we run this tool, we return values of 0, One, and Two.
Method Two: Field Mapping with Lookup
Step 1:
Open the Cars workbook and navigate to the Mapping sheet. In this sheet, we will define what sheets and columns to use as a lookup table. Since I am not using the sourceField for a straight mapping, I will remove Type from the sourceField column In this sheet, the values I have entered are highlighted. In the sheet column, "Type", highlighted in yellow, is used by the Load Data from Workbook tool to locate the correct excel sheet when mapping to Type. In the sheetKeys column, "Type", highlighted in yellow, is used by the Load Data from Workbook tool to locate the correct column(s) on the Type tab when mapping to Type. In the sheetValue column, "NewType", highlighted in orange, is used by the Load Data from Workbook tool to locate which column to use as a lookup table on the Type tab.
Navigating to the Type tab, I have highlighted the Type column in yellow to show the relationship to the user-defined column in keys on the mapping tab previously mentioned. I have also highlighted the user-defined column and values in orange. I entered the domain values 1, 2, and 3 in column C to map to Red, Blue, and Green, respectively. I have also included their domain descriptions in column D. What I have accomplished in this workbook is creating a lookup table for old domain values to new domain values. Where Red Car was using a domain value of 0 to describe Red, Truck One uses a domain value of 1 to describe One.
Repeat steps 2 and 3 above from Method One
Here is our final output.
Method Three: Hard Code Burn-in
Step 1.
Open the Cars workbook and navigate to the Mapping sheet. In this sheet, remove all the values you entered in Method 2. We will simply enter in a hard-coded value of "3" into the expression column. This will burn-in the value of 3 for Red, Blue, and Green.
Repeat steps 2 and 3 above from Method One
Here is our final output.
