MemoryError in R while read.xlsx

Question

I am using the following R code (that utilizes the Java parameter to increase memory as well):

library(xlsx)
options(java.parameters = "-Xmx1g")
library(XLConnect)
NiVe <- read.xlsx("version1.xlsx",1)

The version1.xlsx file is 13 MB in size. I get the following error:

Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl,  : 
  java.lang.OutOfMemoryError: Java heap space

Can someone help?

Frosting

It's because of this issue that I gave up on the xlsx package and have moved to readxl, which is faster – Tartrazine

@Tartrazine I recently encountered the same problem with the xlsx package. I normally use the readxl package for reading Excel files, but had to use the xlsx package as readxl can't open password-protected spreadsheets. – Cacuminal

Answer 1

Try to increase java Heap size(sufficient), by using:

options(java.parameters = "-Xmx1000m")

If you are using 32 bit R make sure you are also using 32 bit Java and if you re using 64 bit R make sure you are also using 64 bit Java. If you get a Java heap space message indicating that it is out of memory see this post.

this thread may help you, to use Garbage Collection.

Reference:
exchanging_data_between_r_and_ms_windows_apps_excel_etc
stats-rosuda-devel_001621

Lozengy

If you are using 32 bit R make sure you are also using 32 bit Java and if you re using 64 bit R make sure you are also using 64 bit Java. How does one check this? – Gusher

Answer 2

You should make sure that you are setting the Java parameters before any JVM is initialized, i.e. before either the XLConnect or xlsx packages are loaded. Note that in your case you are using the read.xlsx function which is not an XLConnect function and therefore the XLConnect package is not required. The XLConnect alternative is the function readWorksheetFromFile.

Usia

Any idea why it takes so much memory to open an Excel file? I had to set my limit to 2 GB to open a 23 MB file. – Entranceway

I'm with @rrs, just had Java freak out after being given 25GB memory to write 15MB file.... – Tartrazine

Answer 3

Try this :- May be this can work. It worked for me

1) when downloading the xlsx file, use this download.file(fileURL, destfile="./whatever", mode="wb")

2) Switched to regular R, not R Studio,

Sagesagebrush