Importing Excel files into R, xlsx or xls

by

For a solution that is free of fiddly external dependencies*, there is now readxl:

The readxl package makes it easy to get data out of Excel and into R.
Compared to many of the existing packages (e.g. gdata, xlsx,
xlsReadWrite) 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 supports both the legacy .xls format and the modern xml-based
.xlsx format. .xls support is made possible the with libxls C library,
which abstracts away many of the complexities of the underlying binary
format. To parse .xlsx, we use the RapidXML C++ library.

It can be installed like so:

install.packages("readxl") # CRAN version

or

devtools::install_github("hadley/readxl") # development version

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")

* not strictly true, it requires the Rcpp package, which in turn requires Rtools (for Windows) or Xcode (for OSX), which are dependencies external to R. But they don’t require any fiddling with paths, etc., so that’s an advantage over Java and Perl dependencies.

Update There is now the rexcel package. This promises to get Excel formatting, functions and many other kinds of information from the Excel file and into R.

More Related Contents:

  1. Easy way to export multiple data.frame to multiple Excel worksheets
  2. Handling java.lang.OutOfMemoryError when writing to Excel from R
  3. export data frames to Excel via xlsx with conditional formatting
  4. Importing a big xlsx file into R?
  5. How to replace a value by another within a variable in R?
  6. Quickly reading very large tables as dataframes
  7. Calculating moving average
  8. Relative frequencies / proportions with dplyr
  9. How to add texture to fill colors in ggplot2
  10. Cleaning up factor levels (collapsing multiple levels/labels)
  11. Split a large dataframe into a list of data frames based on common value in column
  12. ggplot, facet, piechart: placing text in the middle of pie chart slices
  13. How to make execution pause, sleep, wait for X seconds in R?
  14. Convert seconds to days: hours:minutes:seconds
  15. Concatenate strings and expressions in a plot’s title
  16. R gotcha: logical-and operator for combining conditions is & not &&
  17. Range standardization (0 to 1) in R [duplicate]
  18. Create a ranking variable with dplyr?
  19. Find value corresponding to maximum in other column [duplicate]
  20. Add error bars to show standard deviation on a plot in R
  21. R split numeric vector at position
  22. How to add different lines for facets
  23. Join two data frames in R based on closest timestamp
  24. Update/Replace Values in Dataframe with Tidyverse Join
  25. Set number of columns (or rows) in a facetted plot
  26. photo alignment with graph in r
  27. Handling errors before warnings in tryCatch
  28. Calculating peaks in histograms or density functions
  29. Return a matrix with `ifelse`
  30. Milliseconds puzzle when calling strptime in R

Leave a Comment