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.
The Database Connection dialog box will be displayed.
Click the New button to display the Data Link Properties dialog box, which lists all the OLEDB providers installed on your machine.
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.
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.
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.
Click the OK button. The STATISTICA Query dialog box is displayed.
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.
Now, choose the fields that you want to retrieve into STATISTICA. STATISTICA automatically builds the corresponding SQL for this.
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.
Select the values you want, and click the OK button.
Click the Add button.
STATISTICA will give you the option to add another filter criteria for the data.
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.
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.
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.
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.
Click the Run now button; STATISTICA will import the data from the database into STATISTICA.