RODBC odbcConnectExcel function from R package not found on Ubuntu

Question

Installing the RODBC package on Ubuntu is a bit of a shred. First I learned how to set the following:

$ sudo apt-get install r-cran-rodbc 

This was not enough because the package was still looking for header files. I solved this problem:

 $ sudo apt-get install unixodbc-dev 

Well, RODBC is correctly installed on an 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 that the odbcConnectExcel function was not found. I checked the case with each letter, making sure this is not a simple typo. Nope. Then I ran the same script when installing Windows R (of course, the path to the file) and the script works.

Any idea why installing Ubuntu R cannot find the odbcConnectExcel function and how can I make it work?

Answer 1

This feature is available where Excel is available. In other words: not on Ubuntu.

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

4.3.2 RODBC Package

The RODBC package on CRAN provides an interface to database sources supporting the ODBC interface. It is very widely available and allow the same R-code to access other database systems. RODBC runs on Unix / Linux, Windows, and Mac OS X and almost all database systems provide ODBC support. We 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 are successfully connected to the DBMS running on a Unix server with Windows, and vice versa.

Windows ODBC support for commonly installed and current versions Available from http://www.microsoft.com/data/odbc/ as part of MDAC. On Unix / Linux, you'll need an ODBC driver manager, such as unixODBC ( http://www.unixODBC.org ) or iOBDC ( http://www.iODBC.org : this is preinstalled on Mac OS X) and a driver for your database system.

Windows provides drivers not only for DBMS, but also for Excel (.xls) tables, DBase files (.dbf), and even text files. (The named applications do not need to be installed. What file formats are supported depending on the driver versions.) There are versions for Excel 2007 and Access 2007 (go to http://download.microsoft.com and find Office ODBC , which will lead to AccessDatabaseEngine.exe ), `2007 Office System Driver '.

Answer 2

I found RODBC a real pain in Ubuntu. Maybe because I do not know the right spells, but I switched to RJDBC and they were more fortunate. As discussed here .

As Dirk says, this will not solve your problem with Excel. For writing Excel, I was very lucky with the WriteXLS package. On Ubuntu, it was pretty easy for me to set up. I had Perl and many of the packages already installed, and I just had to install Text :: CSV_XS, which I installed using the GUI package manager. The reason I like WriteXLS is the ability to write data frames to different sheets in an Excel file. And now, when I look at your question, I see that you want to read Excel files, not WRITE. Hell. WriteXLS does not. Stick to gdata, as Dirk said in his comments:

gdata on CRAN , and you will need the read.xls () function:

 read.xls("//path//to/excelfile.xls", sheet = 1, verbose=FALSE, pattern, ..., method=c("csv","tsv","tab"), perl="perl") 

you may need to run installXLSXsupport , which installs the required Perl modules.

read.xls expects sheet numbers, not names. A method parameter is just an intermediate file format. If your data has tabs, then do not use the tab as an intermediate format. And also for commas and csv.