"Out of Memory Error (Java)" when using R and XLConnect package
ExcelRMemoryExcel Problem Overview
I tried to load a ~30MB excel spreadsheet into R using the XLConnect package.
This is what I wrote:
wb <- loadWorkbook("largespreadsheet.xlsx")
And after about 15 seconds, I got the following error:
> Error: OutOfMemoryError (Java): GC overhead limit exceeded.
Is this a limitation of the XLConnect package or is there a way to tweak my memory settings to allow for larger files?
I appreciate any solutions/tips/advice.
Excel Solutions
Solution 1 - Excel
Follow the advice from their website:
options(java.parameters = "-Xmx1024m")
library(XLConnect)
Solution 2 - Excel
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 - Excel
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 - Excel
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 - Excel
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.
Solution 6 - Excel
Whenever you are using a library that relies on rJava (such as RWeka in my case), you are bound to hit the default heap space (512 MB) some day. Now, when you are using Java, we all know the JVM argument to use (-Xmx2048m if you want 2 gigabytes of RAM). Here it's just a matter of how to specify it in the R environnement.
options(java.parameters = "-Xmx2048m")
library(rJava)
Solution 7 - Excel
As suggested in this here, make sure to run the option function in the first line in your code. In my case, it worked only when I restarted the R session and run it in the first line.
options(java.parameters = "-Xmx4g" )
library(XLConnect)