14

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?

trincot
  • 317,000
  • 35
  • 244
  • 286
  • It's because of this issue that I gave up on the `xlsx` package and have moved to `readxl`, which is [faster](http://stackoverflow.com/questions/6099243/read-an-excel-file-directly-from-a-r-script/31734198#31734198) – MichaelChirico Feb 25 '16 at 02:16
  • @MichaelChirico 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. – elarry Aug 29 '23 at 09:08

3 Answers3

19

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

Chandra Sekhar
  • 16,256
  • 10
  • 67
  • 90
  • 2
    `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? – coip Mar 01 '16 at 22:54
7

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.

Martin Studer
  • 2,213
  • 1
  • 18
  • 23
  • 2
    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. – rrs Oct 01 '14 at 20:02
  • I'm with @rrs, just had Java freak out after being given 25GB memory to write 15MB file.... – MichaelChirico Jul 30 '15 at 20:21
0

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,

jatin kashyap
  • 93
  • 8