Import data from MySQL to SPSS 15

It so happened that for analysis I needed the data collected by the site's engine. The data is stored in MySQL, and it was more convenient for me to analyze it in SPSS. I didn’t manage to find instructions on how to implement this specific import, so I’m giving instructions here for those who are faced with 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, a similar driver (MySQL Connector / ODBC) is not bundled with the server itself, so we will need to take it from the MySQL site ( link ).

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


If no other database connections were 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, we have a dialog for setting up connections. 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. Suppose the base is located on the same machine as SPSS itself.



We press the button "Add". Before us is a list of available drivers, in which there is a lot of everything. We need 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 - the name of the connection. Try not to use special characters, because in this case the connection may not be created;
• Description - comment to the connection name;
• Server - the name or ip-address of the server hosting MySQL;
• Port - the port through which communication with the server is carried out. Usually 3306;
• User - the user name that will be used when registering with the server. If it is intended to regularly access the database data, then it makes sense to create a separate user for SPSS;
• Password - well, of course, the password of the MySQL account;
• Database is the name of the database from which the data will be taken. If the correct connection parameters are entered, then clicking on the arrow in the drop-down list will contain the names of all databases visible to the user;

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

2. Creating a new query

Now that we have the connection created, go back to File ► Open Database ► New Query ... A query wizard window opens before us.



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



The next two wizard windows are needed if you want to set certain selection conditions and re-encode variables. We will not dwell on them here. Click "Next>" twice. Now the “Results” window is open.



In this window you can do the following:

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

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



In this case, we will not change anything, so just go to the menu Run ► All. Voila! - and data from the table we have in SPSS.

3. Now for the sad

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

1. SPSS is extremely crooked with the import of string data , so if you can convert it to numeric, it is better to do it in the penultimate window of the wizard. 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 when you first try to import such data into it. Apparently, this is a flaw in the testers SPSS ag;
3. The import mechanism in SPSS is not well-established , so the program can crash. Do not forget to save before importing new data.