[R] A little more on R, mdbtools and Access databases
david.whiting at ncl.ac.uk
Wed Nov 3 23:08:05 CET 2004
Anne York <york at zipcon.net> writes:
> Wow, thanks for all the good information and ideas on this topic.
> Have you used mdbtools to convert a data base to something like MySQL
> or other open source data base?
Yes, that's mostly how I have used mdbtools so far. I modified
mdb-export and created a script to use my modified mdb-export and
mdb-schema to act like MySQL's mysqldump, i.e. to produce CREATE TABLE
and INSERT statements. That way I was able to get tables straight from
an Access database into MySQL. Here's a link to what I did:
With those changes and the mdbdump script I am able to do the
following to get a table into MySQL:
mdbdump accessDB accessTable | mysql mysqlDB
As it is now mdb-export produces output in delimited format so you
could use mdb-schema to create the table, export the data using
mdb-export and then load it into mysql (or whatever).
[it looks like the INSERT statements option has been added to
mdb-export, but I am not sure about the MySQL backend]
> It seems that if I needed to do
> unions or intersections, I would have to do the conversion and use the
> other database from R since I'd rather work on my Linux machine.
Another alternative would be to use mdbtools with the R functions I
posted earlier to read the tables into R and then use RODBC to save
(sqlSave) them into another database. This would probably be the
easiest because all you need to do is get the current mdb-tools as it
is now (my changes were made to an old version). This is now the my
preferred route. BTW, I have now tidied the functions up a little so
they don't create temporary files anymore.
> On the negative side, Brian Ripley reported problems compiling
> mdbtools, and on the Debian website, there were some security alerts
> (overflow problems) about mdbtools. Clearly, you were able to compile
I had to make some symlinks to get it all to play properly (it seemed
to expect libraries in /usr/lib when they were in /usr/local/lib), but
it seemed to compile okay. The lib problem might have been a problem
on my side though.
> Do you know anything about the security risks?
Not a sausage. I use on my local machine so I don't think that they
apply to my situation. I could easily be wrong though...
University of Newcastle upon Tyne, UK
More information about the R-help