JULIAN JULIAN - 1 year ago 29
R Question

Loop for Read and Merge multiple excel sheets in r

There are many posts about XLConnect and reading excel files in R with XLConnect like How to read multiple excel sheets in R programming?, including rbind function, but no one answers this question:

If i had multiple excel .xls files in a directory how can i use a loop for reading and merging
each one in order?

I have a directory so i do this:

setwd("C:/Users/usuario/Desktop")
library(rjava)
library(XLConnect)


That directory has 28 excel files named like this:

Bitacora_Metrocali_01_02_2014C
Bitacora_Metrocali_02_02_2014C
. ... ...
Bitacora_Metrocali_28_02_2014C


So i need to merge them using the function:
Merge(x,y,all=T)

So it can add new columns to the dataframe. The thig is, i need a dataframe that starts
merging the first wht the second,and then adding all the new sheets in order. All excel
files of interest are in sheet 1.

THX!

Answer Source

Does this work for you:

# This will give you a vector of the names of files in your current directory 
# (where I've assumed the directory contains only the files you want to read)
data.files = list.files()

# Read the first file
df = readWorksheetFromFile(file=data.files[1], sheet=1)

# Loop through the remaining files and merge them to the existing data frame
for (file in data.files[-1]) {
    newFile = readWorksheetFromFile(file=file, sheet=1)
    df = merge(df, newFile, all=TRUE)
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download