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 you:

• Right-click the version controlled item and choose Exportusing Open Data Export Centre from the popup menu
• Choose Exportusing Open Data Export Centre from the Network menu when a network is open
• Choose Open Data Export Centre from the Results menu when available
• Right -click a simulation and choose Exportusing Open Data Export Centre from the popup menu
• Right-click a risk analysis simulation or a risk analysis result object and choose Exportusing Open Data Export Centre from the popup menu
  Note: Risk analysis simulations or risk analysis result objects are not available for cloud databases

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

• Comma and tab separated files
• Access database (.mdb) format
• Oracle database (if, for export without spatial data, Oracle version 8i or newer is installed, or version 9.2 or newer for the export of spatial data)
• SQL Server database (if the Microsoft OLE DB Provider for SQL Server is installed)
• MapInfo MIF files
• ESRI SHP files
• MapInfo TAB files
• GeoDatabase (if you have the appropriate ESRI licence)
• XML
• File Geodatabase (if you do not have an ESRI licence)
  Note: This option is only available with 64 bit versions of InfoWorks ICM.

You can export:

• Data from any version controlled item
• Results from a completed simulation

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. Note: Risk analysis results objects cannot be generated for cloud databases. (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, XML or File Geodatabase: 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 or File 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, XML or File Geodatabase: GeoDatabase or File 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 OutputCONFIGKeyword in 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. Note: 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. Note: 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, File 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 or File Geodatabase OBJECTID Applicable to Geodatabase and file Geodatabase. Use the field in the Internal Field column of the Field Mappings Grid with the Export Field Name of OID / OBJECT ID to locate objects in the Feature Class. Row objects, where the value in the specified Internal Field matches the Geodatabase OID or File Geodatabase OBJECTID, will be updated. If the Internal Field value of an object does not match the Geodatabase OID or File Geodatabase OBJECTID 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 and File 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 Type column. 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 and File Geodatabase only.