Reading .csv files, converting them and saving them as .xlsx using R

Question

I have a folder full of .csv files that have to be slightly changed and then saved as an xlsx document.

Herefore, I have created a Loop to do this:

library(xlsx)
docs <- Sys.glob( "*.csv" )

for( i in docs )
{
  df <- read.csv(i)
  df2 <- select(df, X, Y)
  df3 <- mutate(df3, Z = (X - Y) / 3600)
  write.xlsx(df3, paste( "C:/users/Desktop/Files/", i), row.names = FALSE)
}

However, when I execute this for loop, the following error message pops up:

Error in createWorkbook(type = ext) : Unknown format csv

Did I forget anything? I would be very grateful, if you could help me, as I have no idea what else to change...

Answer 1

The package rio removes most of the headache with xlsx files. It can also be used to read in files:

docs <- Sys.glob("*.csv")

for(i in docs) {
  df <- rio::import(i)
  df2 <- select(df, X, Y)
  df3 <- mutate(df3, Z = (X - Y) / 3600)
  rio::export(df3, paste0("C:/users/Desktop/Files/", i, ".xlsx"))
}

This should work on the import/export part. What I'm not so sure about is your Sys.glob, since I've never used that before. I find list.files has a really easy and powerful syntax...

Update

If you want to get rid of the .csv file extension, you can use this instead:

for(i in docs) {
  df <- rio::import(i)
  df2 <- select(df, X, Y)
  df3 <- mutate(df3, Z = (X - Y) / 3600)
  fname <- gsub(".csv$", "", i)
  rio::export(df3, paste0("C:/users/Desktop/Files/", fname, ".xlsx"))
}