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
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> -----Original Message----- > From: r-help-bounces at r-project.org [mailto:r-help-bounces at r- > project.org] On Behalf Of Knut Krueger > Sent: Monday, November 25, 2013 10:11 AM > To: r-help at stat.math.ethz.ch > Subject: [R] XLConnect readWorksheet comma decimal sign > > 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 > > ______________________________________________ > 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.
I can't tell since you didn't post any code, so forgive me if you've
tried this. XLConnect has a colTypes parameter so you could try
specifying the relevant columns to be read in as character, set
forceConversion = TRUE, and then use as.numeric. Something like this:
as.numeric(sub(",", ".", Input, fixed = TRUE)) from
http://stackoverflow.com/questions/15236440/as-numeric-with-comma-decimal-separators.
That will change the commas to dots and should eliminate the
conversion to NA problem you were having.
Hi, XLConnect can very well deal with missing values. By default, only blank cells (cells not containing any values) will be treated as missing values. Cells containing the text "NA" are not automatically treated as missing values as "NA" is a valid non-missing text string. If you want to treat the text "NA" as missing value identifier, you can use the method setMissingValue. Generally, XLConnect determines the column type based on the cell types of the containing cells (yes, Excel cells are typed!). Therefore, if there are cells with text, XLConnect will decide to read that column in as text (treating missing value identifiers accordingly, as described above). You may also use the arguments colTypes and forceConversion for further controlling how cell values should be interpreted. Please see the reference manual for more detailed information. Regards, Martin> no change when setting it to dot. I seems that XLConnect is not able to > deal with NA in Excel, means its string instead an number after importing.