[R] XLConnect readWorksheet comma decimal sign

Knut Krueger rh at knut-krueger.de
Fri Nov 29 19:30:36 CET 2013

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).
Thats impossible, we are used to hit the comma
> 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
> 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.
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.
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
But I got definitely wrong inputs with lost data with loadWorkbook. 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.


More information about the R-help mailing list