odbcConnectExcel function from RODBC package for R not found on Ubuntu

r packages rodbc
Milktrader picture Milktrader · Aug 6, 2010 · Viewed 9.4k times · Source

Installing the RODBC package on Ubuntu is a bit of a kludge. First I learned to install the following: 

$ sudo apt-get install r-cran-rodbc

That wasn't good enough as the package was still looking for header files. I solved this issue by:

$ sudo apt-get install unixodbc-dev

Good, RODBC installed properly on the Ubuntu machine. But when I try to run the following script:

## import excel file from Dropbox

require("RODBC")

channel <- odbcConnectExcel("~/Dropbox/DATA/SAMPLE/petro.xls")

petro <- sqlFetch (channel, "weekly")

odbcClose(channel)

str(petro)
head(petro)

I get an error thrown that function odbcConnectExcel not found. I checked the case of each letter, making sure it was not a simple typo. Nope. Then I ran this same script on a Windows R installation (file path different, of course) and the script works.

Any idea of why Ubuntu R installation cannot find the odbcConnectExcel function and how I can get this to work?

Answer

Dirk Eddelbuettel picture Dirk Eddelbuettel · Aug 6, 2010

That functionality is available where Excel is available. In other words: not on Ubuntu.

For reference, from the R Data Import / Export manual (with my highlighting):

4.3.2 Package RODBC

Package RODBC on CRAN provides an interface to database sources supporting an ODBC interface. This is very widely available, and allows the same R code to access different database systems. RODBC runs on Unix/Linux, Windows and Mac OS X, and almost all database systems provide support for ODBC. We have tested Microsoft SQL Server, Access, MySQL, PostgreSQL, Oracle and IBM DB2 on Windows and MySQL, Oracle, PostgreSQL and SQLite on Linux.

ODBC is a client-server system, and we have happily connected to a DBMS running on a Unix server from a Windows client, and vice versa.

On Windows ODBC support is normally installed, and current versions are available from http://www.microsoft.com/data/odbc/ as part of MDAC. On Unix/Linux you will need an ODBC Driver Manager such as unixODBC (http://www.unixODBC.org) or iOBDC (http://www.iODBC.org: this is pre-installed in Mac OS X) and an installed driver for your database system.

Windows provides drivers not just for DBMSs but also for Excel (.xls) spreadsheets, DBase (.dbf) files and even text files. (The named applications do not need to be installed. Which file formats are supported depends on the the versions of the drivers.) There are versions for Excel 2007 and Access 2007 (go to http://download.microsoft.com, and search for Office ODBC, which will lead to AccessDatabaseEngine.exe), the `2007 Office System Driver'.

Related questions

Where does R store packages?

The install.packages() function in R is the automatic unzipping utility that gets and install packages in R. How do I find out what directory R has chosen to store packages? How can I change the directory in which R …

Read More
Check for installed packages before running install.packages()

I have an R script that is shared with several users on different computers. One of its lines contains the install.packages("xtable") command. The problem is that every time someone runs the script, R spends a great deal of …

Read More
Elegant way to check for missing packages and install them?

I seem to be sharing a lot of code with coauthors these days. Many of them are novice/intermediate R users and don't realize that they have to install packages they don't already have. Is there an elegant way to …

Read More