Use ODBC Import to load data from an ODBC-compliant database directly to a worksheet. An import involves establishing a connection to the database, then defining a query to draw specific fields and records from it.
Database connection: Both ODBC import and export use a connection string that defines the database type and location. This string can be created by pointing to the database type and file, or loaded from a previously-saved import or export as detailed under “Establishing a connection”.
Query: Once the database connection is established, an import requires a query to set which data table, field(s) and records to import. The query can be created in the or loaded from a previously-saved import as described under “Specifying the Query”. Phoenix translates the query into Structured Query Language (SQL), which can be edited before executing the import.
The query settings can be saved with a connection string to an import specifications file (*.imp), for later re-use. The query settings can also be saved without the connection string and loaded for use with other databases via different connection strings.
Phoenix also provides a Custom Query Builder for creation of dynamic link library (DLL) files that can access additional data source types, including Watson version 7.x DMLIMS. See “Using the custom query builder”.
Note:If an error is encountered when using ODBC Import, try setting the permissions of the C:\Windows\SysWOW64\comdlg32.ocx file (C:\Windows\SysWOW64\comdlg32.ocx for 32-bit systems) to Read-Execute and Read for Everyone user.”
Once a database connection string is established (see “Establishing a connection”), ODBC import requires a query specifying the data table, field(s) and records to be imported. There are two ways to specify a query:
Building a query
Loading a saved query
Select File > ODBC > Import > Legacy Wnl.
Select the Create a new Import option button in the ODBC Import dialog and click Next.
Create a new or load a saved connection string, as detailed under “Establishing a connection”.
Select the Build a Query option button and click Next.
The Phoenix Query Builder accesses the database, and loads a list of the available tables. If the appropriate ODBC drivers are available, the Schema Filter dialog is displayed. It provides a means to filter the available database tables by schema, a metadata organizational tool. The schema are created by the database administrators.
If the Schema Filter dialog is displayed, select the schema to use in filtering the list of data tables, then click OK.
In the Source Table list, select a table from which to import data.
Click Next to continue with selecting the fields to import or click Done to load all fields in the table.
To select a field(s) to import:
Click a field in the list to select it for import. (Clicking a field a second time, deselects it.)
Click Select all to select all fields. (Click De-Select all to clear all field selections.)
Click Next to continue with filtering the records or click Done to import all records in the selected fields.
To create the record filter:
In the Field Name menu, select a field name used to filter queries.
In the Operator menu, select a comparison or logical operator.
Note:The logical operator like means true if the operand matches a pattern.
Enter a value in the Field Value field or click List Distinct Values to select from all existing values for the selected field.
Click Add to set the filter.
To apply more than one filter, select the And or Or option buttons and return to create another filter. Filters are applied in the order they are created.
Click Done when all filters are entered.
In the ODBC Import: Final Settings dialog, confirm/edit the connection string and query SQL.
Check the Set Column Widths checkbox to adjust the destination worksheet's columns to match the widths of the fields in the database.
Check the Set Column Names checkbox to adjust the destination worksheet to have the same column names as the source fields.
Check the Set Column Formats checkbox to apply the appropriate number formats to columns containing times, currency, and dates.
Check the Check Number of records? checkbox to provide a count of filtered records prior to the import, and the option to return and edit the query if needed.
Click Finish to import the selected data to a Phoenix worksheet.
If Check Number of Records? is checked, a message is displayed that states the number of records returned by the query. Click Yes to continue.
In the Save Import query? dialog, check the Save Password checkbox to save the database username, password, and connection string in the import file.
Saving all three pieces of information in the import file allows users to connect to a database.
Caution:The import file is not encrypted, so the saved password is not secure.
Click Yes to save the import file.
In the Save As dialog, select a directory in the Save in field, type a file name in the File name field, and click Save.
If a user clicks Cancel in the Save As dialog the worksheet is still imported into Phoenix, but the import file is not saved.
The size of the worksheet Phoenix can import depends on the amount of memory in the computer running Phoenix. The list below shows memory requirements and cell limitations. ODBC drivers might impose additional limits.
For 1 gigabyte of RAM, there is a limit of 2 million worksheet cells
For 2 gigabytes of RAM, there is a limit of 4 million worksheet cells
For 3 gigabytes of RAM, there is a limit of 6 million worksheet cells
A saved import specifications file (*.imp) may or may not contain the connection string, depending on what options were specified during saving.
Select File > ODBC > Import > Legacy Wnl.
In the ODBC Import dialog, select the Load an existing Import option button and click Next.
In the Open dialog, select the import (*.imp) file and click Open. The ODBC Import: Final Settings dialog appears with all settings for that import loaded.
Makes changes as needed in the Import: Final Settings dialog or by using the Back button.
When the settings are correct, click Finish. The data from the database are then loaded into a Phoenix worksheet.
Be aware that, once a connection string (new or existing) is established, any saved query can be loaded so long as it uses the tables, fields, and record types in the database to which Phoenix is connected.
Using the custom query builder
The Custom Query Builder provides a means to build custom dynamic link library (DLL) files that access additional data source types, including Watson version 7.x DMLIMS. Further, Phoenix provides templates for creating the DLL files to access custom data sources.
Select File > ODBC > Import > Legacy Wnl.
Select Use Custom Query Builder and click Next.
The Select a Builder dialog is used to set the data resources to access. If the Custom Query Builder has not been used before, this dialog may be empty.
To add resources to the Query Builder, click Add, then double-click the appropriate DLL file such as WatsonLIMS.dll.
To configure the Watson DMLIMS custom resource to make sure that data are imported correctly:
Select that item and click Edit.
In the Configuration dialog, enter the name of the Watson LIMS tables’ owner.
Enter the full directory path for the Watson templates or click Browse to locate the directory on your local machine or on a network server.
Click OK to close the Configuration dialog.
Highlight Watson DMLIMS (or another custom query builder) in the Select a Builder dialog and click OK.
The dialog asks whether to load a query.
To load a saved query, click Yes. In the Open File dialog, select an existing query (file type *.wts for Watson LIMS) and click Open.
If no query has been saved for this builder, click No and proceed with the following steps.
In the log in dialog, enter user name, password, and database alias, and click OK.
Note:The user name, password, and alias for any custom database system (such as the Watson DMLINS) are set by the database administrator or IT personnel. Request this information from that person or department.
In the Study Selection dialog, select a study by clicking on the row and then click Next.
Use the Order Study By field to sort any category, such as study number or ID, study director, etc., in order to find a listing more quickly.
Note:If no projects are listed in this dialog, contact your IT department.
In the Import Data Variables dialog, select the desired variables.
If all available variables are desired, click Select All.
For a subset, drag each variable to the Selected Variables field.
A particular combination of variables can be saved as a template. To create a new template drag the variables for the template to the Selected Variables field and click New. Then enter a name for the template.
The fields will be imported in the order shown in the Selected Variables list.
Click Next to continue with data filtering or click Done to import all data in the selected fields.
Define a filter in the Filter dialog:
Select the field to filter on from the Field box.
Choose an operator.
Either enter a value in the Field Value field or click List Distinct Values to select from all existing value for the selected field.
Click Add to set the filter.
Use the And/Or operators to create compound filters such as Age>12 and Gender=Male.
Click Done to load the data into a Phoenix worksheet. If Check # of rows returned checkbox was selected, Phoenix reports a count of records returned.
