"Out of Memory Error (Java)" when using R and XLConnect package

Solution 1:

Follow the advice from their website:

options(java.parameters = "-Xmx1024m")
library(XLConnect)

Solution 2:

If you still have problems with importing XLSX files you can use this opiton. Anwser with "Xmx1024m" didn't work and i changed to "-Xmx4g".

options(java.parameters = "-Xmx4g" )
library(XLConnect)

This link was useful.

Solution 3:

Use read.xlsx() in the openxlsx package. It has no dependency on rJava thus only has the memory limitations of R itself. I have not explored in much depth for writing and formatting XLSX but it has some promising looking vignettes. For reading large spreadsheets, it works well.

Hat tip to @Brad-Horn. I've just turned his comment as an answer because I also found this to be the best solution!

Solution 4:

In case someone encounters this error when reading not one huge but many files, I managed to solve this error by freeing Java Virtual Machine memory with xlcFreeMemory(), thus:

files <- list.files(path, pattern = "*.xlsx")
for (i in seq_along(files)) {
    wb <- loadWorkbook(...)
    ...
    rm(wb)
    xlcFreeMemory()  # <= free Java Virtual Machine memory !
}

Solution 5:

This appears to be the case, when u keep using the same R-session over and over again without restarting R-Studio. Restarting R-Studio can help to allocate a fresh memory-heap to the program. It worked for me right away.