Import data from MySQL to SPSS 15

It so happened that I needed the data collected by the site engine for analysis. The data is stored in MySQL, and it was more convenient for me to analyze it in SPSS. I did not succeed in finding instructions on how to carry out this specific import, so I give here instructions for those who encounter a similar problem.

1. Database Connector

The main problem you encounter when trying to import data from MySQL to SPSS is the lack of the necessary data driver. As it turned out, such a driver (MySQL Connector / ODBC) does not come bundled with the server itself, so we will need to get it from the MySQL website ( link ).

After installing it, restart SPSS (if it was started), and go to the menu File ► Open Database ► New Query ...



If no other database connections have been created before, you will be asked to create a new one. Click "Yes."



Otherwise, in the “Database Wizard” window (see below), you will need to click the “Add ODBC Data Source ...” button.

So, before us, the connection settings dialog is open. Depending on whether we want to provide access to our connection only for ourselves or for all users of the machine, select the “User DSN” or “System DSN” tabs, respectively. Their contents are absolutely identical. Let's say the base is located on the same machine as SPSS itself.



Click the "Add" button. Before us is a list of available drivers, in which there is a bunch of everything. We need the MySQL ODBC Driver. Click Finish.



Before us opens the MySQL connection settings window. We need to fill in the standard connection parameters:

• Data Source Name - connection name. Try not to use special characters, because in this case the connection may not be created;
• Description - comment on the connection name;
• Server - the name or ip-address of the server hosting MySQL;
• Port - port through which communication with the server is carried out. Usually 3306;
• User - the username that will be used when registering with the server. If regular access to the database data is expected, then it makes sense to create a separate user for SPSS;
• Password - well, understand, the password for the MySQL account;
• Database - the name of the database from which the data will be taken. If the correct connection parameters are entered, then when you click on the arrow in the drop-down list there will be the names of all the bases visible to the user;

Click OK. In the "ODBC Data Source Administrator" window, our connection appeared. Click OK.

2. Create a new request

Now that we have the connection created, go to File ► Open Database ► New Query again ... The query wizard window opens in front of us.



Choose what we need and click "Next>". Before us is a data selection window. The list on the left shows all the tables available in the database, and if we expand them, we will see columns ( variables in SPSS terminology). Suppose we need data from all columns of a table store_items. To do this, grab the store_items from the left list with the mouse and drag it to the right. In this case, individual columns will be represented as <table name>: <column name>.



The next two wizard windows are needed if you need to set certain selection conditions and recode variables. We will not dwell on them here. Click "Next>" two times. The Results window is now open.



In this window, you can do the following:

1. Check the generated SQL query. In theory, there should not be any problems, but still it is worth making sure;
2. Choose whether to execute the request now or translate it into the Syntax language format for further editing (useful when building complex samples);
3. Save the request to a text file.

Suppose we decide to open the Syntax editor instead of executing the request immediately. In this case, we get this window:



In this case, we won’t change anything, so just go to the Run ► All menu. Voila! - and the data from the table in our SPSS.

3. Now about the sad

There are a few points to keep in mind when importing data from MySQL:

1. SPSS is extremely crooked with importing string data , so if it is possible to transcode it into numeric, it is better to do this in the penultimate wizard window. If this data is extremely important, then it may be better to export it from MySQL to csv , prepare the data structure in SPSS and import it;
2. SPSS is not designed to work with binary data , it is a tool for statistical analysis of human-readable data, and it will crash at the very first attempt to import such data into it. Apparently, this is a flaw in the SPSS ag testers;
3. The import mechanism in SPSS is not very well debugged , so the program may crash. Remember to save before importing new data.