[R] XLConnect readWorksheet comma decimal sign

[Jeff Newmiller]

Please understand that this is a contributed package, and definitive assistance can only be provided by the package author. Type 

maintainer("XLConnect")

for contact information, and read the package info at cran.r-project.org/web/packages/XLConnect/index.html. R-devel is unlikely to be a more appropriate forum either.

I avoid reading Excel files directly simply because I have lost interest in fighting these battles. When I need to read many Excel files I write a macro in Excel to export to CSV and work with sane data in R. Others seem to feel these libraries work well for them, but if they don't speak up then complaining at those who do speak up won't necessarily make them say anything.

You do need to understand that reading data in odd formats must often initially be done in character format so you can apply special format parsing functions to the data yourself. In the standard data input functions the colClasses parameter can be used to control this. However,  since I don't use XLConnect I cannot tell you whether that parameter applies to that library or not.
---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
--------------------------------------------------------------------------- 
Sent from my phone. Please excuse my brevity.

Knut Krueger <rh at knut-krueger.de> 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).
>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.
>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
>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.
>
>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.