To import ODBC data sources, select the ODBC radio button on the Select Local Data Source panel and click [Next].
Note: When ODBC is selected, Analyzer displays check boxes to enable you to display a list of registered data sources (the default) and/or locally installed ODBC drivers.
When importing an ODBC table into Analyzer, you need to select the ODBC-compliant data source, tables, columns and specify the Where and Order clauses. Finally, when the conversion is complete, you can view the data and make changes to the table layout. You can also specify a custom SQL Select statement for importing tables.
Note: For specific information on preparing ODBC-compliant spreadsheet data for import via ODBC, see Importing Non-Excel Spreadsheet Data.
The Select Data Source panel lists available 32-bit ODBC data sources in a pull-down menu. Select the ODBC data source and click [Next].
The list of data sources can be restricted or expanded by selecting or deselecting the check boxes for Data Sources, Drivers and File DSN's. Schema's can also be selected.
Check the "Save As Default" check box to save the selected data source as the default for future usage.
Check the "Convert Imported Unicode Data to ASCII" check box if you want any SQL-based Unicode data that is read - including for Excel (OpenXML) imports - to be converted to ASCII. This option should only be used if you are confident that the source data only contains North American English characters. For more information see Convert Imported Unicode Data to ASCII or Set SQLASCII.
Note: The ASCII keyword is automatically inserted into the Import ODBC command syntax generated by the Data Definition Wizard if SET SQLASCII is turned on, if the "Convert Imported Unicode Data to ASCII" table preference is selected, or if the "Convert Imported Unicode Data to ASCII" check box is selected in the Data Definition Wizard via either the Select Data Source or Subsystem panel or the File Format panel.
If your company uses password protection, enter your user name and password. If you need help with your user id and password, contact your database or systems administrator. If SAVE is selected the user name and password are encrypted and stored in the users local registry. If you need help with your user name and password, contact your database or systems administrator.
After selecting the data source, the Select Tables panel is displayed. By default you can select up to five tables (or worksheets/named ranges for Excel data sources). You can also specify the schema.
Note: Depending on the chosen data source, the Select Tables dialog may display check boxes to allow selection of Tables, Views or Aliases.
To select columns and to edit the Where and Order clauses, de-select Define Multiple Tables, select Edit Query and click [Next]. Otherwise, clicking [Next] will advance directly to the Final panel.
To create a custom SQL Query, check the Create SELECT check box and click [Next]. Enter/paste a valid SQL Select statement in the SQL SELECT dialog that appears and then click [OK] to advance to the Final panel.
If more than one table is selected, an Identify Relationship dialog will appear asking you to identify the common key(s) in each of the tables that they should be joined upon before you can proceed.
Click [OK] to return to the Select Tables dialog which now displays the Join criteria.
Note: To increase the number of tables that can be joined at one time, edit the Table Option Maximum Relational Joined Tables.
For data sources that support schema, the Available Schema pull-down menu allows you to choose and join tables from different schemas.
Note: As each table is selected, the Selected Tables list displays the selected table names and their internal Analyzer alias (For example: “dbo.customer A”, “dbo.employee B”). When multiple tables are selected and joined, the Join Criteria list will display the internal Analyzer alias followed by the field name (For example: “A.CustomerID = B.EmployeeID”).
To define multiple worksheets independently at one time, select Define Multiple Tables.
After selecting the desired worksheets or named ranges, clicking [Next] will advance you directly to the Final panel.
Each table layout will be named based on the table or worksheet name and the selected tables or worksheets can either be flattened or read directly.
By default, the ODBC Import Wizard selects all available columns. If you checked the “Edit Query” check box on the Select Table panel, you can choose only desired columns in the order you want them in the Select Columns panel. Click the left arrow button, the right arrow button, [Clear All] or [Add All] to change the selection of individual columns you want to import.
For individual character and numeric fields in an ODBC data source, the Select Columns dialog provides the dynamic ability to override the maximum string length or maximum decimals. This is particularly useful for large memo fields or to ensure consistency with decimal precision.
Use the Table Data pull-down menu to select columns from each table if more than one table was selected. Click [Next] to proceed.
If you checked the “Edit Query” check box on the Select Table panel, you can specify filtering and ordering information in the Where and Order panel. Specify a valid SQL where clause to filter your data or a valid SQL order clause to order your selected data.
There are several options in this dialog:
| • | In the Where Clause text box, enter a Structured Query Language (SQL) “Where” statement if you want to limit the records imported. For example: Amount>0. This SQL Where Clause only applies to the selected table. |
Note: This is not an Analyzer “If” test. The SQL “Where” statement is processed by the ODBC driver and must be valid for this usage.
| • | In the Order Clause text box, enter a Structured Query Language (SQL) “Order” statement if you want to order the records imported. This SQL Order Clause only applies to the selected table. |
| • | To check the validity of the entered Where and Order clause, click the “Validate” button. Errors are reported in a text box. |
To edit the underlying SQL Select clause for this table, click the “Edit SQL Select” check box and then click the Validate button. When the “Edit SQL Select” check box is selected, clicking [Next] allows you to edit the underlying SQL Select statement selected table(s).
Note: Edit the SQL Select statement with extreme caution!
It is useful to ensure that all required fields were selected from all tables prior to editing the SQL Select statement. If not, simply click [Back] to get to the Select Columns dialog and then select all necessary fields.
In the Final panel, the Wizard displays information for the selected table and columns. Enter a name for the table layout and for relational data sources (including MS Excel and Access) determine if you want to directly read the source data or create a flattened data file of the selected data. Uncheck the “Flatten Table Data” check box to directly read the source data instead of flattening it.
If you choose to flatten your selected ODBC data, you are prompted to specify a location and name to save the flattened file.
Click [Save] and your selected data is displayed in the View.