Getting data into a database

This topic provides some notes that you may find helpful when importing data into relational databases such as Microsoft Access 97.

The direct method

It is possible for behavioural tasks to store data in a database directly. Many of the behavioural clients written for Whisker do just this (e.g. SecondOrder, SeekTake, ImpulsiveChoice, FiveChoice, etc.). They ask you which database you'd like to use, and then store data in tables in the correct format. They're very easy to use, and not that hard to program, either, if you use Visual C++ (which can set up classes for you to export data to a given database).

The indirect method

A method that needs less programming skill, and can apply to a great variety of languages and computers, is to export your data in an ASCII text file. The trick is to save the data in a format that is easily importable into a database. Regrettably, there is no easy way to save data destined for multiple database tables into a single text file. You could save data into a single file and process this file later, splitting it into several files for importing into a database. Alternatively, you could save one text file per table. In this case, the files should be saved in comma-delimited format, with the field names on the first row. Our example data might have come from two files looking like this:

response-Feb2000.txt

Date,Rat,NumResponses,NumStimuli,NumReinforcements

2/17/2000 12:29:00,M4,56,5,1

2/17/2000 14:37:06,M5,437,43,8

3/17/2000 12:54:00,M4,263,26,5

groups.txt

Rat,Group

M4,sham

M5,lesion

It is very easy to import data in this format into Access (choose File → Get External Data → Import, choose your text file, ensure that you choose Delimited format and that First Row Contains Field Names is selected; then simply choose the table to put the data in).

Amalgamating multiple files

A problem I had with this method is that it did take a long time to import every single file. So I wrote a small utility called amalgam.pl (written in the language Perl) to amalgamate lots of similar files that were all destined for the same database table, in such a way that the header row (with the field names) only appeared once.

This tool is supplied with Whisker (by default, somewhere within \Program Files\WhiskerControl). To use it, you will need to obtain Perl (from http://www.ActiveState.com/) and install it. (I'm afraid that their distribution terms prevent me from supplying you with a copy of Perl together with Whisker.)

You can either copy amalgam.pl to a directory that is on your path, or you can add the directory containing amalgam.pl to the path. To do the latter, choose Control Panel → System → Environment. Click on the Path variable and edit the Value field to append the directory that Amalgam is in, using semicolons to separate consecutive path entries (e.g. ;d:\program files\rudolf cardinal\whisker\amalgam;). Then click Set and OK. If you change the System path, you'll affect all users of the system; if you change the User path, only your own user. Only administrators can change the system path.

Once you have that set up, simply fire up a command prompt and change to the directory containing your data. Let's say you have a whole host of response data files, called response-Jan2000.txt, response-Feb2000.txt, response-Mar2000.txt, and so on. You can issue this command:

amalgam.pl big-response-file.txt response*.txt

and it will create a single file, big-response-file.txt, which you can import into Access in one step. If you want to import into five tables, you are aiming to generate five files. (Amalgam will not let you merge files that have different headers.)

A note on dates

Access's text import facility will only recognize dates as such if they are in a very particular format. For the UK, it is this:

17/03/2000 15:30:05

and this depends on telling Windows that you are in the UK in Control Panel → Regional Settings. (Specifically, Access recognises the 'short date' and 'short time' pictures set up in Control Panel.) It is so useful to have date/time-stamping of your data (and so awkward to configure the Access's import facility differently) that I strongly suggest you configure your regional settings and write your behavioural tasks to save data to disk in this format.