Hi all,
I have a data frame with more than 100,000 rows.
datx <- function(n,mean,sd) { mean+sd*scale(rnorm(n)) }
dat <- datx(110000,10,2)
1)
WriteXLS(dat, "test4.xls", row.names=FALSE)
Error in WriteXLS(dat, "test4.xls", row.names = FALSE) :
One or more of the data frames named in 'x' exceeds 65,535 rows or 256 columns
I noticed that *.xls has row and column limitations.
How can I take the excess row to the next sheet?
2) I also tried to use xlsx and have a problem
write.xlsx(dat, "test3.xlsx",sheetName="sheet1", row.names=FALSE)
Error in .jnew("org/apache/poi/xssf/usermodel/XSSFWorkbook") :
java.lang.OutOfMemoryError: Java heap
space.jnew("org/apache/poi/xssf/usermodel/XSSFWorkbook")<S4 object of
class "jobjRef">
Any help ?
Thank you in advance
output
3 messages · Val, Jeff Newmiller, Marc Schwartz
1) I recommend against using xls for very large data sets. (Not that xlsx is really that much better.) If you really think this is necessary you will probably need to split the data frame and write each element of the resulting list. See ?split. 2) Google tells me (as it could have told you) that you could choose another package or increase your memory allocation to java. [1] If you encounter bugs in packages, don't forget to use the maintainer() function as mentioned in the Posting Guide. [1] http://stackoverflow.com/questions/19147884/importing-a-big-xlsx-file-into-r
Sent from my phone. Please excuse my brevity.
On December 12, 2016 4:51:24 PM PST, Val <valkremk at gmail.com> wrote:
>Hi all,
>
>I have a data frame with more than 100,000 rows.
>
>datx <- function(n,mean,sd) { mean+sd*scale(rnorm(n)) }
>dat <- datx(110000,10,2)
>
>1)
>WriteXLS(dat, "test4.xls", row.names=FALSE)
>Error in WriteXLS(dat, "test4.xls", row.names = FALSE) :
>One or more of the data frames named in 'x' exceeds 65,535 rows or 256
>columns
>
>I noticed that *.xls has row and column limitations.
>
>How can I take the excess row to the next sheet?
>
>2) I also tried to use xlsx and have a problem
>
>write.xlsx(dat, "test3.xlsx",sheetName="sheet1", row.names=FALSE)
>Error in .jnew("org/apache/poi/xssf/usermodel/XSSFWorkbook") :
> java.lang.OutOfMemoryError: Java heap
>space.jnew("org/apache/poi/xssf/usermodel/XSSFWorkbook")<S4 object of
>class "jobjRef">
>
>Any help ?
>Thank you in advance
>
>______________________________________________
>R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>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.
Hi, With the WriteXLS() function, from the package of the same name, if you specify '.xlsx' for the file name extension, the function will create an Excel 2007 compatible file, which can handle worksheets of up to 1,048,576 rows by 16,384 columns. Thus: WriteXLS(dat, "test4.xlsx", row.names = FALSE) That is all described in the help file for the function. Regards, Marc Schwartz
On Dec 12, 2016, at 6:51 PM, Val <valkremk at gmail.com> wrote:
Hi all,
I have a data frame with more than 100,000 rows.
datx <- function(n,mean,sd) { mean+sd*scale(rnorm(n)) }
dat <- datx(110000,10,2)
1)
WriteXLS(dat, "test4.xls", row.names=FALSE)
Error in WriteXLS(dat, "test4.xls", row.names = FALSE) :
One or more of the data frames named in 'x' exceeds 65,535 rows or 256 columns
I noticed that *.xls has row and column limitations.
How can I take the excess row to the next sheet?
2) I also tried to use xlsx and have a problem
write.xlsx(dat, "test3.xlsx",sheetName="sheet1", row.names=FALSE)
Error in .jnew("org/apache/poi/xssf/usermodel/XSSFWorkbook") :
java.lang.OutOfMemoryError: Java heap
space.jnew("org/apache/poi/xssf/usermodel/XSSFWorkbook")<S4 object of
class "jobjRef">
Any help ?
Thank you in advance
______________________________________________ R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see 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.