Excel Import Configuration

The Excel import allows you to import data directly from an Excel spreadsheet into the tables in the Micromine Geobank database. When you create a New Excel Import object you configure its properties.

To edit the configuration of an Excel import, do the following:

  1. Right-click on the Excel Imports icon and select Properties.

    2. Excel Import Properties

    Set the General properties of the Import object:

  1. To set up Substitution Parameters, click the Substitution Parameters link.
  2. Use the drop down menu from the Plus button to add a new Substitution Parameter.

The Add Substitution Parameter menu

The Substitution Parameter form opens.

In the Identity tab, enter unique Name, as well as a Description for the new parameter.

The Input tab of the Substitution Parameter form

Use the Prompt tab to configure the user prompt for the substitution parameter.

In the Response tab, configure the validation of the user response to the substitution parameter.

The Response tab of the Substitution Parameter form

If you select the Convert text to uppercase option, the value entered by the user will automatically be converted to uppercase.

The Accept blank values without warning option, when selected, indicates that a substitution parameter prompt which is left blank will not cause a warning.

Selecting the Accept blank values as NULL option will set the empty field value as NULL. This is useful for some fields in tables that can not accept blank values.

If required, use the Default Value field to enter a default value for the substitution parameter prompt.

If you want the default value displayed in the prompt field, select the Use this default as the initial display value check box.

SQL

SQL can be typed straight into this window, but it is preferable to use the Build... button. The main advantage of using the Build... option is that the Execute Script button with the Lightning Strike icon can be used to test the script. The SQL query should return a list of values for the user's selection. If this option is selected, the user can only enter a value that is included in the list of values. If this option is not selected, the user may enter any value.

The SQL Editor

The Limit user's response to values in the pick list check box, when selected, indicates that the user will not be able to enter any value for the parameter that is not included in the pick list created by the SQL query.

Where applicable, select the relevant column from the Lookup Result Column drop down.

When the substitution parameter has been configured,

  1. Click OK to save the Substitution Parameter to the Excel Import.

  2. To configure events, click on the Events link and either select an existing event, or click the Add button to create a new event. Allowable events are messages or the execution of SQL code. The event may be set to trigger either before the form opens or after the form is closed.

  3. Click on the Property Editor: Source to define the type of the Excel file and then navigate to the Excel file that you wish to import.

  4. Click on the Property Editor: Excel Import Mapping to specify the mapping of rows and columns in the Excel spreadsheet to tables and columns in the Geobank database. To add a new mapping, click the Mapping link and then click the green plus sign . Fill in the Data Mapping form.
  5. Click on the Property Editor: Advanced link to set up some advanced options regarding the performance of the import.

  6. Click OK to save the Excel Import properties.