Importing multiple Excel files with filenames in R

Question

I have about one hundred excel files which I need to import to R and merge. All excel-files have four columns and each one needs to be imported. The files look like one below:

1     127          122
1     87      
2     107     
1     136    k    
1     210

I also need to add filename as fifth column for each row. All excel files are in the same folder.

So far I have tried following:

library(xlsx)
setwd("c:/temp/")
filenames <- list.files(pattern=".xls")
do.call("rbind", lapply(filenames, function(x) read.xlsx(file=x, sheetIndex=1, colIndex=(1:4), header=FALSE, FILENAMEVAR=x)))

I get following error: Error in rbind(deparse.level, ...) : numbers of columns of arguments do not match

I have located the problem to empty cells in third and fourth columns as function works perfectly if I limit it only to first and second columns.

Do you have unequal number of columns in every file? http://r.789695.n4.nabble.com/Flexible-rbind-td3383857.html — Freak, Jan 16 '14 at 10:47
Yes but couldn't really figure it out. Should I use merge_all instead of rbind? — user3190577, Jan 16 '14 at 11:13

score 6 · Accepted Answer · edited Jan 16 '14 at 11:54

6

Figured it out myself. The key was to use rbind.fill instead of rbind.

library(plyr)
df.list <- lapply(filenames, function(x) read.xlsx(file=x, sheetIndex=1,
                  colIndex=1:4,as.data.frame=TRUE, header=FALSE, FILENAMEVAR=x))
final.df <- rbind.fill(df.list)

edited Jan 16 '14 at 11:54

flodel

87,577
21
185
223

answered Jan 16 '14 at 11:50

user3190577

123
1
9

Thank you for posting the answer after you figured it out on your own; I'm sure it could be useful to others, and not everyone does. Isn't plyr great? – prooffreader Jan 16 '14 at 12:02

Importing multiple Excel files with filenames in R

1 Answers1

Linked

Related