CODEWITHSUNDEEP
rexcelfile-conversionopenxlsx
wernor
wernor

Reputation: 421

How to convert xls to xlsx using R

I want to convert a bunch of xls files to xlsx in R so I can use the openxlsx package to overwrite in specific cells.

Up until now, I've been changing the format manually by opening each xls file and then saving them as .xlsx. As you can imagine this is really tiresome and it is kind of manageable now that I have less than 10 files, but I am expecting to have a lot more of these files.

I was wondering if someone has found a way of doing this automatically. I am using R 4.2.1 so the RDCOMClient package is not an option (I have read that this could be done easily with that package but it is not available anymore)

Edit I am working with tables within an Excel sheet and I need to maintian the format hence the overwriting.

I am familiar with the openxlsx package and my main issue is getting the Workbook objects out of the xls files. That is why I want to convert them into xlsx files.

Upvotes: 2

Views: 2262

Answers (4)

Emmanuel Hamel
Emmanuel Hamel

Reputation: 2223

Here is a function that can be used to convert XLS files to XLSX :

library(RDCOMClient)
convert_XLS_File_To_XLSX <- function(path_XLS_File, path_XLSX_File)
{
  xlApp <- COMCreate("Excel.Application")
  xlApp[['Visible']] <- FALSE
  xlWbk <- xlApp$Workbooks()$Open(path_XLS_File)
  xlWbk$SaveAs(path_XLSX_File, 51)
  xlWbk$Close()
  xlApp$Quit()
}

Upvotes: 1

G. Grothendieck
G. Grothendieck

Reputation: 270248

Try ssconvert which comes with the free gnumeric spreadsheet. It may issue warnings but will likely work despite that.

Given that you would considering RDCOMClient we assume you are using Windows so use this code modifying the ssconvert<- line appropriately for the location of ssconvert on your system.

files <- c("a.xls", "b.xls", "c.xls")
ssconvert <- r"{"C:\Program Files (x86)\Gnumeric\1.12.17\bin\ssconvert.exe"}"
for(f in files) shell(paste(ssconvert, print(f), sub("xls$", "xlsx", f)))

Upvotes: 0

wernor
wernor

Reputation: 421

In case someone else is also interested, I really didn't figure out how to do this in R but I did found this https://www.extendoffice.com/documents/excel/1349-excel-batch-convert-xls-to-xlsx.html#a2 and I can keep the exact same format from the original file.

Upvotes: 1

gaut
gaut

Reputation: 5958

We can use

library(readxl)
library(writexl)

myxls <- list.files(path=".", pattern=".xls")
myxls <- myxls[!grepl("xlsx", myxls)]

for (i in 1:length(myxls)) {
  thisdata <- read_excel(myxls[i])
  newname <- gsub(".xls", ".xlsx", myxls[i])
  write_xlsx(thisdata, newname)
}

You will then be able to open each newly created xlsx file with openxlsx.

Edit: for format issues you might take a look at the rio package:

library("rio")
created <- mapply(convert, myxls, gsub("xlsx", "xls", myxls))

I haven't tested it myself, but it might be worth a try.

Upvotes: 1

Related Questions