Read an Excel file directly from a R script

Question

How can I read an Excel file directly into R? Or should I first export the data to a text- or CSV file and import that file into R?

Answer 1

Let me reiterate what @Chase recommended: Use XLConnect.

The reasons for using XLConnect are, in my opinion:

1. Cross platform. XLConnect is written in Java and, thus, will run on Win, Linux, Mac with no change of your R code (except possibly path strings)
2. Nothing else to load. Just install XLConnect and get on with life.
3. You only mentioned reading Excel files, but XLConnect will also write Excel files, including changing cell formatting. And it will do this from Linux or Mac, not just Win.

XLConnect is somewhat new compared to other solutions so it is less frequently mentioned in blog posts and reference docs. For me it's been very useful.

Answer 2

And now there is readxl:

The readxl package makes it easy to get data out of Excel and into R. Compared to the existing packages (e.g. gdata, xlsx, xlsReadWrite etc) readxl has no external dependencies so it's easy to install and use on all operating systems. It is designed to work with tabular data stored in a single sheet.

readxl is built on top of the libxls C library, which abstracts away many of the complexities of the underlying binary format.

It supports both the legacy .xls format and .xlsx

readxl is available from CRAN, or you can install it from github with:

# install.packages("devtools") devtools::install_github("hadley/readxl") 

Usage

library(readxl)  # read_excel reads both xls and xlsx files read_excel("my-old-spreadsheet.xls") read_excel("my-new-spreadsheet.xlsx")  # Specify sheet with a number or name read_excel("my-spreadsheet.xls", sheet = "data") read_excel("my-spreadsheet.xls", sheet = 2)  # If NAs are represented by something other than blank cells, # set the na argument read_excel("my-spreadsheet.xls", na = "NA") 

Note that while the description says 'no external dependencies', it does require the Rcpp package, which in turn requires Rtools (for Windows) or Xcode (for OSX), which are dependencies external to R. Though many people have them installed for other reasons.