Merging Excel Workbooks

This document will show you how to take a group of Excel workbooks with multiple sheets and merge them into one workbook. The sheets must have the same names in each workbook with the same formatting (identical column names, etc.).

First you need to install the NCRAD package from GitHub. Run the following code in R.

if(!require(devtools)){
  install.packages("devtools")
  library(devtools)
}
install_github("IU-MMGE/NCRAD")

Next, get the file locations of the workbooks you would like to merge. Suppose you had a group of Excel workbooks in a folder located here: C:/My_Stuff. Get the list of files (assuming there are no other files except the workbooks you want to merge).

excel_wbs <- list.files("C:/My_Stuff")
excel_wbs

##  [1] "ARTFL-LEFFTDS Monthly QC Report_March_Site 10J.XLSX"
##  [2] "ARTFL-LEFFTDS Monthly QC Report_March_Site 10R.XLSX"
##  [3] "ARTFL-LEFFTDS Monthly QC Report_March_Site 13.xlsx" 
##  [4] "ARTFL-LEFFTDS Monthly QC Report_March_Site 17.xlsx" 
##  [5] "ARTFL-LEFFTDS Monthly QC Report_March_Site 200.xlsx"
##  [6] "ARTFL-LEFFTDS Monthly QC Report_March_Site 202.xlsx"
##  [7] "ARTFL-LEFFTDS Monthly QC Report_March_Site 27.xlsx" 
##  [8] "ARTFL-LEFFTDS Monthly QC Report_March_Site 35.xlsx" 
##  [9] "ARTFL-LEFFTDS Monthly QC Report_March_Site 4.xlsx"  
## [10] "ARTFL-LEFFTDS Monthly QC Report_March_Site 9.xlsx"  
## [11] "ARTFL-LEFFTDS Monthly QC Reports_March_Site 21.xlsx"

Now we can use the mergeExcel() function from the NCRAD package to write an Exel workbook with the merged content.

library(NCRAD)
excel_locations <- paste0("C:/My_Stuff/", excel_wbs)
mergeExcel(files = excel_locations, output_location = "C:/My_Stuff",
           output_name = "Merged_Monthly_QC")

We can see that the function took the workbooks and wrote a file named “Merged_Monthly_QC.xlsx” in “C:/My_Stuff”

list.files("C:/My_Stuff")

##  [1] "ARTFL-LEFFTDS Monthly QC Report_March_Site 10J.XLSX"
##  [2] "ARTFL-LEFFTDS Monthly QC Report_March_Site 10R.XLSX"
##  [3] "ARTFL-LEFFTDS Monthly QC Report_March_Site 13.xlsx" 
##  [4] "ARTFL-LEFFTDS Monthly QC Report_March_Site 17.xlsx" 
##  [5] "ARTFL-LEFFTDS Monthly QC Report_March_Site 200.xlsx"
##  [6] "ARTFL-LEFFTDS Monthly QC Report_March_Site 202.xlsx"
##  [7] "ARTFL-LEFFTDS Monthly QC Report_March_Site 27.xlsx" 
##  [8] "ARTFL-LEFFTDS Monthly QC Report_March_Site 35.xlsx" 
##  [9] "ARTFL-LEFFTDS Monthly QC Report_March_Site 4.xlsx"  
## [10] "ARTFL-LEFFTDS Monthly QC Report_March_Site 9.xlsx"  
## [11] "ARTFL-LEFFTDS Monthly QC Reports_March_Site 21.xlsx"
## [12] "Merged_Monthly_QC.xlsx"