Dynamically converting a list of Excel files to csv files in R

Question

I currently have a folder containing all Excel (.xlsx) files, and using R I would like to automatically convert all of these files to CSV files using the "openxlsx" package (or some variation). I currently have the following code to convert one of the files and place it in the same folder:convert("team_order\\team_1.xlsx", "team_order\\team_1.csv")

I would like to automate the process so it does it to all the files in the folder, and also removes the current xlsx files, so only the csv files remain. Thanks!

Answer 1

You can try this using rio, since it seems like that's what you're already using:

library("rio")
xls <- dir(pattern = "xlsx")
created <- mapply(convert, xls, gsub("xlsx", "csv", xls))
unlink(xls) # delete xlsx files

Answer 2

library(readxl)

# Create a vector of Excel files to read
files.to.read = list.files(pattern="xlsx")

# Read each file and write it to csv
lapply(files.to.read, function(f) {
  df = read_excel(f, sheet=1)
  write.csv(df, gsub("xlsx", "csv", f), row.names=FALSE)
})

You can remove the files with the command below. However, this is dangerous to run automatically right after the previous code. If the previous code fails for some reason, the code below will still delete your Excel files.

lapply(files.to.read, file.remove)

You could wrap it in a try/catch block to be safe.