IMPORT ODBC command

Creates an Analytics table by defining and importing data from an ODBC data source.

ODBC stands for Open Database Connectivity, a standard method for accessing databases.

Syntax

IMPORT ODBC SOURCE source_name TABLE table_name <QUALIFIER data_qualifier> <OWNER user_name> <USERID user_id> <PASSWORD num> <WHERE where_clause> <TO table_name> <WIDTH max_field_length> <MAXIMUM max_field_length> <FIELDS field <,...n>>

Parameters

Name	Description
SOURCE source_name	The data source name (DSN) of the ODBC data source to connect to. The DSN must already exist and be correctly configured. Note You are limited to data sources that use the Windows ODBC drivers that are installed on your computer. The Analytics native data connectors that can be used with the ACCESSDATA command may not be available with IMPORT ODBC.
TABLE table_name	The table name in the ODBC data source to import data from. table_name usually refers to a database table in the source data, but it can refer to anything Analytics imports as a table. For example, if you use the Microsoft Text Driver, table_name refers to the text file you want to import data from.
QUALIFIER data_qualifier optional	The character to use as the text qualifier to wrap and identify field values. You must specify the character as a quoted string. Use single quotation marks to specify the double quotation character: '"'.
OWNER user_name optional	The name of the database user account that owns the table you are connecting to.
USERID user_id optional	The username to access the data source.
PASSWORD num optional	The password definition to use. You do not use PASSWORD num to prompt for, or specify, an actual password. The password definition refers to a password previously supplied or set using the PASSWORD command, the SET PASSWORD command, or the PASSWORD analytic tag. num is the number of the password definition. For example, if two passwords have been previously supplied or set in a script, or when scheduling an analytic, PASSWORD 2 specifies that password #2 is used. For more information about supplying or setting passwords, see: PASSWORD command SET command PASSWORD tag
WHERE where_clause optional	An SQL WHERE clause that limits the records returned based on a criteria you specify. Must be a valid SQL statement and must be entered as a quoted string: WHERE "SALARY > 50000".
TO table_name optional	The name of the Analytics data file to create. Specify table_name as a quoted string with a .FIL file extension. For example, TO "Invoices.FIL". By default, the data file (.FIL) is saved to the folder containing the Analytics project. Use either an absolute or relative file path to save the data file to a different, existing folder: TO "C:\data\Invoices.FIL" TO "data\Invoices.FIL".
WIDTH max_field_length optional	The maximum length in characters for any field in the Analytics table that originates as character data in the source from which you are importing. You can enter any value between 1 and 254. The default value is 50. Data that exceeds the maximum field length is truncated when imported to Analytics.
MAXIMUM max_field_length optional	The maximum length in characters for text, note, or memo fields you are importing. You can enter any value between 1 and 1100. The default value is 100. Data that exceeds the maximum field length is truncated when imported to Analytics.
FIELDS field <,...n> optional	Individual fields in the source data to import. Specify the name. If you specify multiple fields, each field must be separated by a comma. If you omit FIELDS, all fields are imported. Enclosing the field names in quotation marks makes them case-sensitive. If you use quotation marks, the case of field names must exactly match between FIELDS and the ODBC data source. If you use quotation marks and the case of the field names does not match, the fields are not imported. Note FIELDS must be positioned last among the IMPORT ODBC parameters. If FIELDS is not positioned last, the command fails.

Examples

Importing data from SQL Server

You import data from a SQL Server database to an Analytics table named Trans_Dec11:

IMPORT ODBC SOURCE "SQLServerAudit" TABLE "Transactions" OWNER "audit" TO "C:\ACL DATA\Trans_Dec11.FIL" WIDTH 100 MAXIMUM 200 FIELDS "CARDNUM","CREDLIM","CUSTNO","PASTDUEAMT"

Remarks

Older method of connecting to ODBC data sources

The IMPORT ODBC command is the older method of connecting to ODBC-compliant data sources from Analytics. The new method of connecting to ODBC data sources uses the Data Access window and the ACCESSDATA command.

You can continue to use IMPORT ODBC in Analytics. However, this method of connecting is now available only in scripts and from the Analytics command line. You can no longer access this connection method in the Data Definition Wizard.

Suppress the time portion of datetime values

When using the IMPORT ODBC command to define an Analytics table you can suppress the time portion of datetime values by prefacing the command with the SET SUPPRESSTIME ON command.

This capability allows retrofitting Analytics scripts written prior to version 10.0 of Analytics, when the time portion of datetime values was automatically truncated. If SET SUPPRESSTIME ON is not added to these scripts, they do not run in the datetime-enabled version of Analytics.

For more information, see the "SET SUPPRESSTIME" section in SET command.