R Examples of reading an Excel file into a data.frame using XLConnect, gdata and RODBC packages.

R-IO-Excel.R

# Examples of reading Excel files into R data.frame described here:
# http://earlglynn.github.io/R/input-output/Excel/index.html

#setwd("")   # Set working directory if necessary

# Examples
# 1.  gdata package:  read.xls
# 2.  RODBC package:  sqlFetch or sqlQuery

# efg, 2014-02-07

############################################################
### gdata package using PERL:  read.xls
### ActivePerl Community Edition on Windows can be used,
### http://www.activestate.com/activeperl
### Tested with 32 and 64 bit R with 64-bit ActivePerl

library(gdata)

d1 <- read.xls("Sample.xls", sheet=1)    # .xls or .xlsx
dim(d1)
str(d1)

d1a <- read.xls("Sample.xlsx", sheet=1, as.is=TRUE)
str(d1a)


############################################################
### RODBC package:  sqlFetch or sqlQuery

library(RODBC)

# "odbcConnectExcel is only usable with 32-bit Windows" i.e. R32

channel <- odbcConnectExcel("Sample.xls")       # Excel 2003 and earlier
#channel <- odbcConnectExcel2007("Sample.xls")  # Excel 2007 and later
sqlTables(channel)

d2 <- sqlFetch(channel, "Sample")  # Worksheet name
str(d2)

d2a <- sqlQuery(channel, paste("SELECT Code,Amount,Start,Age,Grade",
                               "FROM [Sample$]",
                               "WHERE Grade = 'B'"),
                as.is=TRUE)
dim(d2a)
str(d2a)

close(channel)

# See additional RODBC examples from Madelaine -- and RMySQL too
# http://research.stowers-institute.org/mcm/rmysql.html
                                              
############################################################
# 64-bit R.

############################################################
### xlsx package using Java:  read.xlsx  read.xlsx2
# If using 64-bit Java, must use 64-bit R.

library(xlsx)
d3 <- read.xlsx("Sample.xls", sheetIndex=1)    # .xls or .xlsx
dim(d3)
str(d3)

d3a <- read.xlsx("Sample.xlsx", sheetIndex=1,
         stringsAsFactors=FALSE)
str(d3a)

############################################################
### XLConnect package using Java:  readWorksheetFromFile
# If using 64-bit Java, must use 64-bit R.

# Avoid Java out-of-memory error for large Excel files.
# See p. 16, http://cran.r-project.org/web/packages/XLConnect/vignettes/XLConnect.pdf
options (java.parameters = "-Xmx1024m")

library(XLConnect)
d4 <- readWorksheetFromFile("Sample.xlsx", sheet=1)    # .xls or .xlsx
dim(d4)

str(d4)
d4