[R] ms access --> mysql --> R in Linux
david.whiting at ncl.ac.uk
Mon Nov 1 22:35:57 CET 2004
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.
> 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
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...
University of Newcastle upon Tyne, UK.
More information about the R-help