ms access --> mysql --> R in Linux
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.