---
title: "Import and Export External Databases"
canonical: "https://help.innovyze.com/space/xps/19663082/Import%20and%20Export%20External%20Databases"
format: markdown
---
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.

![image](media://c478de01-7b7a-4fd8-bac8-f4ff9ef36807)

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.

This page contains the following topics:

> Macro (toc)

## > Macro (anchor)

External Database Wizard

1. Go to **File > Import/Export Data > Import/Export External Database**.
2. In the **External Data Import/Export** dialog, select **New**.> Macro (inline-media-image)
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)**.> Macro (inline-media-image)
  - 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.> Macro (inline-media-image)
  - 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](#ImportandExportExternalDatabases-DatabaseConnectionDataLinkProperties).
4. Once a database has been selected, select the table that will be linked to the XP database.> Macro (inline-media-image)
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.> Macro (inline-media-image)
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**
  > Macro (inline-media-image)
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.
8. Enter the mapping of the external database name to the XP Variable name. Any or all of the fields may be mapped.> Macro (inline-media-image)
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 **I****mport** 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.

## > Macro (anchor)

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 **N****ext**.> Macro (inline-media-image)
2. In the **Connection** tab, specify the connection to the ODBC database. For PostgreSQL, refer to [Connecting to the PostgreSQL via the ODBC Drivers](#ImportandExportExternalDatabases-PostgreSQL).> Macro (inline-media-image)
3. Click **OK**, which will take you back to the** **[External Database Wizard](#ImportandExportExternalDatabases-DatabaseWizardExternalDatabaseWizard).

### > Macro (anchor)

Connecting to the PostgreSQL via the ODBC Drivers

1. Connect to a PostgreSQL Data Source. For more information, refer to the Microsoft [article](https://docs.microsoft.com/en-us/sql/integration-services/import-export-data/connect-to-a-postgresql-data-source-sql-server-import-and-export-wizard?view=sql-server-2017).
2. Select **Microsoft OLE DB Provider for ODBC Driver **from the **OLE DB Provider** list. Click **N****ext**.> Macro (inline-media-image)
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.> Macro (inline-media-image)
5. Click **OK, **which will take you back to the [External Database Wizard](#ImportandExportExternalDatabases-DatabaseWizardExternalDatabaseWizard).

## > Macro (anchor)

Configure Database Connection

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

![image](media://085d2d46-a104-4b27-afe8-8c559b1eed3b)

## > Macro (anchor)

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.

![image](media://b50e14a5-62ef-4d9f-ab3b-c09208aa3064)

## > Macro (anchor)

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.

![image](media://64625849-1762-41cd-816d-7cfea7d58a80)

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 type** | **Mandatory data** |
| --- | --- |
| Node | Node name, X pos, Y pos |
| Link | Link, US Node, DS Node |
| Global Database | GLDB Name, GLDB Type |
| Job Control | Job 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 Link | Channel/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.