[R] ms access --> mysql --> R in Linux

[David Whiting]

Anne York <york at zipcon.net> writes:

> I am trying to use some ms access databases in R (version 1.9.1 or 2.0
> on a Debian system).  In searching the net for promising software to
> do this, I found mdbtools. Mdbtools claims the ability to convert
> schemas and tables in MS Access to MySQL and other databases.
> 
> http://mdbtools.sourceforge.net/
> 
> I'm wondering if anyone in the R community has tried using this
> software to use MS-Access databases in R with a Linux system. If so,
> Were you successful? What kind of problems did you encounter?

I have used it several times. The ODBC driver is not really (at all?)
working but the command line tools seem to work well enough.  I
modified mdb-export and created a script that worked like mysqldump
(to produce CREATE TABLE and INSERT statements) so that I was able to
get the tables into MySQL to use with R.  The latest version is quite
a bit more recent than the one I have been using and I believe it has
more features. 

Here is something that I have just tried and seems to work and avoids
the need to take your data into another database:

x <- read.table(pipe('mdb-export -d "\t" databasename.mdb tableName'), sep="\t", header=TRUE)

mdb-export exports the contents of a table from an Access database
(fields separated with commas by default). The -d option specifies the
delimiter (I prefer to use a tab).  This seems to work well on my
relatively small test database. I guess it would not take much work to
write a little set of functions to get the table names (using
mdb-tables) and do some other useful things. Not as good as having a
working ODBC driver, but quite nice all the same.

I'll have spend a little more time playing with this...

Dave


-- 
David Whiting
University of Newcastle upon Tyne, UK.