Getting Data From The Database

It's often not very easy to get your data into a Statistics program from a database, or to edit the data and monitor it while it's in the database. Here are some ways of doing that.

First, decide what program you're trying to get the data into -- if you can go directly from the survey database to this program, it avoids certain problems that may come up. Then, click on the link below for them to get directions on how to import the data directly.
If you just want to export the data and view it elsewhere, use the Survey Editor. The best format to export into is a Microsoft Excel spreadsheet, if you can use it.

To a spreadsheet from the Survey Editor: This is the simplest way to export, from the Survey Editor GUI.
From MySQLCC: How to edit, view, and export data if you're using MySQL. MySQLCC is a GUI interface to a MySQL database. It's not a great UI -- but if you are familiar with SQL, this is a good program.
To Microsoft Access: If you're familiar with Microsoft Access, you may prefer it, despite its problems.
To Microsoft Access from a DSN file: Someone may have sent you a Microsoft Access database and a file that ends in ".dsn" that won't work on your machine. Here's how to fix it.
To a JSP Survey: How to set up your web survey, or a new web survey, to access the database.
JMP: If you're using JMP for data analysis, you probably want to import your data this way, and not through any other technique.
SAS: If you're using SAS for data analysis, you probably want to import your data this way, and not through any other technique.
Backing up the database: How to back up a MySQL database to a text file.
Warning about importing/exporting data with returns in them ("long answer" questions)

Survey Editor

In the survey manager, select your survey, and then click "Edit Survey", and wait for the survey manager to launch. Then, click the "Export to file..." button, and follow the wizard through.

Alternatively, you can export and edit one table at a time. Go into the "View" menu and check "Show Database Editor". In this database editor, you can see all of the tables in the database and edit it (if you have security permissions to do so). Be careful about editing tables that you're unfamiliar with -- typically, the only tables you'll be interested in editing by hand are "Answers" and "Users", unless you've specifically set up other tables for your survey. You can use the Import and Export buttons to import and export data from the database.

Warning about importing into SAS/JMP:
If you are using SAS or JMP, you should import directly to them in the documentation in that section. They have several bugs (or "features") which often fail to import data correctly. For the best way to import your data without risking corruption, see the sections below about SAS and JMP.

Warning about editing other tables in the Databse Editor: If you start editing your survey itself in the database (most of the other tables, like "Questions", and "SurveyPages" hold the data for your questionnaire), make sure that the Survey Editor is NOT open and that you are not editing something in both places -- you could have some unexpected results if you use both at the same time -- the Survey Editor does not reload data from the database as you edit it.

MySQLCC

Download and install MySQLCC from http://www.mysql.com/downloads
On the toolbar, click the upper left hand button, the "New Database" button.
Type in the following into the screen:
Name: (pick a name)
Host Name: (type in the domain name of the server you're connecting to, for example DomainName.com)
User Name: (this should have been given to you by whomever is administering the database)
Password: (this should have been given to you by whomever is administering the database)
Then you'll be able to get into the database and edit it. The key tables are usually "Users" and "Answers".

If you want to download the data to your computer as a spreadsheet, you can go to "File -> Save -> Save Results". This file will be a "comma separated file", whose filename should end in ".csv". Before you can open it in a stats program, though, you'll have to go in and delete all of the first few rows before you can load it -- delete all the sections that look like this, and save the file:

MySQLCC Warning About Number Of Rows: If you see exactly 1000 rows of your data, you might be running into MySQLCC's default limit of downloading 1000 rows -- to fix this, go to "View -> SQL Pane" and, at the end of the SQL query text, type "LIMIT 5000", or "LIMIT 10000" so that it'll set the limit above 1000 to whatever limit you want, and it will load that number of rows.

MySQLCC Warning About using "long answer" questions and carriage returns: See the warning about carriage returns.

Microsoft Access

You may find Microsoft Access to be the easiest editor if you're already familiar with the MS Access interface. However, there are some klunky things that MS Access does when connecting with other databases which may make it difficult to use. If you have more than 256 variables in one table, forget Microsoft Acesss -- it can't handle them. (You may run into this limit if you have more than 250 or so questions in your survey and you have used the standard interfaces).

Go to http://www.mysql.com/downloads/ and download the "MySQL Connector/ODBC". Install it.
Create a new MS access database.
Go to File -> Get External Data -> Link Tables
In the "Files of type" dropdown, change it to "ODBC Databases()"
Click "New"
Choose "MySQL ODBC Driver"
Click next.
Pick a place to save the file.
Click next a couple times.
Enter the data that your database administrator has given to you:
Host: [type your host name here]
Database Name: [type your database name here]
User Name: [type your mysql userID here]
Password: [type your mysql password here]
Click "Test Data Source" (and make sure it works).
Click Ok.
Click "Select All"
Click "OK".

If this isn't enough information, there is a tutorial at http://www.devshed.com/Server_Side/MySQL/ODBC/page5.html that should explain more.

Microsoft Access from a DSN file

In the above section, Microsoft Access, it shows how to open a database into MS Access. Once you've done this the first time, you've created a ".dsn" file which you can pass around to other people so that they don't have to do all of the steps (and you should keep this file with the database). If you've recieved one of these MS Access databases with the DSN file but it doesn't open, follow these steps.
Go to http://www.mysql.com/downloads/ and download the "MySQL Connector/ODBC". Install it.
Open up "Movers on hciresearch.mdb" -- make sure it's in the same directory as "MoversSurveyODBCLink.dsn".
Test the database, and tell me whether it worked or not (I've never tested these directions from a new machine).

JSP Survey Library Webapp under Tomcat

If for some reason the survey cannot access the database and shows you many error messages, you may need to go into your webapp's directory (for example, "jakarta-tomcat/webapps/SurveyTemplate"), look at "WEB-INF/classes/Configuration.properties" and at "WEB-INF/classes/HowToUseConfiguration.properties"
In that file you will see directions on what to type and where.

JMP

If you're using JMP to analyze your data, you'll first need to import the data. There are some other ways of doing this, but there are several bugs in JMP and SAS which may corrupt your data -- thus, we recommend doing it this way.

1) Go to http://dev.mysql.com/downloads/ and download "MySQL Connector/ODBC". Install it.
2) In JMP, go to File -> Database -> Open Table.
3) Click "Connect". (From here on, the directions are written for Windows JMP -- they may vary for the Mac version).
4) Click the "Machine Data Source" tab.
5) Click the "New..." button.
6) Click "User Data Source" and then click Next.
7) Choose "MySQL ODBC Driver" from the list (probably near the bottom), and click Next, and then click Finish.
8) Enter the database connection information in the appropriate boxes (you should have recieved this from your database administrator), and then "Test Data Source" to make sure you typed everything right. Click OK, and then OK.
9) You can then open any table you want, and save it to a JMP file from there. The typical tables you'll want to import for your statistics are the "Answers" and "Users" tables. (You'll only be using other tables if you explicitly set up your survey to use different tables for logging in and saving answers).
10) If you are using SAS, you can now export your file from JMP to SAS. (If you have directions on how to do this from SAS, please contact us and we'll put those directions up here)

Warning: JMP has a 256-character limit. If you import data into JMP, you might run into data being cut off when a column has a long strings of data that have more than 256 characters in them. This is because JMP has a 256-character limit in character columns. There is no known way to get around this problem (besides using another statistics package).

SAS

If you're using SAS to analyze your data, you'll first need to import the data this way. There are some other ways of doing this, but there are several bugs in JMP and SAS which may corrupt your data or format.

1) Go to http://dev.mysql.com/downloads/ and download "MySQL Connector/ODBC". Install it.
2) In Windows XP, go to Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC).
3) Click the "Machine Data Source" tab.
4) Click the "New..." button.
5) Click "User Data Source" and then click Next.
6) Choose "MySQL ODBC Driver" from the list (probably near the bottom), and click Next, and then click Finish.
7) Enter the database connection information in the appropriate boxes (you should have recieved this from your database administrator), and then "Test Data Source" to make sure you typed everything right. Click OK, and then OK.
8) In SAS, use the following code snippet:

9) You should now have access to the database in SAS.

Warning: The directions for SAS here have not been tested by the authors.

Explanation of bugs in JMP/SAS

Problem #1 (last tested in JMP 5.1):
When JMP imports a file (not a database, as described above), it only looks at the first line to decide what type of data in the column. Any blank columns become character columns. Anything that happened to have a number in the first row that wasn't numeric all the way down does one of two things depending upon how you imported:
a) If you chose the text import preview, it assumes you picked the right type and then the cells become blank.
b) If you open the file without the "text import preview", it converts that column from numeric to character, and then pops up a vague error message saying that some columns were converted (even though, to the user, no columns were converted because the column is always supposed to have been that way).

So, there are three ways of fixing this and getting JMP to import the right way, and they all are monotonous:
a) use the technique mentioned above to import(easiest)
b) In the JMP open dialog box, choose "text import preview" as the file type, and manually go through every column to make sure that it's the right type. (This is even harder than doing this after it gets into JMP).
c) Fix the types after they are imported
d) Make sure that the first row has a number in every column that is numeric, and no numbers in columns that aren't numeric. You could add fake data for the first row with all numbers and strings so it knows what to do correctly, and then delete the row after import.

Problem #2 (SAS):
When SAS imports a CSV file (comma separated values), it parses the line for the commas, splitting by the line. It requires all strings to be surrounded by quotes (""). However, if you have text with a comma in it, like "Austin, TX", SAS will ignore the quotes and import that as "Austin", "TX", meaning that all your columns in that row will be off by one -- this throws off your entire import and corrupts the dataset since it'll recognize types incorrectly and move data around from that point on. It's a huge bug. JMP does not have this problem and you can get around this by importing the file into JMP, and then exporting it to a SAS format from there, or by using the the technique mentione above.

Backing Up Your Database

If you are using the survey editor, you can use the following steps to backup your database.
1) Download and install MySQL from http://mysql.com
2) In the Survey Editor, click the "Export to file" button.
3) Select "SQL Database to file".
4) Use the wizard. You will be prompted to show where you installed MySQL and where the "mysqldump" or "mysqldump.exe" file is, and from there on it should be easy.

If you're not using the Survey Editor, or you want to download a copy of the database straight from a MySQL database, see these directions.

Warning about Carriage Returns in your data

If you use a "long answer" question in the survey, you may have carriage returns in the middle of a participant's response. For example, someone's answer to a question saying "Tell us about your pets" might look like this:

Most programs cannot export or import a standard text file (neither CSV nor Tab-Separated values) that has data in it like this. If the import or export does not support it, you can simply use the Survey Editor to export your data -- it will make sure that the data is exported in a clean format that can be imported into other programs (unlike certain programs).

If you use the Survey Editor to export an "Excel Spreadsheet", the carriage retuns will stay intact in the export. If you export as a "CSV" or as "Tab-separated values", the carriage returns will be replaced by a placeholder, "<br>" (which stands for line break, in HTML).

Credits
Written by Aaron Powers. Last updated February, 2005.