16

I am trying to write an open data package that reads New York State education data into R. That data are provided as an Access database.

I want to write a function that downloads, reads, and imports those files, and I want it to be supported across platforms. The existing approach suggests installing a 32-bit version of R, which does not fit the bill for programmatic access.

A tour of #RStats suggests that this is a common pain point (1, 2, 3, 4)

On OS X/Linux, you can use mdb.get from the Hmisc package, provided that you have a third party library called mdb-tools. But this seems to be of little help for Windows.

An accepted answer will read the .mdb file linked above into R across Windows, OS X, and Linux.

ToroJ
  • 15
  • 4
Andrew
  • 9,090
  • 8
  • 46
  • 59
  • Um…did you look at the contents of the ZIP file? the `.mdb` file is a single-table with what looks like the _exact same data_ as the plain text and highly cross-platform `.tab` file. – hrbrmstr Jun 20 '16 at 02:22
  • Hi @hrbrmstr - I probably should have picked a different example in the link - the goal is to read many years of data, and NY State only started releasing the `.tab` in the past year. If you look at https://data.nysed.gov/files/assessment/13-14/3-8-2013-14.zip for instance, you'll see that it's only a `.mdb`. – Andrew Jun 20 '16 at 13:00
  • If you look at the twitter links you'll see that @david-lawrence-miller, author of the R `Distance` package has also bumped into this problem. Not sure if the close vote is yours but if you could reconsider I would be grateful. – Andrew Jun 20 '16 at 13:02
  • 3
    `if` is your friend. there's nothing wrong with wrapping platform specific solutions (i.e. RODBC for Windows and `mdb.get()` for linux or macOS) in `if` statements. Sometimes you actually have to work to get the data in. – hrbrmstr Jun 20 '16 at 13:04
  • @hrbrmstr Good call. Andrew you can answer your own question via an if/else. – Tyler Rinker Jun 21 '16 at 00:52

1 Answers1

2

How about just with RODBC? Can you also download and use the mdb file (e.g. to make queries/views directly inside the mdb?)

I usually load data from Access dbs into R with the following code chunk:

# read in the data
library(RODBC)
db <- odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)};
                        DBQ=C:\\Path\\To\\Database\\my_db.accdb")

# Get data
data <- as_tibble(sqlFetch (db , "Table or Query Name", rownames=TRUE))
DarwinsBeard
  • 527
  • 4
  • 16
  • 1
    This doesn't answer the question because the OP is already aware of the RODBC approach (link in 2nd paragraph), found it didn't work for their problem because of the requirement to use 32-bit R, and asked for an alternative solution that will work across platforms. – Jan Boyer Jul 02 '18 at 16:04
  • Thanks @DarwinsBeard - I will give that a try! – Andrew Jul 10 '18 at 15:18