In this tutorial, we’re going to see how to export data from SPSS into the MySQL database. If you want to follow along, you’re going to need the login details for your database, and also the IP address or domain name of the server on which it is hosted.
The Data
As you can see below, we have a simple data set with four variables. For the purposes of this tutorial, it doesn’t really matter what these variables represent, but for reasons that will become clear later it is worth taking note of the presence of the ID variable, which functions as a unique identifier for each case in the data set.
Our task is to get this data into a table in MySQL.
Setting Up a MySQL Data Source
We’re working on the assumption that you have opened SPSS on a Windows operating system, and you’re looking at an empty Data View.
Click on File -> Export -> Database. The Export to Database Wizard will pop up.
If you haven’t previously set up an ODBC data source connection, you’re not going to see anything in the Data Sources box, and you’re going to need to set up the connection.
We’re not going to show you how to do this here, because it’s exactly the same procedure as described in our import into SPSS from MySQL tutorial. You should check that out, setup the ODBC data source connection as detailed there, and then return to this tutorial.
Connect and Export Data
We’re assuming that you now have the ODBC data source connection set up, and that you’re looking at the Export to Database Wizard.
Highlight your data source connection (as above), and then click the Next button.
You’ll now be asked to choose what sort of export you want to set up.
The simplest option is to create a new table within a MySQL database. We’ve selected this option, and named the new table PEFExperiment (see above).
Select Variables to Store in a Table
Clicking the Next button will bring up a dialog box asking you to select the variables you want to be stored in the new table.
The SPSS variables show up in the text box on the left. The idea is to move the variables you want to import into your database over to the right, where you can set a number of their attributes (e.g., type and width).
This is where the significance of the ID variable comes into play. It is normal for a database table to have a primary key, which functions as a unique identifier for each database entry. This is often implemented by means of a field that increments automatically each time an entry is added to a table. This functionality is not supported by the SPSS database wizard. Ideally, therefore, you should ensure that your SPSS data set includes a variable that functions as a unique identifier, and which you can import into the database table. The ID variable performs this role in our data set.
You can specify that a variable should function as a primary key when you select the variables to store in a new table. This is what we’ve done below.
As you can see, we’ve elected to include all our variables within the new database table. To move them over from the left, you just drag and drop. We’ve specified ID as the primary key by ticking the little key icon.
The other thing worth noting is that we’ve instructed SPSS to export the value labels (Male, Female) for the Sex variable rather than the data values (1, 0). This is just for illustrative purposes, and shouldn’t be taken as a recommendation.
Result of the Export
If you set things up correctly, then you can just hit Finish at this point. If you want to check the options you’ve chosen, click Next, and review the summary dialog box that appears.
We’re going to hit Finish to do the export.
This is the output that SPSS generates for an export to a database.
As you’ll be able to see above, there are a couple of SQL statements (marked) that are responsible for creating the new table and generating the records. Let’s see if they’ve worked.
New Table
Here you can see the first 20 rows of the new table that SPSS has created within the MySQL database.
ID has been correctly instantiated as the primary key (though you can’t tell from this screenshot), and the sex variable has been populated with its value labels rather than a numerical data type (in MySQL it’s the varchar data type).
***************
That’s it, really. You should now have an idea of how to export data from SPSS to a MySQL database. In a later tutorial, we’ll look at some of the more sophisticated options on offer during this process.
EZSPSS on YouTube
Check out our YouTube channel for video tutorials, including this tutorial that covers the material above.