Copying Ports from Excel
You can configure ports and port properties in Excel and copy them into transformation ports in the Developer tool. Port properties include the column name, data type, precision, and scale. You might want to do this when you need to develop or edit a transformation with many ports.
You can copy metadata to the following transformation types:
- •Aggregator
- •Expression
- •Filter
- •Java
- •Joiner
- •Lookup
- •Normalizer
- •Rank
- •Read
- •Router
- •Sequence
- •Sorter
- •SQL
- •Union
- •Update Strategy
- •Web Service Consumer
- •Window
- •Write
Editing Transformations in Excel
When you need to edit a large portion of a transformation, you do not need to change every value in the Developer tool. Instead, you can copy the transformation ports to Excel, change all the values simultaneously using Auto Fill, and then Paste (Replace) the transformation ports back into the Developer tool.
1. In the mapping editor of the Developer tool, select the transformation that you want to copy ports from.
2. To copy the original transformation from the Developer tool, right-click within Ports and click Select All.
3. Copy the ports to an Excel spreadsheet.
4. Make changes within the Excel spreadsheet. If you are changing large portions of metadata, you can use the Auto Fill feature in Excel. This allows you to fill in data based on adjacent cells by dragging the fill handle. See Example: Editing a Transformation in Excel for more information. 5. Copy the metadata from Excel.
6. To update the transformation with the changes, right-click within Ports and click Paste (Replace).
Copying Metadata to the Developer Tool
You can create transformation ports in Excel and then copy them to the Developer tool.
1. Create a mapping in the Developer tool with the required transformations.
2. Define metadata for a transformation in Excel.
3. Copy the metadata from Excel.
4. To move the metadata to the transformation in the Developer tool, right-click within Ports and click Paste (Replace).
The following image shows a sample Excel table and the resulting transformation after you copy the metadata to the Developer tool:
Note: You must confirm that the values in each cell are valid before copying the values to a transformation. For example, a string type cannot have a scale value other than "0." Precision values cannot be words and type values cannot be numbers. If the metadata is incorrect, an error message appears.
Example: Editing a Transformation in Excel
You are developing a transformation and you need to change all the string data types to decimal. Instead of changing each field individually, you make global changes in Excel and copy them to the Developer tool.
- 1. You right-click within Ports, click Select All, and paste the metadata into Excel.
- 2. You change the first data type value from "string" to "decimal" and then use the fill handle to automatically change the remaining cells in the column.
- 3. To update the transformation with the changes, you copy the metadata from Excel, right-click within Ports, and click Paste (Replace).
By using Excel, you avoid having to change each field individually.
The following image shows the process of moving a transformation to Excel, using Auto Fill to change certain values, and then copying the transformation back to the Developer tool:
Rules and Guidelines for Copying from Excel
Consider the following rules and guidelines when you copy metadata from Excel to the Developer tool:
- •You must confirm that the values in each cell are valid before copying the values to a transformation. For example, a string type cannot have a scale value other than "0." Precision values cannot be words and type values cannot be numbers. If the metadata is incorrect, an error message appears.
- •The location that you can copy metadata to depends on the type of transformation that you are updating. For example, the Paste (Replace) option might be unavailable when you right-click in the Properties view of a transformation. However, the option will still be available when you right-click directly within the transformation ports in the editor.
