[R] XLConnect readWorksheet comma decimal sign

[David Winsemius]

On Nov 29, 2013, at 10:30 AM, Knut Krueger wrote:

> Am 29.11.2013 18:31, schrieb David Winsemius:
>> On Nov 29, 2013, at 6:18 AM, Knut Krueger wrote:
>> 
>>> Am 25.11.2013 13:06, schrieb Knut Krueger:
>>>>> how can I read exel files where the decimal sign is comma instead dot.
>>>>> I get the data as ascii and when converting "3,5" with as.numeric the  3,5 will be converted to NA
>>> I think here is a major bug because no warning is genereated.
>> You were already advised how to change the defaults for R's input functions' behaviors with respect to decimal separators. (I would add that you should also become familiar with the 'colClasses' argument.)
> ? I was adviced:
>> ither change comma to dot in Excel (but sometimes Excel is rather reluctant to accept such changes).

Well, I suppose you were, but I was referring to the advice to ' read data with option:  dec="," '. The reason I advised to also look at colClasses was to provide a mechanism for asserting that certain columns were "numeric". With a combination of dec="," and colCalsses="numeric" you would get coercion to 'numeric' after the decimal-punkts (as ",") were changed to decimal-points (as ".").

> Thats impossible, we are used to hit the comma

I don't know what that means.

>> 
>> Or change commaa to dot in R which probably can be easily done by gsub command
> Thats also impossible because the data are lost after teh import

Until you show a reproducible example, we will not be able to offer further advice:

>> 
>> Or read data with option dec=",". I do not know XLConnect but in read.table it is optional parameter and maybe it is also readWorksheet.

I was not making any advice contingent on XLConnect (other than to read it's help page to answer your question about how it was importing from xls files. My memory was that it used perl scripts but looking at its DESCRIPTION file I see that it is Java based.)


> Read data has no problem
>> does anybody know whether Rcmdr and XLCOnnectare using the same or different ways to import Excel?
>> Rcmdr: same ; XLConnect:different
>> 
>> You should read the help pages for XLConnect.
> http://cran.r-project.org/web/packages/XLConnect/XLConnect.pdf
> sorry I did not found anything.
>> 
>>> We found also that RCmdr is setting comma separated values to na if there is an NA in the column.
>>> 
>>> Maybe it more useful to discuss this in the devel group?  (set the follow up to DEVEL)
>> No. This is more a sign of your lack of experience with R. There are wiki-pages with advice about various ways to do Excel input. (You surely cannot think you are the first to experience this. This has been a difficulty for 15 years, and more with Excel than with R.)
> 
> Once again I have no problems to get the data inside R but I am looking for a solution to get an error message instead of setting 0,25 to na and only i a few column not in the hole sheet. That's a behaviour what I am expecting from MS not from R

You should post the code you used and the output from head(dfrm) where dfrm is the name of the object you created with whatever code you have never shown.
 
> But I got definitely wrong inputs with lost data with loadWorkbook.

If you are asking about XLConnect then correspond with the package authors.


> But it is not reproducable. doing the same  with a fresh environment,not after working 5 hours with R,  the excel sheet is imported without error. I am no a newbie in R and i am using it nearly 10 years. Them same problem occoured with RCmdr in the university by a phd Student. I can not reproduce the problem with the same script and the same file. Maybe you can agree that this is strange. Its a kind of "never be sure to get the correct data" and that happened to me the first time with R.
> 
> Knut
> 
> ______________________________________________
> 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.

David Winsemius
Alameda, CA, USA