Importing data using the Data Access window

Concept Information

ACCESSDATA command

The Data Access window is a component of Analytics that contains data connectors, which you can use to import data to Analytics from a wide range of sources. The Data Access window also contains features for precisely shaping the set of data you are importing.

For a complete list of the data sources you can connect to using the Data Access window, see Data sources you can access with Analytics.

Note

You can also import data using the Data Definition Wizard. For more information, see Defining and importing data using the Data Definition Wizard.

When connecting to any data source, or importing from any data source, Analytics is strictly read-only. For more information, see Data access by Analytics is read-only.

What are data connectors?

Data connectors are ODBC drivers that provide an interface between Analytics and ODBC-compliant databases and file formats, such as Microsoft SQL Server, Oracle, Salesforce, and Microsoft Excel.

ODBC stands for Open Database Connectivity, a Microsoft standard that use SQL, or Structured Query Language, to allow an application to access data in an external database or file.

Note

The Data Access window is an import-only tool. You can edit the SQL import statement used to access data in an external database or file. Editing the SQL to write to the data source is not supported.

Overview of the Data Access window

The features contained in the Data Access window are explained below.

Number

Feature

Description

1

Connection

Displays information about the currently active data connection.

Includes the name of the connection, and the name of the database, or the location of the file, containing the source data.

 

2

Search tables

A search box for progressively filtering the list of available tables in the source data.

As you enter characters in the search box, the Available Tables list is filtered so that it contains only tables names with a matching string of characters.

3

Available Tables

The tables in the source data that are available for import.

The first 200 tables in the source data are displayed. If additional tables exist, you can click a link to display them in blocks of up to 500 tables at a time.

4

Staging Area

The area in the Data Access window that contains the table or tables you have selected for import.

The Staging Area is also the location where you perform joins between tables, and select which fields in a table are imported.

5

Filter panel

A panel for building simple or compound filters that precisely specify which records from a data set are imported.

6

Import Preview

A preview of the data exactly as it will be imported into Analytics.

As you work with the data by joining tables, omitting fields, and creating filters, you can refresh the preview to see the effect of your changes.

The Estimate Size option displays an estimate of the number of records in the import, and the size of the Analytics data file (.fil) that will be created.

7

Field configuration

Three options that allow you to:

  • adjust the maximum field lengths for imported character or memo fields
  • import all fields as a Character data type

8

SQL Mode

A text editor that allows you to directly edit the SQL import statement.

Users with knowledge of SQL can control aspects of the data import not available through the user interface.

Managing data connections

In the Data Access window, you can rename, copy, or delete a connection created using an Analytics data connector.

You can also clear the list of cached table names stored by a connector.

Rename, copy, or delete a connection

Note

You cannot use the Data Access window to rename, copy, or delete a DSN connection created using a Windows ODBC driver. These connections can be maintained in Windows.

  1. From the Analytics main menu, select Import > Database and application.
  2. In the Existing Connections tab, under ACL Connectors or ACL DSN Connectors (Bundled), hover over the connection that you want to maintain, and click the ellipsis icon .
  3. Select one of the following as available:
    • Create a copy
    • Rename connection
    • Delete connection
  4. Follow the on-screen prompts to complete the task.

Clear cached table names

When you successfully connect to a data source using the Data Access window, the connector that you use caches the names of the first 200 tables in the data source. This list of table names speeds up subsequent connections to the same data source.

If a schema change in the data source adds, deletes, or renames any tables, you need to manually clear the table name cache. A mismatch between the table name cache and the data source schema causes a connection error.

After clearing the cache and successfully connecting to the data source, the cache is refreshed with the updated list of table names.

  1. From the Analytics main menu, select Import > Database and application.
  2. In the Existing Connections tab, under ACL Connectors, ACL DSN Connectors (Bundled), or Windows DSN Connectors, hover over the connection that you want to maintain, and click the ellipsis icon .
  3. Select Clear cache.

    The table name cache is cleared.

Data Access log files

Two log files record activities in the Data Access window, and can be used for troubleshooting if a data connection fails:

  • ServerDataAccess.log records all Data Access window activities and errors prior to importing the data

    Location: C:\Users\<user account>\AppData\Local\ACL\ACL for Windows\Data Access\ServerDataAccess.log

    Note

    The "Server" in ServerDataAccess.log refers to the data access component of Analytics running locally on the computer where Analytics is installed.

  • DataAccess.log records information about the import operation and the Analytics project that you are importing data to

    Location: ..\<Analytics project folder>\DataAccess.log