Using the Import and Export External Databases functionality, data can be obtained from many different ODBC compliant databases.

You must install the MDAC drivers from your installation CD prior to using this function. Only applications with the GIS module will be able to access this menu.

Any number of databases can be linked to the XP model. When you select the new database option an Import wizard is invoked. Once databases have been defined they can be edited, re-mapped, deleted or moved up or down in the import hierarchy.

External Database Wizard

  1. Go to File > Import/Export Data > Import/Export External Database.
  2. In the External Data Import/Export dialog, select New.


  3. In the External Database Wizard - Step 1 dialog, select the database to connect to either by clicking Select a file or Choose a database connection (advanced).


    • If you choose Select a file, locate the file of the known database type and then click Open. Applicable databases include Microsoft Access Database (*.mdb), Microsoft Excel Worksheet (*.xls, xlsx), dBase file (*.dbf), and other ODBC databases.


    • If your database is not recognized, you will need to define your own database connection. Select Choose a database connection (advanced). For more information, refer to Data Link Properties.

      You may need your database administrator to help you on this process. 
  4. Once a database has been selected, select the table that will be linked to the XP database.

    Select Preview Table Data to view the data in the currently selected database table.
  5. In External Database Wizard - Step 3, define the database/table combination as:
    1. Import data only - Read-only access.
    2. Export data only - The results from XP are saved in the database for use in reviewing results from an external program.
    3. Import and/or export data - The table can be updated with changes made in the application.

  6. Select your import/export options for On Import and/or On Export. The available options are:
    • Update existing objects only
    • Create new and update existing


  7. Define the mandatory data for the object:
    1. In the Object Type field, select the type of object from the drop-down menu.
    2. In the Mandatory Data section, enter the value for each of the mandatory fields.

      The fields in the Mandatory Data section will change depending on the Object Type selected.

  8. Enter the mapping of the external database name to the XP Variable name. Any or all of the fields may be mapped.


  9. You have now finished the procedure for one table. To connect to multiple tables, select the icons in the right-hand side of the dialog.
    • Use New to add tables to the Database Connection list.
    • Click Delete to remove a table.
    • Click Close to exit Database Connection Wizard.
    • Select a table in the Database Connections list and click the Mapping button to check or modify the field mappings.
    • Use the Configure button to modify your database connection settings.
    • Highlight a table and click Import or Export. The data will displayed.

  10. Click Finish.  If the data was successfully mapped and no errors have occurred, the table will be added in the Database Connections list.

Data Link Properties

The Data Link Properties dialog contains the relevant ODBC database driver library for the database that you wish to import. You should select from the existing driver list loaded onto your machine or create a new definition file for your ODBC database.

  1. Select a OLE DB Provider from the list and then click Next.


  2. In the Connection tab, specify the connection to the ODBC database. For PostgreSQL, refer to Connecting to the PostgreSQL via the ODBC Drivers.


  3. Click OK, which will take you back to the External Database Wizard.

Connecting to the PostgreSQL via the ODBC Drivers

  1. Connect to a PostgreSQL Data Source. For more information, refer to the Microsoft article.
  2. Select Microsoft OLE DB Provider for ODBC Driver from the OLE DB Provider list. Click Next.


  3. In the Connection tab, select the DSN created via ODBC Data Sources in the Use data source name drop-down list. 
  4. Specify the user name and password if authentication is needed and specify the initial catalog (database) to use. Click Test Connection to test the connection to the database.


  5. Click OK, which will take you back to the External Database Wizard.

Configure Database Connection

Use this dialog to modify your database connection settings. Click OK to return to the Database Connection Wizard.

External Database Query Results

Once you have selected the table for import, you will be presented with the Import/Export Data table. Use the tools to navigate through the data. Click OK to complete the import or export step.

Mappings

The variables from each database need to be mapped prior to importing the raw data from the source database. Listed in this dialog are the full set of source database variables, which can be used to match those in the xp database.

You need to select variables for the selected object type. Data from each object type should be mapped separately. Select the variable that you wish to import and then click the Set button to choose a variable for the software database. This procedure should be repeated for all elements in the source database that you wish to import.

For each object selected in the Object Type field, there are separate Mandatory Data requirements:

Object typeMandatory data
Node Node name, X pos, Y pos
Link Link, US Node, DS Node
Global Database GLDB Name, GLDB Type
Job ControlJob Control
Pump Pump Name, US Node, DS Node
Weir Weir Name, US Node, DS Node
Orifice Orifice Name, US Node, DS Node
Special Special Name, US Node, DS Node
Multi LinkChannel/Conduit, US Node, DS Node

In the Options portion of the Mappings dialog:

  • In the Import/Export field, select whether to Import only, Export only, or Import and Export from the drop-down list.
  • In the Object Creation on Import/Export fields, select whether to Update Existing Objects only or Create New or Update Existing from the drop-down list.

Click OK to return to the Database Connection Wizard.