How to Import Data from Microsoft Access into STATISTICA

You can import data from any external database into STATISTICA as long as you have the necessary OLEDB/ODBC providers on your machine.  In this example, we’ll connect to Microsoft Access, one of the most commonly used relational databases. The OLEDB driver for Microsoft Access is installed on your machine along with the Microsoft Office installation.

Before you start out, you want to ensure that bit-width between STATISTICA and your Microsoft Office match. That is, if you have 32-bit Microsoft Office, you need to use 32-bit STATISTICA, or if you have 64-bit Microsoft office, you need to use 64-bit STATISTICA.
To import data from Microsoft Access, click the STATISTICA Start button in the top-left corner of the ribbon bar, and select Create Query from the Open External Data submenu.
HowTo Menu

The Database Connection dialog box will be displayed.

Database connection image

Click the New button to display the Data Link Properties dialog box, which lists all the OLEDB providers installed on your machine.

Datalink properties image

If you are using 32-bit STATISTICA and Access, you can use either one of two providers: Microsoft  Jet 4.0 OLE DB Provider or Microsoft Office 12.0 Access Database Engine OLE DB Provider.

If you are using 64-bit STATISTICA and Access, you can use the Microsoft Office 12.0 Access Database Engine OLE DB Provider.

If you don’t see these drivers, you can download the driver from this URL:

After you have selected the provider, click the Next button, or select the Connection tab
Click the browse button adjacent to the Select or enter a database name text box and browse to and select an mdb/accdb Access file. Alternatively, you can type in the path and the database filename in the text box.

connections tab image

If your database is password protected, enter the username and password. If there is no password, select the Blank password check box as shown in the image above.

Click the Test Connection button to ensure the path and the filename is right. A message box  will be displayed to inform you that the Test Connection succeeded. Click the OK button in the message, and click the OK button in the Data Link Properties dialog box.

The Add a Database Connection dialog box is displayed. Enter a name for the connection.

add image

STATISTICA will remember this connection the next time you retrieve data from the same database. Click the OK button. The Database Connection dialog box is displayed.

data con 2 image

Click the OK button. The STATISTICA Query dialog box is displayed.

query image

The STATISTICA Query dialog box is divided into three panes. The left pane lists all the tables in your database. In this example we have just one table – Orders.

Drag the table that you want to query to the top-right pane.

query 2 image

Now, choose the fields that you want to retrieve into STATISTICA. STATISTICA automatically builds the corresponding SQL for this.

query 3 image

There are a several tabs in the lower-right pane:
Field Sequence. The field sequence can be changed on this tab. Suppose you want ID to be the first variable; drag this field to the top.

Criteria. This is equivalent to a where clause of a SQL statement. You can drag a field to this tab, and STATISTICA will display the Add Criteria dialog box, where you can filter out the data if needed.

Click the Values button to see the distinct values in the field if you are unsure of the contents of the fields.

cirteria tab image

Select the values you want, and click the OK button.

add criteria image

Click the Add button.

STATISTICA will give you the option to add another filter criteria for the data.

blank add image

If you are finished, click the Close button. If you want to add another condition, select either the And option button or the Or option button, and then add another filter as previously described.

Sort. You can sort the fields in ascending or descending order. Drag the field you want to sort by to the Sort tab, and choose the sort order based on that field (select a field and then click the button with two arrows to toggle from ascending to descending). You can add more than one field. STATISTICA will sort them in the same order they are listed. That is, first sort by field one and then by field two and so on.

sort tab image

Preview Data. On this tab, you can preview a subset of the data you have chosen to retrieve. You can retrieve more data by pressing F11 on your keyboard.

preview tab image

SQL Statement. STATISTICA writes a SQL statement as you choose the fields to retrieve and add filter conditions. You can preview this SQL statement on this tab.

SQL image

On the STATISTICA Query toolbar, click the button with green triangular arrow to retrieve the data into STATISTICA.  If you have a spreadsheet open, STATISTICA lets you choose whether to insert data into the active spreadsheet or a new spreadsheet.

return image

Click the Run now button; STATISTICA will import the data from the database into STATISTICA.


About statsoftsa

StatSoft, Inc. was founded in 1984 and is now one of the largest global providers of analytic software worldwide. StatSoft is also the largest manufacturer of enterprise-wide quality control and improvement software systems in the world, and the only company capable of supporting its QC products worldwide, with wholly owned subsidiaries in all major markets (StatSoft has 23 full-service offices, on all continents), and its software is available in more than 10 languages.

Posted on March 5, 2012, in Uncategorized. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: