R loading multiple excel files and merge

Question

I have many excel files that I need to load and merge into a single data frame.

The script below works! However, before merging all files I want to paste each file name in a new column.

library(gdata)
library(tools)
filelist <- list.files(pattern = "*\\.xlsx$")
files = lapply(filelist, read.xls, header=TRUE)

new = Reduce(function(...) merge(..., all=T), files)

So I added this:

files$source <- file_path_sans_ext(filelist)

However, this didn't work.

My desired output is:

Col1 Col2 Col3 Source(this column doesnt exist in excel)
abc  1    2    Filename1
def  3    4    Filename2

How can I achieve this?

Answer 1

Try

library(tools)
source <- file_path_sans_ext(filelist)
files1 <- Map(cbind, files, Source=source)
files1
#[[1]]
#  Col1      Col2   Source
#1    A 0.5365853 Filname1
#2    A 0.4196231 Filname1

#[[2]]
# Col1     Col2   Source
#1    A 0.847460 Filname2
#2    C 0.266022 Filname2

#[[3]]
# Col1       Col2   Source
#1    C -0.4664951 Filname3
#2    C -0.8483700 Filname3

data

set.seed(24)
files <- lapply(1:3, function(i) data.frame(Col1=sample(LETTERS[1:3], 2, 
         replace=TRUE), Col2=rnorm(2)))
 filelist <- paste0('Filname', 1:3, '.txt')