Open Data Export Centre Dialog

This dialog is used when exporting data to external data sources via the Open Data Export Centre. It is displayed when Export | using Open Data Export Centre is selected from the Network menu.

The Open Data Export Centre allows you to export data from InfoWorks ICM in a variety of formats. These are:

You can export:

The Open Data Export Centre dialog can be opened in several different ways:

 

The Open Data Export Centre

Clickable Image

Open Data Export Centre Options

Dialog Section

Description

Table to Export Data From

Option

Description

Table dropdown list

Select a table to export data from.

Unlike the Open Data Import Centre, this does NOT include arrays within tables. These are handled in a different way by the Export Centre.

There are two additional options - All Nodes and All Links. If these are selected then all nodes and all links will be exported, or all selected nodes or links if the Selection only option is selected. If either of these options is selected then all fields available for one or more node or link types will be available. If a field is selected in the field list which is not available for one or more node or link types, then that field will be blank whenever a node or link for one of those types is exported.

Selection Only

Enabled if the network / results are open in a GeoPlan.

Check this option to export the current selection only.

Time For Results

Option

Description

Choose Time button

Enabled if the Open Data Export Centre is invoked when you right-click on a simulation when the results are not currently open in a GeoPlan.

(If the results are currently open in a GeoPlan, the button will be disabled and the current time displayed in the GeoPlan will be used.)

Click the button to select the time for which results values will be exported.

Time

Time for which results values will be exported.

If the results are currently open in a GeoPlan, this will be set to the current time displayed in the GeoPlan.

If maximum results are currently displayed, the time will be set to Max Results and maximum results will be exported.

Choose return period

Option

Description

Return period

Enabled if the Open Data Export Centre is invoked when you right-click on a Risk analysis results object when the results are not currently open in a GeoPlan.

(If the results are currently open in a GeoPlan, the button will be disabled and the current return period displayed in the GeoPlan will be used.)

Click Return period to choose the appropriate period. A Multiple Selection dialog is displayed from which you can select a return period for which results values will be exported.

The selected return period is displayed in the adjacent field.

Export Data To

Option

Description

Export Type

The type of data file to which you want to export your data.

The ESRI SHP (Shapefile) and MapInfo MIF file options are available regardless of which GIS component you are using within InfoWorks ICM.

The GeoDatabase option will only be available if you have a licenced copy of the ESRI software on your computer.

When exporting to Shapefile, projection format PRJ files will be exported where there is projection information available. See Export to GIS File Format for further details of PRJ file export.

File

The name of the file to which data is to be exported. Either type in the path of the file or click to browse for the file location.

When exporting to an Oracle database, this displays the Select Oracle Data dialog.

When exporting to an SQL Server database, this displays the Select SQL Server Data dialog.

Feature Class

Enabled when exporting to Access Database, Oracle, SQL Server, GeoDatabase or XML:

Oracle Database

The name of the Feature Class to which data is to be exported.

When the export is performed, if the Oracle Feature Class does not exist, it will be created and the Feature Class added.

An existing Feature Class can also be selected for updating. Only tables for which the user has the appropriate updating permissions in Oracle will be available for selection.

See the Exporting Data to Oracle topic for further information.

SQL Server

The name of the Feature Class to which data is to be exported.

When the export is performed, if the SQL Server Feature Class does not exist, it will be created and the Feature Class added.

An existing Feature Class can also be selected for updating.

See the Exporting Data to SQL Server topic for further information.

GeoDatabase

The name of the Feature Class to which data is to be exported.

When the export is performed, if the Geodatabase (or Feature Dataset if it is specified) does not exist, it will be created and the Feature Class added. If a Feature Dataset is specified, and exists, the Feature Class will be added to the existing Dataset.

An existing Feature Class can also be selected for updating when browsing for a Geodatabase from the File field.

See the Exporting Data to GeoDatabase topic for further information.

XML

Defines the top level tag defining the start and end of the exported data. See the Exporting Data to XML topic for further information.

Feature Dataset

The Feature Dataset box is enabled when exporting to GeoDatabase or XML:

GeoDatabase

Optional. The name of the Feature Dataset to which data is to be exported. You will also need to define a Feature Class.

When the export is performed, if the Geodatabase does not exist it will be created and the Feature Dataset and Feature Class added. If the database already exists, the Feature Dataset will be added to the existing database.

An existing Feature Dataset can be selected when browsing for a Geodatabase from the File field.

See the Exporting Data to GeoDatabase topic for further information.

XML

Defines the second level tag defining the start and end of exported data for a single object, such as a node or a link. See the Exporting Data to XML topic for further information.

ArcSDE Keyword

This is the Output CONFIG keyword used when importing data to ArcSDE via a script. Look up OutputCONFIGKeywordin the ArcGIS help.

Script File (optional)

Scripts can be used to refine the export process. This is an advanced option.

Contact Innovyze for further information on using a script to control export.

Units Behaviour

Select from User Units to export values using the current display units, or InfoWorks ICM Units to export values as InfoWorks ICM native units.

Field Mapping Configuration buttons

Use the Field Mapping Configuration buttons to apply saved mappings to the Field Mapping Grid:

Option

Description

Load Config

Load the grid settings from a configuration (CFG) file.

Save Config

Save the grid settings to a configuration (CFG) file. Grid settings for all tables will be saved.

Note

Only settings for the grids and Updating Options settings are saved. Other dialog options should be set to appropriate values before export.

Clear Config

Clear the settings of the currently displayed grid.  Settings for other tables will be retained.

Auto-Map

Populate field mapping grid with all available fields for export, excluding flag fields.

This will include all the available fields for the object available in the pull down list, including joined fieldsand results fields if appropriate.

AutoMapping will not allow you to map fields in arrays stored within a network object. You will have to use the Add Row button and map these fields manually. See the Exporting Array Data topic for more information on array fields.

Titles for the fields are automatically generated. If you hit one of the Automap buttons with ESRI Shapefiles as your selected output, the names will be truncated to the maximum allowable length for Shapefiles. If you use AutoMap, and then change the output type to Shapefiles later, the field titles may be too long for export.

Warning

Note that this list can easily get very large, particularly if you select All Nodes and/or All Links. Including all possible fields for all object types (including joined and results fields) could easily exceed the number of columns that can be displayed in Microsoft Excel or ArcView 3 for example.

Auto-Map (with flags)

As Auto-Map, but with flag fields included.

Field Mapping Grid

The field mapping grid allows the mappings for individual data fields to be set up.

The grid contains a variable number of columns. Some columns only appear for some data types, and some columns will be disabled depending on the values of other columns.

See the Field Mapping Grid section below for further details.

Add Row

Adds a row to the field mapping grid to allow another field to be specified.

Export

Validates the field mapping grid and then does one of the following:

  • exports the data if there were no errors or warnings
  • brings up a list of warnings and asks you if you want to continue, and then exports the data if you say yes
  • brings up a list of errors and possibly some warnings – typical errors include:
    • loading a configuration containing results fields when you do not have results open
    • loading a configuration containing Script fields but not loading the script

Once the data is exported, any errors encountered are displayed in a dialog
Append to existing data

This option applies when exporting to comma separated and tab separated files, Access, Oracle, SQL Server and GeoDatabase format only.

Exported records are added to an existing table/file instead of creating a new table/file.

Cannot be used in conjunction with Updating options.

Warning

The field mapping configuration used to export the table originally should be used when appending data to the table. The append functionality does not support checks for existing fields and addition of new fields for all formats.

Use report mode for arrays

When exporting a value array, all the data values are exported. There are two methods of organising exported value data, and these are controlled by the Use report mode for arrays check box.

When exporting a structure array to XML, the Use report mode for arrays check box controls the format of this exported array.

For further information, see the Exporting Array Data topic.

Updating and Delete Options

The Updating options apply when exporting to Geodatabase, Oracle or SQL Server only.

The options will be enabled if an existing Feature Class is selected in the Export Data To section of the dialog (and the Append to existing data option is unchecked). See the Exporting Data to GeoDatabase , Exporting Data to Oracle and Exporting Data to SQL Server topics for further information.

Option

Description

Update Based On

Select the field(s) to be used to locate objects to be updated in the specified Feature Class:

Asset ID

Use the Asset ID field to locate objects in the Feature Class

Primary Key Field(s)

Use the InfoWorks ICM primary key fields to locate objects in the Feature Class e.g.

  • Nodes - Node ID
  • Links - US Node ID, Link Suffix

Geodatabase OID

Applicable to Geodatabase only.

Use the field in the Internal Field column of the Field Mappings Grid with the Export Field Name of OID to locate objects in the Feature Class.

Row objects where the value in the specified Internal Field matches the Geodatabase OID will be updated. If the Internal Field value of an object does not match the Geodatabase OID it will be treated as a new object.

Only Update Existing Objects

Check the Only Update Existing Objects box to only update objects that exist in the specified Feature Class.

If this box is unchecked, objects that do not exist in the specified Feature Class will be added.

Delete Missing Objects

If this option is checked, InfoWorks ICM will search for missing objects of the relevant type and delete them from the external data table at the end of the export process. (A missing object is an object that is in the external Data source table but is NOT in the InfoWorks ICM Table To Export Data From.).

The update key selected from the Update Based On dropdown will be used to search for missing objects.

All missing objects found will be listed and a prompt for confirmation of deletion will be displayed before any objects are deleted.
Update using changes from a previous version of the network

Check this option to update objects in the external data table with differences between the current version of the network and a previous version of the network.

Either type in the version number of the previous network, or select a network from the Select commit dialog, (displayed by clicking on the button).

On export, InfoWorks ICM will compare the current version of the network with the selected previous version of the network. Differences in objects, identified by the comparison, will be applied to the external data table.

Field Mapping Grid 

The grid allows the mappings for individual data fields to be set up. The grid contains a variable number of columns. Some columns only appear for some data types, and some columns will be disabled depending on the values of other columns.

Column

Description

Export Field Name

The name used for the column, either in the database or file for GIS or database files, or in the first row for CSV and tab separated data files.

If this field is left blank a sensible name will be generated when the file is exported. Therefore you only need to put something in here if you do not like the default value.

Field Type

There are currently 5 field types:

Field Type

Description

Field

A value from a field in the database for the object being exported, for example name, x coordinate, y coordinate

SQL

An SQL expression that can be evaluated and return a value.

Constant

A constant value – a value that you choose that will be the same for every object in the table.

This is typically useful if you:

  • want to import the data into some other system and want to import a value that is the same for everything in this particular table
  • need such a value for a report
  • want a placeholder that you can then replace with an Excel formula

Special

There are only a few special values:

  • SimulationTime - the simulation time if you are exporting results
  • TableName - the table name for the object being exported ( the internal version of the name)
  • TableDescription - the table description(a user friendly version of the table name)
  • NetworkName - the name of the network from which the object is to be exported
  • NetworkID - an internal number which identifies this network in the database
Note

If either the NetworkName or NetworkID is selected when performing an update export, then it is assumed that the specified field is part of the Primary Key Fields (along with the fields specified in Update Based On) and is used to identify which objects are to be updated.

Tip

The Network ID can be displayed by right clicking on the applicable network in the Explorer window and selecting Properties from its popup menu.

These special values are likely to be particularly useful if you are exporting things of more than one type, such as All Nodes or All Links

Script

A Ruby Script function from the loaded .rb file can be executed

Geometry

The geometry type (e.g. point, polyline, polygon) of the object being exported.

Applicable to Geodatabase only.

Data Type

The data type column allows you to override the normal export data type for this data field.

This column is hidden for comma and tab separated variable files. Data is always exported to these file types as text.

The possible data types are:

  • Boolean
  • Date
  • Text - up to 100 characters
  • Memo - text type field normally used for strings longer that 100 characters. Can hold about 10,000 words
  • Short - 2 byte integer
  • Long - 4 byte integer
  • Single - 4 byte floating point
  • Double - 8 byte floating point
  • GUID

By default, fields are exported in the same format as they are held in InfoWorks ICM. These formats are predominantly Text and Double.

You can choose to export any field in any format. If you try to export a string that cannot be represented as a number in one of the number formats, you will get a warning message after the export. You will get a warning message for every failure, so if you have a long list of objects it could be a long list.

It may be perfectly legitimate for you to export text fields as numbers. For example, if:

  • your node IDs are all numeric
  • you have stored numbers in the user text fields

If you export a text or numeric field as a Boolean value, the Boolean value will be set to true if:

  • a string field is not zero length
  • a numeric field has a value and the value is not zero

Otherwise, the Boolean will be set to false.

Length

Applicable to fields of Data Type Text only.

This column is hidden for comma and tab separated variable files.

The default length for text fields is the maximum length of the field in InfoWorks ICM. If you are exporting something using SQL or Ruby Script into a text field, InfoWorks ICM scans the exported text, and sets the length of the longest string as the default.

If you choose a length for a text field and try to export strings that are longer than this maximum, the exported text will be truncated and you will receive a warning for each occurrence.

Precision

Applicable to fields of Data Type Text only.

There are two reasons for changing the default precision:

  • InfoWorks ICM may store a value at a higher precision than you want to export.
  • If you are calculating the value with a Script or SQL, InfoWorks ICM cannot calculate a sensible precision for the value. It is up to you to set an appropriate precision.

Internal Field (or other data)

The entry in the Internal Field column will vary depending on the value selected in the Field Typecolumn.

Field Type

Description

Fields

The dropdown list contains all the available fields. These fields include:

  • fields for the selected object type
  • fields for any object that can be "joined" to the selected object type

For example, joined fields may include:

  • the upstream and downstream nodes for a link
  • simulation results if results are loaded
  • simulation results at a joined object such as upstream and downstream nodes of a link

You are also allowed to export fields from arrays. This is the tabular data that can only be viewed in grids on an objects property sheet.

SQL

Enter an SQL expression

Constant

Enter a constant value

Special

Choose from the available set of special values:

  • simulation time if you are exporting results
  • the table name for the object being exported - the internal version of the name
  • the table description - a user friendly version of the table name

Script

Choose one of the available functions in the loaded script file

Geometry

Field is disabled. The geometry type of the object will be exported.

Applicable to Geodatabase only.

Open Data Export Centre

Exporting SQL Expressions