This how-to will help you to take data from multiple MS Excel files into one big CSV file using packages and a simple program in R.
- First you must have installed R (RStudio is optional).
- Once installed, run R and open a new console.
- In the console, write the following to install and load the
readxl
package:
> install.package("readxl") # install the package
> library(readxl) # load the package
- Make sure to set the working directory to the folder where your Excel files are allocated. Use
setwd()
function in the console to do this:
setwd("C:/your_path_goes_here/xlsx_folder")
To check if the previous step is correct, run
getwd()
in the console.Now run the following subroutine:
# list of the names of the excel files in the working directoy
lst = list.files()
# create new data frame
df = data.frame()
# iterate over the names in the lists
for(table in lst){
dataFromExcel <- read_excel(table)
df <- rbind(df,dataFromExcel)
}
write.csv(df, "data.csv")
This program will fetch the
.xlsx
files by their names, read their data and write it overdataFromExcel
variable (you can read the read_excel() documentation to give specific information on how to read the file). Then therbind()
function will merge the rows from both data frames. This will loop over all the Excel files in the folder (Note: try to have ONLY Excel files in your folder).Finally, the
write.csv()
function will create a .csv file from the data we just uploaded to our main data framedf
with the name “data.csv” in the current working directory.
Don't forget to give a ❤️ if you found this useful :)
Top comments (0)