Skip to content

XLConnect readWorksheet comma decimal sign

8 messages · PIKAL Petr, David Winsemius, Knut Krueger +1 more

Knut Krueger
# 
Hi to all,
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

Kind Regards Knut
PIKAL Petr
# 
Hi

Either change comma to dot in Excel (but sometimes Excel is rather reluctant to accept such changes).

Or change commaa to dot in R which probably can be easily done by gsub command

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.

Regards
Petr
Knut Krueger
# 
Am 25.11.2013 11:29, schrieb PIKAL Petr:
read table is not able to read xls and xlsx files

I found the reason there are NA inside the column but
   gdata  read.xls  is doing the job fine, but it needs the perl 
interpreter, nor problem but somthing to install addtionally
   also Rcmdr has also no problems with NA in a decimal data column
   readWorksheet does the job also when no NA is in the column and with 
NA readWorksheet is interpreting the NA like any other text.

Knut
4 days later
Knut Krueger
# 
Am 25.11.2013 13:06, schrieb Knut Krueger:
I think here is a major bug because no warning is genereated.
It is impossible to change  excel to dot decimal separator.
First reason it is unusual  in our (and I think also  other countries).
Lot of computers in the university are using decimal separator mostly 
without R. The user would be  unable (or simply do not want) to use it 
furthermore.

A question
does anybody know whether Rcmdr and XLConnect are using the same or 
different ways to import Excel?
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)

Knut
David Winsemius
#
On Nov 29, 2013, at 6:18 AM, Knut Krueger wrote:

            

      
      
      
    

        
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.)

            

      
      
      
    

        
Rcmdr: same ; XLConnect:different

You should read the help pages for XLConnect.

            

      
      
      
    

        
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.)

            

      
      
      
    

        

      
      
    

        
David Winsemius
Alameda, CA, USA

  
  


  


      

    

    

      
      

        


  

        

      Knut Krueger
    

    

      
      #
    

  


  

      
Am 29.11.2013 18:31, schrieb David Winsemius:

            

      
      
      
    

        
? I was adviced:

            

      
      
      
    

        
Thats impossible, we are used to hit the comma

            

      
      
      
    

        
Thats also impossible because the data are lost after teh import

            

      
      
      
    

        
Read data has no problem

            

      
      
      
    

        
http://cran.r-project.org/web/packages/XLConnect/XLConnect.pdf
sorry I did not found anything.

            

      
      
      
    

        
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

  
  


  


      

    

    

      
      

        


  

        

      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:

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      David Winsemius
    

    

      
      #
    

  


  

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

        

            

      
      
      
    

        
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 ".").

            

      
      
      
    

        
I don't know what that means.

            

      
      
      
    

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

            

      
      
      
    

        
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.)

            

      
      
      
    

        
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.

            

      
      
      
    

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

            

      
      
      
    

        

      
      
    

        
David Winsemius
Alameda, CA, USA