HOW TO: Multiple .xlsx files to one .csv using R/RStudio

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 over dataFromExcel variable (you can read the read_excel() documentation to give specific information on how to read the file). Then the rbind() 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 frame df with the name “data.csv” in the current working directory.

Don't forget to give a ❤️ if you found this useful :)