Opening Excel file and Saving as CSV in R

Question

I'm using the following code:

setwd("~/R/Test")
require(openxlsx)
file_list <- list.files(getwd())

for (file in file_list){
  file = read.xlsx(file)
  write.csv(file,file=file)
}

Where it opens each file in a directory, reads the excel file, and saves as a CSV. However, I'm trying to source the original file name, and save the CSV with the original file name. Is there a way to do this?

Thanks!

You're overwriting the variable file, which used to contain the original file name, in the line file = read.xlsx(file). Just avoid doing that. — Frank
– Frank, Commented Feb 19, 2015 at 19:50
Please describe what you want is different than what you are getting. That code appears to be doing what you say you want. — IRTFM
– IRTFM, Commented Feb 19, 2015 at 19:50
@Frank - so if I just remove the 'file=' portion, would it pull the original name and then save with that same? Because if I just remove that portion I'm not seeing what you're suggesting. — GregdeLima
– GregdeLima, Commented Feb 19, 2015 at 20:00

blakeoft · Accepted Answer · 2015-02-19 19:59:16Z

1

As pointed out in the comments, you're overwriting the variable file. I also recommend changing the extension of the file. Try this as your for loop:

for (file in file_list) {
  file.xl <- read.xlsx(file)
  write.csv(file.xl, file = sub("xlsx$", "csv", file))
}

Note that you'll need to change the "xlsx$" to "xls$" depending on what the extensions are of the files in your directory.

answered Feb 19, 2015 at 19:59

blakeoft

2,4001 gold badge16 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

GregdeLima Over a year ago

This worked perfectly. Can you help me understand the file.xl portion? is that just generating a new variable?

blakeoft Over a year ago

@GregdeLima Yes, it's making a new variable. file will stay as the string that contains the spreadsheet's name, and file.xl will be the data frame that actually contains all of the info.

GregdeLima Over a year ago

Thank you! I hate when the answer is so simple and it just goes over my head.

blakeoft Over a year ago

@GregdeLima I also recommend looking into setting quote = F and row.names = F inside the write function, but you should check out the csv files to make sure you want that.

GregdeLima Over a year ago

- Can you elaborate on those? Looked into the help for each, but I'm not following.

|

Collectives™ on Stack Overflow

Opening Excel file and Saving as CSV in R

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related