"Out of memory error (Java)" when using R and XLConnect packets

Question

I tried loading an Excel 30 MB spreadsheet into R using the XLConnect package.

Here is what I wrote:

wb <- loadWorkbook("largespreadsheet.xlsx") 

And after about 15 seconds I got the following error:

Error: OutOfMemoryError (Java): GC upper limit exceeded.

Is this a limitation of the XLConnect package or is there a way to change my memory settings to allow the use of large files?

I appreciate any solutions / tips / advice.

Answer 1

Follow the tips on the website :

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

Answer 2

If you still have problems importing XLSX files, you can use this opiton. Anwser did not work with "Xmx1024m" and I changed to "-Xmx4g".

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

This link has been helpful.

Answer 3

Use read.xlsx() in the openxlsx package. It does not depend on rJava , therefore it has only memory limitations of R. I did not understand much depth for writing and formatting XLSX, but it has promising looking vignettes. For reading large spreadsheets, it works well.

The tip of the hat is up to Brad Horn. I just passed my comment as an answer because I also found this to be the best solution!

Answer 4

If someone encounters this error while reading not one huge, but a large number of files, I managed to solve this error by freeing the memory of the Java virtual machine using xlcFreeMemory() , thus:

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

Answer 5

This is similar to the case when you use the same R-session again and again without restarting R-Studio. Restarting R-Studio can help allocate a new pile of memory for the program. It worked for me right away.

Answer 6

As suggested here , make sure you run the option function on the first line of your code. In my case, it only worked when I restarted the R session and started it on the first line.

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

Answer 7

Whenever you use a library that relies on rJava (for example, RWeka in my case), you will be forced to hit by default a heap of space (512 MB). Now that you are using Java, we all know the JVM argument to use (-Xmx2048m if you want 2 gigabytes of RAM). Here we are only talking about how to specify it in R.

  options(java.parameters = "-Xmx2048m") library(rJava)