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

[David Whiting]

David Whiting <david.whiting at ncl.ac.uk> writes:

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

Well, here's a simple start.  There's more checking that needs to be
done, but this makes the process a little more straight forward:


mdbTables <- function(dbname) {
  system(paste("mdb-tables -d '\t' -S", dbname), intern=TRUE)
}


mdbExport <- function(dbname,tableName) {
  tableName <- dQuote(tableName)
  read.table(pipe(paste("mdb-export -d '\t' ", dbname,  tableName)), sep="\t", header=TRUE)
}


With these functions you can now do:

mytabs <- mdbTables("myAccessDB.mdb")

to get the list of tables in the DB. The first 5 seem to be
information about the database and the tables seem to start from the
6th element of the vector.  If the table you want is the one named in
the 9th element of the vector:

mytab <- mdbTables("myAccessDB.mdb")[9]

and you want that table in R:

x <- mdbExport("myAccessDB.mdb", mytab)


At the moment you have to specify the extension (.mdb) otherwise
mdb-export cannot find the file.  

-- 
David Whiting
Dar es Salaam, Tanzania