CODEWITHSUNDEEP
r
Darwin Chang
Darwin Chang

Reputation: 59

R: Saving Output as xlsx in for loop

Using (openxlsx) package to write xlsx files. I have a variable that is a vector of numbers

x <- 1:8

I then paste ".xlsx" to the end of each element of x to later create an xlsx file

new_x <- paste(x,".xlsx", sep = "")

I then write.xlsx using the ("openxlsx") package in a forloop to create new xlsx files

for (i in x) {
for (j in new_x) {
write.xlsx(i,j)
}}

When I open ("1.xlsx" - "8.xlsx"), all the files only have the number "8" on them. What I don't understand is why it doesn't have the number 1 for 1.xlsx - 7 for 7.xlsx, why does the 8th one overwrite everything else.

I even tried creating a new output for the dataframes as most others suggested

for (i in x) {
for (j in new_x) {
output[[i]] <- i    

write.xlsx(output[[i]],j)
}}

And it still comes up with the same problem. I don't understand what is going wrong.

Upvotes: 1

Views: 3272

Answers (1)

Kerry Jackson
Kerry Jackson

Reputation: 1871

The problem is that you are creating each Excel file multiple times because you have nested loops. Try just using a single loop, and referring to an element of new_x.

x <- 1:8
new_x <- paste(x,".xlsx", sep = "")
for (i in seq_along(x)) {
    write.xlsx(i,new_x[i])
}

if you want to read a number of .csv files and save them as xlsx files it is a similar approach, you still want to only have a single for loop such as:

# Define directory of where to look for csv files and where to save Excel files
csvDirectory <- "C:/Foo/Bar/"
ExcelDirectory <- paste0(Sys.getenv(c("USERPROFILE")),"\\Desktop")

# Find all the csv files of interest
csvFiles <- list.files(csvDirectory,"*.csv")

# Go through the list of files and for each one read it into R, and then save it as Excel
for (i in seq_along(csvFiles)) {
  csvFile <- read.csv(paste0(csvDirectory,"/",csvFiles[i]))
  write.xlsx(csvFile, paste0(ExcelDirectory,"/",gsub("\\.csv$","\\.xlsx",csvFiles[i])))
}

Upvotes: 1

Related Questions