If you create a link to an external file then, by default, STEM will decide how to read data from this file on the basis of its file extension. Both
.dtl
and .dtm
extensions identify STEM models, whilst the
.xls
extension identifies an Excel workbook – see 6.3 Linking input data from Microsoft Excel spreadsheets.
Automatic inference of ODBC driver from file extension
A file with any other extension will be treated as a database if an ODBC driver for that file type is installed on the machine. (You can see which, if any, ODBC drivers are installed on your computer by selecting the ODBC manager in Windows Control Panel.)
For example, if you have installed Microsoft Access, you should find a driver called ‘Access 2007/2010 Database file (*.ACCDB or *.MDB) ’ in the list of installed drivers, and this will allow you to import data from an Access database, simply by selecting or entering the corresponding filename. STEM will automatically locate the Access driver and use it to connect to the Access database.
Figure 1: Pasting a filename from the External Links dialog
Explicit ODBC links
However, it is not always possible to infer an appropriate ODBC driver from a file extension, because:
- client-server databases, such as Microsoft SQL Server, typically hide the names of the actual database files and communicate via machine names and internal identifiers
- you may wish to import data from an ODBC data source, which is just identified by a name defined in the ODBC manager in Windows Control Panel.
In such cases, you have to select an explicit driver from the list of ODBC Drivers within the Define External Link dialog in the STEM Editor.
These explicit drivers are described in detail in 6.6 Linking input data via ODBC; but suppose, for example, you want to import a Capital Cost from a Microsoft SQL Server database called ‘MyDatabase’ located on a machine called ‘MyServer’, using a SQL query,
SELECT Value from CostTable WHERE Name='Transceiver':
Figure 2: Creating a link to a client–server database
- Select Costs from the Resource icon menu. The Costs dialog appears.
- Select the Capital Cost field with the mouse or cursor keys.
- Select Links… from the File menu on the main Editor window. The External Links dialog is displayed.
- Select the Add… button to display the Define External Link dialog.
- Select SQL
from the ODBC Driver list. The File Name box is re-labelled with the heading ‘Parameters’ and the Browse button is disabled, indicating that this driver does not accept a filename.
- Enter MyServer:MyDatabase
in the Parameters box, and press <Enter> or click OK.
- The database is added to the list of Links in the External Links dialog, and appears as
SQL:MyServer:MyDatabase.
- Select the Paste button to enter the <filename>! part of an external reference (in this case, the driver identifier and parameters) into the formula bar for Capital Cost:
'SQL:MyServer:MyDatabase'!.
- Now type the <reference> part (in this case, a SQL query),
"SELECT Value from CostTable WHERE Name='Transceiver' ", including the quotes, to complete the formula.
Note: The server part of the external reference, i.e.,
'SQL:MyServer:MyDatabase', can be entered directly in the formula bar if you prefer.
The SQL query itself is interpreted by the ODBC driver, outside STEM. The syntax is described in outline in 6.6 Linking input data via ODBC.