[R] Access Data Base Reading on Linux Platform

[Marc Schwartz]

> On Jul 31, 2017, at 5:51 AM, Lorenzo Isella <lorenzo.isella at gmail.com> wrote:
> 
> Dear All,
> I am really far from a database expert (I do prefer flat files as long as
> that is reasonable), but I have to deal with an accdb database (Microsoft
> Access new format). It all stems from the fact that I run R almost
> exclusively on Debian platforms.
> I did a bit of googling
> 
> http://r.789695.n4.nabble.com/How-to-load-data-from-accdb-into-R-td4656613.html
> 
> http://rprogramming.net/connect-to-ms-access-in-r/
> 
> https://www.r-bloggers.com/getting-access-data-into-r/
> 
> but it seems you cannot go very far unless you can install some microsoft
> drivers on your platform.
> Hence, my two questions
> 
> 1) Can I read (I do not need write permissions) an accdb database on linux
> using R without resorting to WINE?
> 
> 2) If 1) is cumbersome, to what R friendly format (excluding for now a csv
> dump) I can convert the data base? If this is doable entirely in Linux?
> Otherwise, worse comes to worse, how to make this one time conversion from
> Windows? I read there are ways from Access to convert everything to mdb,
> which should be more R friendly, but I need the opinion of someone more
> experienced than me here.
> 
> Many thanks
> 
> Lorenzo


Hi,

As an FYI for future reference, there is a dedicated R and DB list here:

  https://stat.ethz.ch/mailman/listinfo/r-sig-db

That being said, you are likely better off trying to get the data either in MS Excel formats, for which there are cross-platform R packages on CRAN that can help read the XLS/XLSX data or get CSV files which are easily read in R.

There is a utility called MDBTools (https://github.com/brianb/mdbtools), but it only supports the older MDB file format.

A couple of alternatives would be:

Use MDBOpener.com (http://www.mdbopener.com), which provides an online conversion tool where you can then export the data from the ACCDB file to either Excel or CSV files, if the source of the data cannot do it for you.  

Use Jackcess (http://jackcess.sourceforge.net) which is a Java based library to create an application which could be called within R. This would be more cumbersome that the aforementioned options and would depend upon how much work you want to do to avoid the Excel or CSV based approach.


I have not used any of the above, so cannot speak for either ease of use or the validity of the tools.

There is also a general resource here:

  https://www.lifewire.com/accdb-file-2619459

The OpenOffice/LibreOffice based options listed there are likely more work than they are worth.

Regards,

Marc Schwartz