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

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.

Answers


Follow the advice from their website:

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

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.


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!


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 !
}

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.


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)

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)

Need Your Help

What is the correct way to handle stale NSURL bookmarks?

cocoa nsurl security-scoped-bookmarks

When resolving an NSURL from a security scoped bookmark, if the user has renamed or moved that file or folder, the bookmark will be stale. Apple's document says this regarding staleness:

How do I create a ClaimsIdentity object for Asp.NET MVC 5?

authentication ravendb asp.net-mvc-5 claims-based-identity katana

I am working in a .NET MVC 5 application. I do not want to use Entity Framework. I want to authenticate to a RavenDB database. It looks to me that I want to replace the UserManager that comes with ...