Mapping Source fields to Destination fields

Overview

In Etlworks, it is possible to map SOURCE fields to DESTINATION fields, exclude fields, add fields to the DESTINATION which do not exist in the SOURCE, and more.

Field names

Etlworks can automatically read field names from the source and destination objects. It then populates the SOURCE and DESTINATION drop-downs. You can manually enter the field name if it is not available in the drop-down.

Read about metadata selector.

It is also possible to define a template for the SOURCE or DESTINATION so that Etlworks  won't have to connect to the actual data source.

If a field name is not available in the drop-down, click Clear Cache at the bottom of the screen. Etlworks will reconnect to the data object and attempt to reread field names. You can always manually enter the field name. 

The default Mapping

• If there is no Mapping, the DESTINATION fields will be exactly the same as the SOURCE fields.

• If a SOURCE field is not part of the Mapping, it will be automatically added to the DESTINATION (the default mapping).

• A SOURCE field must be manually excluded to not appear in the destination.

The default mapping is not used with the nested mapping. Read about the nested mapping.

Select field from the list

Use a metadata selector to select a field from the available fields for the specific source or destination Connection.

Exclude a field

A field must be manually excluded to not appear in the destination.

Select or enter the SOURCE field, and click the Exclude check box to the right.

Calculated fields

By default, a DESTINATION field gets its value from its corresponding SOURCE field. The value of any field can also be changed or calculated using JavaScript, Python, and SQL (if the destination is a relational database).

Select or enter the DESTINATION field, click the pen icon to the right, and enter a program in the scripting language to calculate the field's value.

Use scripting languages

• How to use JavaScript or Python to calculate a field's value.
• Examples of calculating field value using the scripting language
• How to use native SQL to calculate a field's value.
• How to use SQL to Lookup Fields' value.

Add a pair of source and destination fields

To add a new pair of SOURCE and DESTINATION fields, click +. Select or enter the SOURCE and DESTINATION field names.

Add a field to the destination that does not exist in the source

To add a field to the destination that does not exist in the source, click the add field, enter the DESTINATION field name, and click the Add Field checkbox. Continue by clicking the pen icon and entering JavaScript to calculate the field value.

Exclude a field from the Mapping

To exclude a field from manual Mapping, simply delete it. The system will fall back to its default Mapping.

Change the field data type

By default, the destination field gets its data type from the source field. To change the destination data type, click the Data type button, select the Data type from the drop-down, and if needed, enter a Database specific type (for example, varchar(255)). You can also specify if the field is nullable.

Reorder Fields

Drag to reorder files in the Mapping. 

If the field already exists in the source, it will retain its position. The only exclusion if the destination is the CSV file, in which case it is possible to configure the destination (TO) CSV Format to honor the order of fields in the Mapping.  

Create Mapping

Etlworks can automatically create a default Mapping by simply reading the fields from the source object and Mapping them to the same fields in the destination. You can then manually change the source and destination field names.

You can choose to Create regular (flat) mapping or Create mapping with nested destination:

Clicking Create Mapping overrides the current Mapping.

Remove Mapping

You can remove all manual Mapping by clicking Remove Mapping.

Multiple columns with the same name in the destination

When the destination is not a database, the mapping editor allows the creation of a destination document (for example, an Excel file) that has multiple columns with the same name. 

Example:

This feature is not supported when the destination is a relational database or cloud data warehouse, such as Snowflake. 

Work with nested datasets

When Etlworks reads a nested data object, it parses it and represents the fields in a tree-like structure:

Use Make this field a child of the one above and Move child field up one level to control the level of nestedness for a specific field. You can also drag and drop fields up and down.

Read more about nested mapping and other techniques for working with complex nested datasets.