Handling java.lang.OutOfMemoryError when writing to Excel from R
This is a known issue:
http://code.google.com/p/rexcel/issues/detail?id=33
While unresolved, the issue page links to a solution by Gabor Grothendieck suggesting that the heap size should be increased by setting the java.parameters
option before the rJava
package is loaded. (rJava
is a dependency of xlsx
.)
options(java.parameters = "-Xmx1000m")
The value 1000
is the number of megabytes of RAM to allow for the Java heap; it can be replaced with any value you like. My experiments with this suggest that bigger values are better, and you can happily use your full RAM entitlement. For example, I got the best results using:
options(java.parameters = "-Xmx8000m")
on the machine with 8GB RAM.
A further improvement can be obtained by requesting a garbage collection in each iteration of the loop. As noted by @gjabel, R garbage collection can be performed using gc()
. We can define a Java garbage collection function that calls the Java System.gc()
method:
jgc <- function()
{
.jcall("java/lang/System", method = "gc")
}
Then the loop can be updated to:
for(i in seq_along(the_data))
{
gc()
jgc()
message("Creating sheet", i)
sheet <- createSheet(wb, sheetName = names(the_data)[i])
message("Adding data frame", i)
addDataFrame(the_data[[i]], sheet)
}
With both these code fixes, the code ran as far as i = 29
before throwing an error.
One technique that I tried unsuccessfully was to use write.xlsx2
to write the contents to file at each iteration. This was slower than the other code, and it fell over on the 10th iteration (but at least part of the contents were written to file).
for(i in seq_along(the_data))
{
message("Writing sheet", i)
write.xlsx2(
the_data[[i]],
"test.xlsx",
sheetName = names(the_data)[i],
append = i > 1
)
}
How to deal with Java Heap Space error while running rjava
You need to modify the java parameters with the following function at the start of your script.
options(java.parameters = "-Xmx8000m")
You may also need restart your environment first.
Related Topics
Collapsing Data Frame by Selecting One Row Per Group
Filter Data Frame Rows Based on Values in Vector
How to Get the Maximum Value by Group
Calculate Row-Wise Proportions
Add "Filename" Column to Table as Multiple Files Are Read and Bound
Combine Rows in Data Frame Containing Na to Make Complete Row
Automatically Delete Files/Folders
Code to Import Data from a Stack Overflow Query into R
How to Remove an Element from a List
How to Change the Formatting of Numbers on an Axis with Ggplot
Convert Column Classes in Data.Table
Perform a Semi-Join with Data.Table
Count Number of Zeros Per Row, and Remove Rows with More Than N Zeros
Growing a Data.Frame in a Memory-Efficient Manner
Extract Month and Year from Date in R
Convert Yyyymmdd String to Date Class in R
Function to Calculate Geospatial Distance Between Two Points (Lat,Long) Using R