odbcConnectExcel function from RODBC package for R not found on Ubuntu

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'.