[R] problem loading large xlsx file into r

[Jim Holtman]

did you try converting xlsx to xls (if possible).  the xlsx format puts a large demand on both memory and cpu resources.

Sent from my iPad

On Jul 22, 2013, at 6:09, S N V Krishna <krishna at primps.com.sg> wrote:

> Thanks Jim, I tried XLConnect but faced with same error.
> 
>> options(java.parameters = '-Xmx5g')
> 
>> library(XLConnect)
> Loading required package: rJava
> XLConnect 0.2-5 by Mirai Solutions GmbH
> http://www.mirai-solutions.com ,
> http://miraisolutions.wordpress.com
> 
>> cftc = readWorksheetFromFile("d:\\Krishna\\Research\\CFTC_COT\\cftcdata.xlsx", sheet = 'Sheet1')
> Error: OutOfMemoryError (Java): Java heap space
> 
> What is the maximum file size to load into R? is there a better way to load large excel files to R?
> 
> Many thanks for the help.
> 
> Regards, 
> 
> Krishna
> 
> -----Original Message-----
> From: Jim Holtman [mailto:jholtman at gmail.com] 
> Sent: Monday, July 22, 2013 5:10 PM
> To: S N V Krishna
> Cc: r-help at r-project.org
> Subject: Re: [R] problem loading large xlsx file into r
> 
> try the "XLConnect" package and if possible change the "xlsx" to "xls" format for better performance.
> 
> Sent from my iPad
> 
> On Jul 22, 2013, at 1:24, S N V Krishna <krishna at primps.com.sg> wrote:
> 
>> Hi,
>> 
>> I am facing trouble when trying to read large xlsx file into R. please find the code and error below. The file I was trying to read has 36,500 rows X 188 col, ~ 37 MB size.
>> 
>>> options( java.parameters = "-Xmx4g" )
>> 
>>> library(xlsx)
>> Loading required package: xlsxjars
>> Loading required package: rJava
>> 
>>> cftc = read.xlsx("d:\\Krishna\\Research\\CFTC_COT\\cftcdata.xlsx", 1)
>> Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl,  :
>> 
>> 
>>> sessionInfo()
>> R version 3.0.1 (2013-05-16)
>> Platform: x86_64-w64-mingw32/x64 (64-bit)
>> 
>> locale:
>> [1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United 
>> States.1252 [3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C 
>> [5] LC_TIME=English_United States.1252
>> 
>> attached base packages:
>> [1] stats     graphics  grDevices utils     datasets  methods   base
>> 
>> other attached packages:
>> [1] xlsx_0.5.1     xlsxjars_0.5.0 rJava_0.9-5
>> 
>> Many thanks for the help and guidance.
>> 
>> Regards,
>> 
>> Krishna
>> 
>>   [[alternative HTML version deleted]]
>> 
>> ______________________________________________
>> R-help at r-project.org mailing list
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide 
>> http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.