Import Sections

Definition and Main Properties

An import section consists of a set of import properties related to one pair of source and target tables and at least one assignment in a target table. I.e. assignments in a section establish a relation between data from, primarily, one source table and one target table. By applying user-specified variables and LOOKUP function in assignment expressions, the data from other related source tables may be used.

An import job may include one or more sections. Sections within one job are executed sequentially. It is possible to re-order the sections, and also their assignments, using the buttons in the upper right corner.

 

Figure: Example of a defined section to import nodes with assignments from the source data including conditional statements from the CoverTypeNo

New sections are added using the 'Insert section' button at the top. Sections can be deleted by right-clicking on the section's top row.

When importing a MIKE+ database into another MIKE+ database, it is possible to create one section for each table by using the 'Automap sections' button. The button will only create sections for tables related to the active mode (e.g. 'Water Distribution'), and will populate the list of assignments for each section. It will not import "system" tables, which are e.g. used to store the following data:

• MIKE 1D engine configuration
• Fields' status
• Default values
• User-defined column information
• Status codes
• Bookmarks
• Model type settings
• Custom units

These data must therefore by defined manually after the import, if required.

When importing an ICM model setup in .csv files, this 'Automap sections' button will also load a predefined import configuration, providing a default mapping between collection system data in ICM files and in MIKE+ database.

Section Properties

The following is a detailed description of the section definition parameters.

Section Name

When created, a section gets a generic ID (name) Sectionn, where "n" is a simple counter. By double-clicking the section ID turns editable, i.e. user-specified ID may be typed.

Section On/Off Toggle

A checkbox next to Job ID controls if the actual job will be included in the "Run" or not.

Source

Source is ID (name) of the table in the data source specified for the current job. User selects the source table from the drop-down list containing all source tables. The full list of source tables may be reduced (and made easier to navigate) by setting up the Source mask (see above).

When the source file is a CAD file, each layer from the CAD file is divided into source layers per data types. For example, importing from the source layer "LayerA_Polyline" will import only the polylines from LayerA. This list of source tables is supplemented with extra options with format "Model_ElementType", e.g. *Model_Polyline or *Model_Polygon, Selecting one of these options, all source layers with the selected type will be imported. For this reason, only the common attributes (available in all layers with the selected type) can be applied in assignments. This is especially useful when the same type of data is saved in different layers in the CAD file.

Note

The same CAD data may be imported from multiple source layers. This is especially true for Blocks in the CAD file, which are defined by a reference point (location, associated to other attributes) and by a detailed geometry (symbol shown on the map, possibly created with multiple geometry types like polylines and polygons). For instance, blocks data may be imported from the following source layer types:

• Model_ElementType: Can import the parts of the detailed geometry (symbol) with the selected type. Only the common attributes from all layers with the selected type can be imported.
• layerName_Elementype: Can import all geometrical elements with the selected type, from the selected layer. All attributes from this layer can be imported.
• *Model_Point: Can import the reference point of the blocks. Only the common attributes from all point layers can be imported.
• *Model_Block: Can import all reference points from all layers. Only the common attributes from block layers can be imported.
• layerName_Insert: Can import the reference points from the selected layer. All attributes from this layer can be imported.

Target

Target is ID (name) of the table in the data target specified for the current job. User selects the target table from the drop-down list containing all target tables. The full list of target table may be reduced (and made easier to navigate) by setting up the Target mask (see above).

The geometry type of the selected target layer should normally match the geometry type of the source layer. For instance, source polygons cannot be imported to a point layer. It is however possible to import a source polyline layer to a polygon target layer, in which case each source polyline gets closed to create a polygon (the start and end points of the polyline don't have to be at the same location).

Filter

Purpose of filtering is to eliminate unwanted records from import. Filtering is applied to the source before executing the assignment.

Syntax of the filter is the same as SQL WHERE clause. User only needs to type the contents of the WHERE clause, e.g. CustomerID = 'abc', would include only records with specified CustomerID. All other source records will be neglected.

Sort

Sorting is applied to one of source fields (column) before executing the assignment.

The content can only be a source field name, selected from a combo-box.

Distinct

Distinction is applied to source data before executing the assignment. It means that only one (the first) instance of the original source field (column) value will be accepted and all other records containing the same value will be removed.

The content of "Distinction" can only be a source field name, selected from a combo-box.

Transfer Mode

The following transfer modes are available:

• Append
• Update
• Append & Skip existing
• Append & Update
• Overwrite
• Sync

In the following, the actual workings of transfer modes are described and illustrated with some examples.

Append

With this mode, the source data are appended (added) to the current content of the target table, i.e. it preserves original data in the target table.

If there is an assignment for the MUID, the program will ensure that there is no duplicate MUIDs after the import, to fulfill the requirement in MIKE+ that MUIDs must be unique in each table:

• If multiple records in the source have the same ID, only the last one will be imported.
• If an imported record in the source has the same ID as one of the original record in the target table, the source ID will be renamed.

This is illustrated with examples below. Note that the new or modified target content is shown in red fonts. Unchanged target contents are shown in black fonts.

 

Figure: "Append" with "MUID" assignment, applied to the target with some initial contents. The existing data in the target are not changed. The first instance of the duplicate source record ID3 is ignored. Source records ID1 and ID3 are renamed.

 

Figure: "Append" without "MUID" assignment, applied to the target with some initial contents. The existing data in the target are not changed. All source data are imported with a default MUID.

This mode is typically applied when building a model from several sources and/or when updating a target with newly added data from the same source.

Update

For each record in source data, the program looks for the matching records in the target, and for the found matching records it updates any mismatching attribute value. If no match is found in the target, the source record is not used.

If there are duplicated IDs in the source data and the same ID exists in the target, the last duplicate record in the source will be used to update the target.

 

Figure: "Update" applied to the target. Only the existing data in the target with matching ID in the source are updated, i.e. values of attributes set to their values from the source. Note that in case of double ID in the source (ID3), attribute values from the last instance will eventually be applied.

By default, matching records are found by comparing the MUID in the source data with the corresponding target attribute selected in the MUID assignment. When another attribute than the MUID should be used to find matching records (e.g. the Asset ID), then an assignment must be created for this attribute, and this assignment can be used by right-clicking and selecting 'Select as matching key'. The assignment used to find matching records is shown in light blue.

 

Figure: The assignment used to find matching records is shown in light blue. This matching key can be changed from the context menu.

With initially empty target, "Update" operation would not do any change, i.e. the target would remain empty.

This mode is typically applied when maintaining the model data to fit with any modifications of imported data in the source.

Note

The tool cannot update the MUID attribute from existing records.

Append & Skip existing

For each record in source data, the program looks for a matching record in the target. If a matching record is found in the target, then this target record is kept unchanged (the import step is skipped). If not found already being in the target, the record will be appended into database. If there are duplicated IDs in source data, the last one will be used.

 

Figure: "Append & Skip existing" applied to the target with some initial content. Only the missing records are imported whereas records already present in the target are kept unchanged.

This mode is typically applied when importing data from a new version of the source, without changing data imported earlier from a previous version of the source.

Append & Update

For each record in source data, the program looks for a matching record in the target, and for the found matching record it updates any mismatching attribute value. If not found already being in the target, the record will be appended into database as well. If there are duplicated IDs in source data, the last one will be used.

 

Figure: "Append & Update" applied to the target with some initial contents. Initially, the existing data in the target with matching ID in the source are updated, i.e. values of attributes set to their values from the source. Note that in case of double ID in the source (ID3), attribute values from the last instance will eventually be applied. Subsequently, any source record with non-matching ID in the target (ID4) will be appended to the target.

This mode is typically applied when updating data in the target with the source contents, both in terms of changes of already existing data and added new data in the source.

Overwrite

Any data in the target table, before running the import/export job, get deleted. All source data are then appended (imported) to the empty target table. The behavior when appending data is the same as when using the 'Append' mode.

In this mode all source data are being imported.

This is illustrated with two examples below. Note that the new or modified target content is shown in red fonts. Unchanged target contents are shown in black fonts.

 

Figure: "Overwrite" applied to the initially empty target. The target table has the same contents as the source table, except that a duplicate record is renamed to ensure non-duplicate IDs in the target table.

 

Figure: "Overwrite" applied to the target with some initial content. All existing data in the target are deleted and the new content is imported.  The target table has the same content as the source table, except that a duplicate record is renamed to ensure non-duplicate IDs in the target table.

With initially empty target, result of “Overwrite” mode would be identical as “Append”.

This mode is typically applied when started building a model, i.e. when populating target tables "from scratch".

Sync

The program will first do “Append & Update” mode process. When completed, it will also delete all the target records which are not existing in source data.

 

Figure: Result of the "Sync" operation is identical as "Update & Append", except that the record ID5 is deleted from the target, as it has no matching ID in the source table

This mode is typically applied when synchronizing data in the target with the source contents.

Action

"Action" is a command which usually acts on the target records, not on the target values (except for the ‘Iterate’ action).

An action line is included in the current section by "Insert action" button and by selecting a wanted action from the drop-down list.

Action commands available are:

• DeleteRecord
• AggregateGeometry
• Log
• Iterate

DeleteRecord

"DeleteRecord", removes the current record from the target table.

To make it meaningful, "DeleteRecord" is always used with a condition. As such, "DeleteRecord" supports advanced filtering during import, i.e. it is an alternative to a (optional) filter specified in the section header.

Note

The record is first written to the target table, later to be removed if the condition associated with "DeleteRecord" is true.

Consider the following example, where source contains data on two types of pipes (ptype): "Mainline" and "Servicepipe".

 

Figure: Example of a source table and a target table after import including action assignment "DeleteRecord" for ptype = "Servicepipe"

If only pipe with ptype = "Mainline" are to be imported, such import can be achieved by "DeleteRecord" action, and condition ptype = "Servicepipe".

Another example is shown below where all empty cells in the source 'TypeNo' column will be deleted from the target table.

 

Figure: Add an assignment for a specific function (e.g. DeleteRecord)

AggregateGeometry

The geometry for lines is usually stored in a single line of text in MIKE+ (WKT format). The AggregateGeometry action can be used to import e.g. pipes coordinates from geometry tables, where the geometry is instead stored in columns (e.g. pipe ID, Vertices number, X coordinate, Y coordinate) for each vertice of the pipe.

The AggregateGeometry action must be used in combination with the PointFromXY function. After selecting the AggregateGeometry action, open the Expresssion editor, and apply the PointFromXY function which requires two attributes, corresponding to the source columns containing X and Y coordinates.

 

Figure: Using the AggregateGeometry action to import lines shapes from a geometry table

Log

The Log command writes to the MIKE+ log file. The log file can be found in the user folder under "\Appdata\Local\DHI\MIKE+\all-utf8.log".

The Log command is used when creating advanced assignments. When user-specified variables and LOOKUP function are used, it can be useful to see actual values of the variables to ensure that the data are correctly assigned. It is recommended to remove the Log command after completed testing of the assignment.

The Log command can also be used to create error messages when something unexpected occurs during execution of an import job.

The syntax is:

Log(Value)

The command parameter "Value" represents the value to be written to the log file. "Value" can either be an attribute in the assignment source data, a constant, a user-specified variable or a value returned by a specified function.

The Log command can be extended by a condition.

Iterate

Assignments usually import values from one field in the source to another corresponding field in the target. It may however happen that one field in the source combines multiple values in a string, from which the different values are distinguished with a specific separator. In this case, the 'Iterate' action allows to iterate through the different parts of the string, and apply the assignments using each part of the string successively.

The Iterate action must be used in combination with the Split function, which specifies the substring to be used as separator between the different parts. After selecting the Iterate action, open the Expression editor, and apply the Split function which requires two attributes, corresponding to the source string to be split and the substring used as separator.

Once the Iterate action is created, define the assignments to be applied to the resulting substrings. These resulting substrings are obtained from the variable 'Iterate'. Additionally, the variable 'IterateIndex' returns the number of the substring being processed within the current string.

The example below imports tabular data with multiple values for two variables, all stored in a single string, into the 'Curves and relations' editor.

 

Figure: Using the Iterate action to import tabular data from a single string

Note

The 'Iterate' action cannot be combined with another action, i.e. it is not possible to add other actions within iterations.