How to read a multiple-sheet Excel file into R?

In my daily job, I have to deal with many Excel 2003 files. These files often have multiple sheets, as a sheet in Excel can have at most 65536 rows. I find Hans-Peter Suter’s xlsReadWrite package very useful here.

As my data often contain columns of date-time information, I use the following values for the read.xls() function:

dateTime = 'isodatetime',
stringsAsFactors = FALSE

The date-time column will be returned as a vector of character strings, which can be converted into POSIXct using the function as.POSIXct().

For multiple sheets Excel files, I suggest the column classes to be as simple as possible—for example, use characters instead of factors. Changing of column classes should be left after sheets are read-in and combined. Below is an example code snippet for reading multiple-sheet parameters:

library(xlsReadWrite)
sheet1 <- read.xls(file='foo.xls', colNames = TRUE, sheet = 1, dateTime = 'isodatetime', stringsAsFactors = FALSE)
cnames <- colnames(sheet1)
sheets <- list(sheet1)
for(i in 2:4){
sheets[[i]] <- read.xls(file='foo.xls', colNames = cnames, sheet = i, dateTime = "isodatetime", stringsAsFactors = FALSE)
}
combineddata <- c()
for(i in 1:length(sheets)){
combineddata <- rbind(combineddata, sheets[[i]])
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: