Downloading Excel import/export templates

About Excel Templates

One of the main ways that data is exported from and imported to GRANTA MI is via specially-configured Microsoft® Excel files. For example, the MI:Toolbox Remote Import tool provides a simple web browser interface through which any authorized user can upload data stored in Excel worksheets:

 

Excel Templates provide a convenient method of data entry, enabling people with no knowledge of GRANTA MI or the structure of the information that needs to be supplied to enter or modify data values into a pre-formatted Excel worksheet. The template defines the worksheets where data is entered, and additional worksheets that specify the data structure for the attributes and parameters to be imported, and various import options.

The process of creating Excel Templates is simplified by the use of Excel Template Definitions:

To download a new Excel template

  1. In the Contents tree, locate the table or folder where the imported data will be placed and click   Download Excel Template on the item menu button .

    The exact location and structure of the data within a table when it is imported will depend on the placement options specified in the Excel template definition you use (step 3 below).

  2. On the Download Excel Template page, locate the Excel Template Definition you want to use.

    If no Excel Template Definitions have been created for the selected table, you can select the Default Template definition here to create a new Excel Template that includes all attributes available in the current layout.

  3. To change the file format, click Options and choose the format required. The default file format for new Excel Templates is .xlsx for Excel 2007 and later workbooks.
  4. Click Download to generate the new Excel Template.
  5. Save the new Excel file.

Note: Equations and Logic (EEL) attributes will not be included in templates generated in this way. If you need a template that can export/import EEL attributes, it will be necessary to edit the downloaded template in Microsoft Excel to add the required expression, parameter, and curve details. This is a specialist task, requiring familiarity both with Microsoft Excel and with the structure of the database into which the data is being imported. See the PDF document GRANTA MI Excel Importer Reference Guide for further information (available from Help > Reference Documentation).

What's in an Excel template?

The Excel template created using the Download Excel Template command is a Microsoft Excel workbook that includes a number of different worksheets. As well as one or more worksheets where data can be entered, it will include additional (hidden) worksheets that provide the information needed to export and import different types of data from/to the selected table or folder.  The specific worksheets in a downloaded template will depend on the data in the selected table or folder (for example, additional worksheets will be created for tabular data). Typically, the following worksheets will be included:

Worksheet Settings
Data This is the worksheet in which the data to be imported is entered. Typically,  it will be configured to restrict where and what type of data can be entered. This, along with the style and layout of the worksheet (colors, labels, and notes or instructions about entering data), is defined in the Excel Template Definition used to create the template.
Import Options Defines data import settings including:
  • Autoplacement options specifying the database, table, subset, and record into which the data will be imported.
  • Auto-linking options specifying how incoming data can be automatically linked to existing records in the database based on their record names, GUIDs, or particular attribute values.
  • Tree hierarchy options defining exactly where in the record tree new records will be placed.
  • Access control options specifying read/write permissions for imported records (for Granta implementations using permission-based access control).
  • Conflict resolution options specifying what happens when a record name conflict occurs on import.
Attribute Lookup Defines attributes to be imported: their name, data type, and units, and the location of the corresponding data worksheet. Also includes options for different types of attribute, such as interpolation type (for functional attributes) and whether or not data is imported with trailing zeroes (for point data). Each row represents an attribute.
Parameter Lookup Maps attribute parameter ranges for functional, tabular, and multi-value data to specific parameters or tabular columns in the database.
Export Lookup Lists the GRANTA MI attributes to be exported, their data type and units, and the location of the data within the Excel workbook.
Template Information Specifies information about the template, including the name of the source database and table, the GRANTA MI software version with which the template was created, the name of the person who created (downloaded) the template and when the template was created, as well as a number of import/export options.
Export Information When the template is used to export data, this worksheet records the date of the export and the name of the user who performed the export.

For detailed information about the format of Excel templates for exporting data from GRANTA MI, refer to the GRANTA MI:Toolbox Excel Importer Reference Guide. This document can be accessed via Help > Reference Documentation.