How to read data from Microsoft Access .accdb database files into R?

P

8

20

The RODBC documentation suggests it is possible, but I am not sure how to read data from a Microsoft Access (the new .accdb format) file with this package into R (on Debian GNU/Linux). The vignette talks about drivers, but I do not quite understand how I can see which drivers are installed, and in particular, if I have a driver installed for me to access those .accdb files.

What code do you use to read data from .accdb files? And please indicate what platform you are on and if you had to install a special driver.

Pyrrhonism answered 18/8, 2011 at 6:32 Comment(1)

So far as I've seen, there is no ACE support available on Linux, only Jet 4. Thus, you'd need MDB format instead of ACCDB format. But I'm an Access programmer and Windows user, so I could have missed it -- but it hasn't come up on SO. – Kosse 19/8, 2011 at 23:22

B

5

The title of the page you linked, RODBC: ODBC Database Access, may be misleading. Access doesn't mean MS Access; in that title access means connectivity. RODBC is an ODBC manager for R. It serves as the mediator to provide communication between R and the ODBC driver for your target database. So for GNU/Linux, you would still need an ODBC driver for MS Access database files ... RODBC doesn't provide one.

However, I don't know of any free (as in freedom and/or beer) MS Access ODBC drivers for Linux. Easysoft sells one, but it's not cheap. There may be offerings from other vendors, too; I haven't looked.

It might be easier to use a Windows machine to export your ACCDB to a format R can use. Or run R on Windows instead of Linux.

Borries answered 18/8, 2011 at 20:43 Comment(2)

I missed the "GNU/Linux" bit. You're right - no chance on Linux. – Catullus 19/8, 2011 at 4:0

Well, I saw things like this post, where they do access .mdb files: rforge.org/2009/08/07/open-access-mdb-file-with-rodbc But, I get the point that I need a Windows driver for it at this moment... indeed, this odbcConnectAccess() method seems not available in my rodbc package... thanx for the pointers! – Pyrrhonism 20/8, 2011 at 19:10

H

9

To import a post-2007 Microsoft Access file (.accdb) into R, you can use the RODBC package.

For an .accdb file called "foo.accdb" with the following tables, "bar" and "bin", stored on the desktop of John Doe's computer:

library(RODBC)    #loads the RODBC package
dta <- odbcConnectAccess2007("C:/Users/JohnDoe/Desktop/foo.accdb")   #specifies the file path
df1 <- sqlFetch(dta, "bar")   #loads the table called 'bar' in the original Access file
df2 <- sqlFetch(dta, "bin")   #loads the table called 'bin' in the original Access file

Hemicycle answered 11/5, 2016 at 18:14 Comment(6)

I'm not seeing this function in the RODBC library. – Morgenthaler 3/7, 2018 at 19:57

Which version of RODBC are you on (packageVersion("RODBC"))? On 1.3.15 both of those functions (odbcConnectAccess2007() and sqlFetch() show up for me. – Hemicycle 19/7, 2018 at 0:59

Ah, these functions only exist on windows R, not linux R that I was running – Morgenthaler 19/7, 2018 at 21:48

Is this still valid? This function is not documented: cran.r-project.org/web/packages/RODBC/RODBC.pdf – Lonne 21/4, 2020 at 8:43

Yes, @Lonne it should still be valid. I have the newest version of RODBC installed and it still has all of those functions available. – Hemicycle 21/7, 2020 at 19:29

Also, another common problem people face when importing Access databases into R is incongruity between 32-bit and 64-bit. They need to be the same. For example, if your Access program is 32-bit, then you need to use the 32-bit version of R or, you may be able to install a new driver for Access to solve that issue. – Hemicycle 17/12, 2020 at 18:50

B

5

The title of the page you linked, RODBC: ODBC Database Access, may be misleading. Access doesn't mean MS Access; in that title access means connectivity. RODBC is an ODBC manager for R. It serves as the mediator to provide communication between R and the ODBC driver for your target database. So for GNU/Linux, you would still need an ODBC driver for MS Access database files ... RODBC doesn't provide one.

However, I don't know of any free (as in freedom and/or beer) MS Access ODBC drivers for Linux. Easysoft sells one, but it's not cheap. There may be offerings from other vendors, too; I haven't looked.

It might be easier to use a Windows machine to export your ACCDB to a format R can use. Or run R on Windows instead of Linux.

Borries answered 18/8, 2011 at 20:43 Comment(2)

I missed the "GNU/Linux" bit. You're right - no chance on Linux. – Catullus 19/8, 2011 at 4:0

Well, I saw things like this post, where they do access .mdb files: rforge.org/2009/08/07/open-access-mdb-file-with-rodbc But, I get the point that I need a Windows driver for it at this moment... indeed, this odbcConnectAccess() method seems not available in my rodbc package... thanx for the pointers! – Pyrrhonism 20/8, 2011 at 19:10

C

3

You'll need the drivers to connect Access to the ODBC interface. These should be on your system if you have Access installed. If not, download the Access Database Engine from Microsoft. Then create your data connection in ODBC (You may need to run the 32-bit c:\windows\sysWOW64\odbcad32.exe if running 64-bit Windows). Note that this method doesn't work on GNU/Linux. The runtimes are Windows only, as mentioned by @HansUp below.

As for code, you'll probably start with odbcConnect(dsn, uid = "", pwd = "", ...), and the documentation can help with the details.

Catullus answered 18/8, 2011 at 16:22 Comment(0)

C

3

ODBC is a bit of 'plug and pray' system connecting different bricks.

RODBC allow you to get something from an ODBC provider into R. What you still need is the (for lack of a better word) ODBC-exporting driver of the database system in question. Which you need on your OS --- so I think with the Access-into-Linux combination you are without luck. Windows-only.

People have managed to access SQL Server using FreeTDS drivers (for the TDS protocol underlying Sybase and via an early license also MS-SQL) but it is usualluy a fight to get it going.

Cosgrave answered 18/8, 2011 at 16:44 Comment(0)

E

2

library(RODBC)
db<-file.path("student.accdb")
channel<-odbcConnectAccess2007(db)
data<-sqlFetch(channel,"stud")

data
  ID  Name M1 M2 M3 M4 M5 Result
1  7 Radha 85 65 92 50 62   Pass
2  8  Reka 75 85 96 75 85   Pass

Empurple answered 30/1, 2014 at 5:41 Comment(0)

C

2

The best method that worked for me

#Package
library(RODBC)

#Defining the path
datab<-file.path("Main_File.accdb")
channel<-odbcConnectAccess2007(datab)

#reading the individual files inside the Main
table<-sqlFetch(Channel,"File_1")

This will fetch data from the "File_1" inside the Main_File.

But the above code did not support the UTF encoding.

Cellule answered 8/3, 2018 at 9:47 Comment(0)

C

1

An alternative to directly accessing it might be to facilitate the data export from MS Access. At least the most recent MS Access allows to save the various export steps. One can then simply run the export of various queries / tables fairly quickly.

I know this does not answer the question, but might be a workaround if you do not get RODBC to run.

Contrivance answered 11/6, 2012 at 18:18 Comment(0)

K

-1

My solution (the most simple that I found):

install "Access Database Engine" from Micosoft
configure the connection to the Access data base (mdb or accdb) in Windows Administrative Tools, using the ODBC 32b tool. I's also possible to use c:\windows\sysWOW64\odbcad32.exe
run RStudio in 32b mode ; it can be fixed in RStudio settings (relaunch RStudio after any change)
finally, the RODBC functions work successfully.

CAUTION: it works only in Windows, not in linux. Personnally I use Windows as a Virtual Box guest within Xubuntu.

Katricekatrina answered 28/11, 2019 at 10:6 Comment(0)

Recommended topics

Hot tags