[R] output

[Marc Schwartz]

Hi Val,

Presuming that the Excel file that you are trying to read has a second worksheet, which is what the read.xls() command you are using is trying to do, the problem may be that XLSX file support has not been installed for the gdata package, which is what I presume you are using. You do not explicitly indicate that, so I am guessing here.

In case there is any misunderstanding, I do not have any support for reading Excel files in WriteXLS.

If you look at the help for ?read.xls in Greg's gdata package, there is a reference to using ?xlsFormats, which tests as to whether or not you have support for XLSX formats installed for that package. If that latter function only returns "XLS" and not both "XLS" and "XLSX", then you need to use ?installXLSXsupport to install additional Perl modules so that Greg's functions can read XLSX format files.

Alternatively, there are other Excel file I/O packages available on CRAN if you would prefer to consider other options as well. They will typically require Java being installed, rather than Perl, as I and Greg do.

Regards,

Marc


> On Jan 17, 2017, at 2:36 PM, Val <valkremk at gmail.com> wrote:
> 
> Hi Marc and all,
> 
> Last time you suggest me to use  WriteXLS  function to write more than
> 65,000  row in excel.  Creating the file worked fine.  Now I wanted to
> read it using the WriteXLS   function but have a problem,. The file
> has more than  one sheets.  Here is the script and the error message.
> 
> datx <- function(n,mean,sd) { mean+sd*scale(rnorm(n)) }
> dat <-data.frame(datx(110000,10,2))
> WriteXLS(dat, "test5.xlsx", row.names=FALSE)
> I created several sheets  by copying the first sheet
> 
> t1<- read.xls("Test6.xlsx",2, stringsAsFactors=FALSE)
> 
> I am getting an error message of
> Error in read.table(file = file, header = header, sep = sep, quote = quote,  :
>  no lines available in input
> 
> Thank you in advance
> 
> 
> On Tue, Dec 13, 2016 at 5:07 PM, Val <valkremk at gmail.com> wrote:
>> Marc,
>> Thank you so much! That was helpful comment.
>> 
>> 
>> On Mon, Dec 12, 2016 at 10:09 PM, Marc Schwartz <marc_schwartz at me.com> wrote:
>>> 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.
>>>