[R] A little more on R, mdbtools and Access databases

[David Whiting]

Hi again,

I have played a little more with mdbtools and R. I downloaded the
latest version of mdbtools from sourceforge (version 0.6pre1). Quickly
scanning the mailing list suggests that ODBC seems to work with PHP
but I have not been able to get it to work with R. I can make a
connection to the database and when I do a query I get back the names
of the rows but not the data. I must admit I have not spent long
trying to figure it out.

For my own use I am able to work directly with a database file on my
local machine, and to make things easier for myself I have
concentrated on making some simple functions (based on those I posted
yesterday) that use mdbtools to:

i) get table names, 
ii) describe tables, 
iii) read tables into R, and
iv) use the (basic) SQL functionality of mdbtools to perform simple
queries (really only able to select subsets of columns and rows)

Here is an example session using a database I found on the web at
http://www.microsoft-accesssolutions.co.uk/downloads/login.zip


> db <- "/home/dave/tmp/login.mdb"
> mdbTables(db)
[1] "MSysObjects"       "MSysACEs"          "MSysQueries"      
[4] "MSysRelationships" "MSysAccessObjects" "tblEmployees"     
> mytab <- mdbTables(db)[6]
> mytab
[1] "tblEmployees"
> x <- mdbReadTable(db, mytab)
> str(x)
`data.frame':	4 obs. of  4 variables:
 $ lngEmpID      : int  1 2 3 4
 $ strEmpName    : Factor w/ 4 levels "David","Gavin",..: 3 2 4 1
 $ strEmpPassword: Factor w/ 4 levels "david","gavin",..: 3 2 4 1
 $ strAccess     : Factor w/ 2 levels "Admin","User": 1 2 2 2
> head(x)
  lngEmpID strEmpName strEmpPassword strAccess
1        1     Graham         graham     Admin
2        2      Gavin          gavin      User
3        3      Lynne          lynne      User
4        4      David          david      User
> mdbDescribe(db, mytab)
      ColumnName         Type Size
1       lngEmpID Long Integer    4
2     strEmpName         Text   20
3 strEmpPassword         Text   20
4      strAccess         Text   40

> mdbQuery(db, "select lngEmpID, strAccess FROM tblEmployees where lngEmpID < 3")
  lngEmpID strAccess
1        1     Admin
2        2      User
> 









Here are the functions:

### Some quick code to make use of mdb-tools to use MS Access tables in R.
### 2004-11-02
### David Whiting


require(gdata) # for the trim function.

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


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


mdbDescribe <- function(dbname,tableName) {
  tableName <- dQuote(tableName)
  cat("describe table ", tableName, "\ngo", file = "tempR.sql")
  mdesc <- system(paste("mdb-sql -i tempR.sql ", dbname), intern=TRUE)
  mdesc <- strsplit(substring(mdesc[-c(1:3,5, length(mdesc), length(mdesc)-1)], 2), "\\|")
  tabDesc <- rbind(mdesc[[2]])
  for (i in 3:length(mdesc)) {
    tabDesc <- rbind(tabDesc, mdesc[[i]])
  }
  tabDesc <- matrix(trim(tabDesc), ncol=3)
  tabDesc <- data.frame(tabDesc)
  names(tabDesc) <- c("ColumnName", "Type", "Size")
  tabDesc$Size <- as.numeric(levels(tabDesc$Size)[tabDesc$Size])
  system("rm -f tempR.sql")
  tabDesc
}


mdbQuery <- function(dbname, mstatement, header=FALSE, footer=FALSE) {
  cat(mstatement, "\ngo", file = "tempR.sql")
  sqlOptions <- "-p"
  if (!header) sqlOptions <- paste(sqlOptions, "H", sep="")
  if (!footer) sqlOptions <- paste(sqlOptions, "F", sep="")
  sqlStatement <- paste("mdb-sql", sqlOptions)
  tmp <- read.table(pipe(paste(sqlStatement, "-i tempR.sql", dbname)), sep="\t")
  names(tmp) <- trim(unlist(strsplit(substr(mstatement, 7, regexpr(" [Ff][Rr][Oo][Mm]", mstatement)[1]), ",")))
  system("rm -f tempR.sql")
  tmp
}


 


-- 
David Whiting
University of Newcastle upon Tyne, UK