Exchange Scripts - Open Data Export Centre

This topic contains an example script for exporting data from an InfoAsset Manager network to an external database using InfoAsset Manager Exchange. The functionality corresponds to that provided by the Open Data Export Centre within the normal InfoAsset Manager application.

The example below provides a text version of the example script that can be copied and pasted into a text file:

Example

#
# Example Script to Data to a Comma Separated Value File
#
begin
        puts 'Start InfoAsset Manager  to Comma Separated Value File'
        #open an InfoAsset Manager database
        #
        db = WSApplication.open(nil,false)   # nil indicates the last opened database
        # get the network from the object type and id
        #
        nw = db.model_object_from_type_and_id('Collection Network',23)
        # check for changes made by other users 
        current_commit_id = nw.current_commit_id
        latest_commit_id = nw.latest_commit_id
             if(latest_commit_id > current_commit_id) then
                     puts "Updating from Commit ID #{current_commit_id} to Commit ID #{latest_commit_id}"
                     # get all changes in the latest version of the network
                     nw.update
             else
                     puts 'Network is up to date'
        end
        #
        # set options
        #
        options=Hash.new
        options['Callback Class'] = nil
        options['Error File'] = 'csvexp.txt'
        options['Image Folder'] = 'c:\Images'
        options['Units Behaviour'] = 'Native'
        options['Report Mode'] = true
        options['Export Selection'] = false
        # export network data to csv format
        #
        nw.odec_export_ex(
             'csv',                      # export data format => Comma Separated Value
             'in_exp.cfg',               # field mapping config file
             options,                    # specified options override the default options
             # table group
             'node',                     # first  table to export
             'node.csv',                 # export to file name
             # table group
             'pipe',                     # second  table to export
             'pipe.csv')                 # export to file name
        puts 'Done'
# handle exceptions
#
rescue Exception => exception
        puts "[#{exception.backtrace}] #{exception.to_s}"
end

Click on the image below for additional information on each section of the example script:

Update network to include other users changes

Before exporting data ensure that the network is up to date so that changes by other users are included in the export.

Example

# check for changes made by other users
        current_commit_id = nw.current_commit_id
        latest_commit_id = nw.latest_commit_id
             if(latest_commit_id > current_commit_id) then
                     puts "Updating from Commit ID #{current_commit_id} to Commit ID #{latest_commit_id}"
                     # get all changes in the latest version of the network
                     nw.update
             else
                     puts 'Network is up to date'
end

Where:

• current_commit_id returns the commit id for the local network
• latest_commit_id returns the id of the most recent commit by any user
• update gets updates made by other users

Export options

The export options are defined in a hash. The options correspond to the options that are set from the Open Data Export Centre dialog in the main application.

• It is only necessary to define an option if you do not want to use the default for the option.

Export Option Key Name	Type	Default	Description
['Callback Class']	Ruby Class	nil	Ruby class used for callbacks Parameter can be nil or a Ruby class
['Error File']	String	nil	Path of Error log file Parameter can be null or ' '
['Image Folder']	String	nil	Path of folder to which images will be exported Parameter can be null or ' '
['Units Behaviour']	String	Native	Permitted values are: 'Native' and 'User' Corresponds to 'Units Behaviour' parameter in Open Data Export Centre in main application.
['Report Mode']	Boolean	True	Corresponds to 'Use report mode for arrays' parameter in Open Data Export Centre in main application.
['Append']	Boolean	False	Corresponds to 'Append to existing data' parameter in Open Data Export Centre in main application.
['Export Selection']	Boolean	False	Corresponds to 'Selection only' parameter in Open Data Export Centre in main application. To create a selection for export use: nw.select_sql(<table>,<sql query>) Where: table is InfoAsset Manager table name e.g. nw.select_sql('pipe','location IsNull') (See the Using SQL section in the main help for further information on SQL Queries in InfoAsset Manager)
['Previous Version']	String	nil	Corresponds to 'Update using changes from a previous version of the network parameter in Open Data Export Centre in main application.

Export from network to external source

Export network to external data source:

nw.odec_export_ex(export_parameters)

The export parameters correspond to the parameters that are set from the Open Data Export Centre dialog in the main application.

The first two export parameters are common to all external data source types.

The parameters that follow are used to export any number of InfoAsset Manager tables to the external data source. These parameters differ according to the type of data format to which data is being exported.

Common export parameters

Parameter Type	Example	Description
String	CSV	External data format Possible values: "CSV", "TSV", "XML", "MDB", "SHP", "MIF" "GDB", "ORACLE", "SQLSERVER"
String	in_exp.cfg	Path of Configuration (CFG) file The CFG contains the mappings between the fields in the InfoAsset Manager Network and the external data source. The quickest and easiest way to create a valid configuration file for export is to use the Open Data Export Centre in the main InfoAsset Manager application.
String	options	The hash in which the export options are defined.

Export to file parameters

The export parameters used to define the destination to which the InfoAsset ManagerNetwork data is to be exported differ according to the type of data format to which the network data is being exported. Examples are provided below.

Tip: To specify an InfoAsset Manager table name, use the name that appears in the configuration file (exported from the Open Data Export Centre in the main program). The name in the configuration file is the same as the table name that appears in the Open Data Export Centre dialog with spaces removed, e.g. CCTV Survey.

CSV / TSV / SHP / MIF / TAB

# table group
'node',	# InfoAsset Manager table to export
'node.csv',	# export to file name

(Parameters are all of type String.)

MDB

# table group
'node',	# InfoAsset Manager table to export
'in_node',	# export to table name
'features.mdb',	# export to file name

(Parameters are all of type String.)

XML

# table group
'pipe',	# InfoAsset Manager table to export
'all_pipes',	# export to layer name
'pipe_object',	# export to feature class
'network.xml',	# export to file name

(Parameters are all of type String.)

GDB

Requires installed and licenced ArcGIS:

Personal Geodatabase

# table group
'node',	# InfoAsset Manager table to export
'nodes',	# export to feature class
'west_end',	# export to feature dataset
true,	# export update? if true, then the export target must exist
nil	# ArcSDE configuration keyword (always set to nil for Personal Geodatabases.)
'connect.sde'	# export to file name or connection name

(Export parameter 'export update?' is type Bool. All other parameters are type String.)

File Geodatabase

# table group
'node',	# InfoAsset Manager table to export
'nodes',	# export to feature class
'west_end',	# export to feature dataset
true,	# export update? if true, then the export target must exist
nil	# ArcSDE configuration keyword (always set to nil for File Geodatabases.)
'database.gdb'	# export to file name or connection name

(Export parameter 'export update?' is type Bool. All other parameters are type String.)

SDE Database

# table group
'node',	# InfoAsset Manager table to export
'nodes',	# export to feature class (always use the unqualified name - the feature class names as they appear in ArcGIS/ArcCatalog)
'west_end',	# export to feature dataset (always use fully qualified name)
true,	# export update? if true, then the export target must exist
'SDO_GEOMETRY'	# ArcSDE configuration keyword (ignored if this is an export update)
'database.mdb'	# export to ArcSDE using ArcSDE connection file name

(Export parameter 'export update?' is type Bool. All other parameters are type String.)

ORACLE

Requires installed and licenced Oracle Client :

# table group
'node',	# InfoAsset Manager table to export
'in_node',	# export to oracle table name
'gmsora',	# export to oracle table owner
true,	# export update? if true, then the export target must exist
username,	# oracle username (or ARGV[1] to take user name from input parameter)
password,	# oracle password (or ARGV[2] to take passwrod from input parameter)
'//power:/orcl'	# oracle server

(Export parameter 'export update?' is type Bool. All other parameters are Type String.)

SQL SERVER

Requires installed and licenced SQL Server Client :

# table group
'node',	# InfoAsset Manager table to export
'in_node',	# export to SQL server table name
'server'	# export to server
'instance',	# export to SQL server instance
'database',	# export to SQL server database
true,	# export update? if true, then the export target must exist
true,	# integrated security?
username,	# SQL username (or ARGV[1] to take user name from input parameter)
password,	# SQL password (or ARGV[2] to take password from input parameter)

(Export parameter 'export update?' and 'integrated security' are type Bool. All other parameters are Type String.)

See Running Exchange Scripts for more information.