I am trying to combine multiple excel files in the same folder that all have the same layout. I have successfully pulled all the data from A13:M28 and have been able to append the rows to one big excel. But this file is unusable for me because it does not contain the name field in cell B5.See Example Excel file Using the code below I've been able to export an excel sheet that looks like This This is almost what I need but I need the name cell from the original excel files to be appended as a column so that I know who the data is referencing as I scroll down the merged file. The solution I tried was getting the names with with another read.xlsx (lst2) then using Map(cbind but this Map line throws the error: Error in data.frame(..., check.names = FALSE) : arguments imply differing number of rows: 0, 15
My Code
path <- "Payroll Files"
filenames_list <- list.files(path= path, full.names=TRUE) #create list of all file names
merge_file_name <- "Payroll Files//merged_file.xlsx"
lst1 <- lapply(filenames_list, function(x) read.xlsx(x, rows = 13:28))
lst2 <- lapply(filenames_list, function(x) setNames(read.xlsx(x,,
rows = 5, cols = 2), 'Employee'))
Map(cbind, Employee = lst2, lst1)
df <- do.call(rbind.data.frame, lst1)
write.xlsx(df,merge_file_name)
