Exchange Scripts - Open Data Import Centre

This topic contains an example script for importing CSV data to an InfoAsset Manager network from an external database using InfoAsset Manager Exchange. The functionality corresponds to that provided by the Open Data Import 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 update InfoAsset Manager Data from a Comma Separated Value File
#
begin
        puts 'Start InfoAsset Manager Import from CSV File' 
	       # open an InfoAsset Manager database
	       #
	       db = WSApplication.open('in.icmm',false)
	       # get the network from the object type and id
	       #
	       nw = db.model_object_from_type_and_id('Collection Network',23)
	       # reserve the network to prevent other users from editing it
	       #
	       nw.reserve
	       #set options
	       #
	       options=Hash.new
	       options['Allow Multiple Asset IDs'] = false
	       options['Blob Merge'] = false 
	       options['Delete Missing Objects'] = false
	       options['Update Based On Asset ID'] = false 
	       options['Update Only'] = false
	       options['Update Links From Points'] = false
	       options['Use Network Naming Conventions'] = false
	       options['Error File'] = 'csvimp.txt' 
	       options['Default Value Flag'] = 'DV'
	       options['Set Value Flag'] = 'CV'
	       options['Duplication Behaviour'] = 'Merge'
	       options['Units Behaviour'] = 'Native'
	       # import network data from csv format
	       #
	       nw.odic_import_ex( 
	                 'csv',                   # import data format => Comma Separated Value 
	                 'in_imp.cfg',            # field mapping config file 
	                 options,                 # specified options override the default options 
	                 # table group
	                 'node',                  # import to table name 
	                 'node.csv',              # import from file name
                 	# table group
                 	'pipe',                  # import to table name
                 	'pipe.csv')              # import from file name
        # commit changes and unreserve the network
	       #
	       nw.commit('Imported data via ruby script')
	       puts 'Done'
# handle exceptions
#
rescue Exception => exception 
        puts "#{exception.to_s} (#{exception.backtrace})"
ensure
        # unreserve the network on error, only if it has been reserved 
        nw.unreserve if nw
end

Additional information about each section of the example script (shown below) can be found in the following sections.

Import options

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

The import options are common to all external data source types.
It is only necessary to define an option if you do not want to use the default for the option.

Note: By default the scripts use the Open Data Import Centre Overwrite updating option when updating existing objects, except for arrays, where the Merge update functionality is used. See the Open Data Import Centre dialog topic for a full description of these options.

Import Option Key Name	Type	Default	Description
['Import Images']	Boolean	false	Allows images to be imported to image fields. If true, fields mapped to image fields will be treated as filenames and the images imported. If the "Image Folder" option is set, the importer assumes that the import fields contain the filename of the image file and that the image file is located in the specified folder. If the "Image Folder" option is not set, the importer assumes that the import fields contain the full path to the image file.
['Allow Multiple Asset IDs']	Boolean	false	This option allows updating of multiple assets, when used in conjunction with the 'Update based on Asset ID' option. If true, all objects with the same Asset ID as an imported object will be updated with data from the imported object. (Geometry fields and US / DS Node ID fields will not be updated.) If false, objects with non-unique Asset IDs will not be updated. Note: Applies only to Pipe tables. If used with any other type of table, the option will be ignored and a message will be generated in the error log.Uniqueness of Asset ID fields in imported data is not checked.
['Blob Merge']	Boolean	false	Allows merging of data from multiple sources to arrays with a single entry. Unlikely to be of use for InfoAsset Manager - should be set to false.
['Delete Missing Objects']	Boolean	false	Corresponds to 'Delete missing objects' parameter in Open Data Import Centre in main application.
['Update Based On Asset ID']	Boolean	false	Corresponds to 'Update based on Asset ID' parameter in Open Data Import Centre in main application.
['Update Only']	Boolean	false	Corresponds to 'Only update existing objects' parameter in Open Data Import Centre in main application.
['Update Links From Points']	Boolean	false	Allow point data to be imported into an InfoAsset Manager links table. See Open Data Import Centre - Advanced Features for further information.
['Use Network Naming Conventions']	Boolean	false	Corresponds to 'Use auto-name option for generated nodes' parameter in Open Data Import Centre in main application.
['Error File']	String	nil	Path of Error log file. Parameter can be null or ' '
['Callback Class']	Ruby Class	nil	Ruby class used for callbacks Parameter can be nil or a Ruby class
['Image Folder']	String	nil	Path of folder in which images to be imported are located. If this option is not set, the importer assumes that fields mapped to image fields contain the full path to the image file. Parameter can be null or ' '
['Default Value Flag']	String	nil	Corresponds to 'Flag when default value is used' parameter in Open Data Import Centre in main application.
['Set Value Flag']	String	nil	Corresponds to 'Otherwise, set flag on imported fields to:' parameter in Open Data Import Centre in main application.
['Duplication Behaviour']	String	Merge	Permitted values are: 'Ignore', 'Merge' and 'Overwrite'. Corresponds to the Ignore, Merge and Overwrite Updating and Delete options in Open Data Import Centre in main application.
['Units Behaviour']	String	Native	Permitted values are: 'Custom', 'Native' and 'User'. Corresponds to 'Units Behaviour' parameter in Open Data Import Centre in main application.
['Group Type']	String	nil	Corresponds to 'Asset name group' parameter in Open Data Import Centre in main application. Required if ['Group Name'] option is specified.
['Group Name']	String	nil	Corresponds to 'Asset name group' parameter in Open Data Import Centre in main application. Required if ['Group Type'] option is specified.

Import to network from external source

Import network from external data source:

nw.odic_import_ex(import_parameters)

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

The first three import parameters are common to all external data source types.

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

Common import parameters

Parameter Type	Example	Description
String	CSV	External data format . Possible values: "CSV", "TSV", "XML", "MDB", "SHP", "GDB", "ORACLE", "SQLSERVER"
String	in_imp.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 Import Centre in the main InfoAsset Manager application.
String	options	The hash in which the import options are defined.

Parameter Type

Example

Description

String

CSV

External data format .

Possible values: "CSV", "TSV", "XML", "MDB", "SHP", "GDB", "ORACLE", "SQLSERVER"

String

in_imp.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 Import Centre in the main InfoAsset Manager application.

String

options

The hash in which the import options are defined.

Import to network parameters

The import parameters used to define the destination from which the InfoAsset Manager Network data is to be imported differ according to the type of data format from which the network data is being imported. The import to network parameters are all of type String. 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 Import Centre in the main program). The name in the configuration file is the same as the table name that appears in the Open Data Import Centre dialog with spaces and separators removed, e.g. CCTVSurvey, CCTVSurveyDetails etc.

CSV / SHP / TAB / TSV

`# table group`
`'node',`	`# import to InfoAsset Manager table name`
`'node.csv'`	`# import from file name`

MDB

`# table group`
`'node',`	`# import to InfoAsset Manager table name`
`'features.mdb',`	`# import from feature class i.e. database`
`'in_node'`	`# import from table name`

XML

`# table group`
`'pipe',`	`# import to InfoAsset Manager table name`
`'pipe_object',`	`# import from feature class`
`'network.xml'`	`# import from file name`

GDB

Requires installed and licenced ArcGIS:

Personal geodatabase:

# table group
'node',	`# import to InfoAsset Manager table name`
'nodes',	`# import from feature class`
'atlanta.mdb'	`# import from personal geodatabase name`

File geodatabase

`# table group`
`'node',`	`# import to InfoAsset Manager table name`
`'nodes',`	`# import from feature class`
`'atlanta.gdb'`	`# import from file geodatabase name`

Shapefiles:

`# table group`
`'node',`	`# import to InfoAsset Manager table name`
`'nodes',`	`# import from feature class (always use the unqualified name)`
`'connect.sde'`	`# import from shapefile folder name`

SDE database:

`# table group`
`'node',`	`# import to InfoAsset Manager table name`
`'sde.nodes',`	`# import from feature class (always use the unqualified name, i.e. specify the feature class names as they appear in ArcGIS/ArcCatalog)`
`'connect.sde'`	`# import from ArcSDE using connection file name`

ORACLE

Requires installed and licenced Oracle Client

`# table group`
`'node',`	`# import to InfoAsset Manager table name`
`'in_node',`	`# import from table name`
`'//power:/orcl',`	`# import from oracle service name`
`'gmsora',`	`# import from table owner`
`'myloginname',`	`# oracle user login name (or ARGV[1] to take user name from input parameter)`
`'myloginpassword'`	`# oracle user login password (or ARGV[2] to take password from input parameter)`

SQL SERVER

Requires installed and licenced SQL Server Client :

`# table group`
`'node',`	`# import to InfoAsset Manager table name`
`'in_node',`	`# import from table name`
`'server'`	`# import from server`
`'instance',`	`# import from SQL server instance`
`'database',`	`# import from SQL server database`
`true,`	`# integrated security?`
`'myloginname',`	`# SQL user login name (or ARGV[1] to take user name from input parameter)`
`'myloginpassword',`	`# SQL user login 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.

Exchange Scripts - Open Data Import Centre

Import options

Import to network from external source

Common import parameters

Import to network parameters

CSV / SHP / TAB / TSV

MDB

XML

GDB

Personal geodatabase:

File geodatabase

Shapefiles:

SDE database:

ORACLE

SQL SERVER

Related Information