The Problem

I have been recently conducting an evaluation study in the field of social work with elderly people. With the purpose to provide advice to elderly peoply regarding age-related questions about housing, home care etc, 10 offices for senior citizens were founded. Each of the offices is requested to document its activities (number of persons, events etc.) on a monthly basis. The data need to be entered in a prestructured Excel table. Since the offices started working about 2.5 years ago, I needed to handle 300 Excel sheets (30 months * 10 offices).

In a first step, I decided to create one Excel file for every office each containing 30 sheets (one sheet per month). While the files are named after the offices (abbreviated with 4 characters), the sheets are named after the following pattern: YYYY.MM (year followed by month).

The Solution

Since I did not find a solution for my problem with the packages I usually use to import Excel files into R ( xlsx , readxl ), I searched the internet for help. Fortunatelly, I found the paper “How to import and merge many Excel files; each with multiple sheets of data for statistical analysis.” by Jon Starkweather. The paper is really worth reading and gives a very comprehensive description on the subject matter. The following code snippets stem from Starkweather's paper.

In a first step, we have to load the following packages:

library(rJava) library(XLConnect, pos = 4)

In a second step, we define the file type, we want to import (.xls), save the sheet names of the Excel files into a new vector called sheet.names (since the sheet names in each of the files are identical, we may extract them from any of the 10 files) and create another vector ( e.names ) containing the names for the variables we want to import (in this case 28).

file.names <- list.files(pattern='*.xls') sheet.names <- getSheets(loadWorkbook('Name.xls')) e.names <- paste0(rep('v', 28), c(1:28))

In a thirt step, we create a data frame with 28 variables, named

v1, v2, v3, v4, v5, v6, v7, v8, v9, v10, v11, v12, v13, v14, v15, v16, v17, v18, v19, v20, v21, v22, v23, v24, v25, v26, v27, v28

and one row containing NAs only.

data.1 <- data.frame(matrix(rep(NA,length(e.names)), ncol = length(e.names))) names(data.1) <- e.names

Finally, we use 2 for-loops to import all the files and sheets and bind them to a data frame we can use for analysis.

for (i in 1:length(file.names)) { wb <- loadWorkbook(file.names[i]) for (j in 1:length(sheet.names)) { ss <- readWorksheet(wb, sheet.names[j], startCol = 2, header = TRUE) condition <- rep(sheet.names[j], nrow(ss)) sub.id <- rep(file.names[i], nrow(ss)) s.frame <- seq(1:nrow(ss)) df.1 <- data.frame(sub.id, condition, s.frame, ss) names(df.1) <- e.names data.1 <- rbind(data.1, df.1) rm(ss, condition, s.frame, sub.id, df.1) } rm(wb) }

In the mentioned paper, Jon Starkweather elaborates in detail on what each line of each for-loop is doing.