Dear R users I use R (1.3.0) Windows version usually with connection to Excel sheets via RODBC Version: 0.8-2. Everything is OK until I use mixed data types (numeric and character) in one column. In that case I will get a particular column of data frame with NA's. Example vzorek lpas apas NA 51.240 20.26 NA 51.340 20.42 NA 49.770 22.05 RB 12 49.840 19.30 RB 12 49.880 19.32 But in actual Excel sheet instead of NA values were numbers 11 (numeric). I can modify elevens in Excel as let say RB 11 or something like that, but if there are more such columns and many different values it can be quite complicated. The values are either considered character, converted to factor and numeric values are changed to NA or numeric and character values are converted to NA.> is.factor(pigment[,1])[1] TRUE> is.numeric(pigment[,2])[1] TRUE> is.numeric(pigment[,3])[1] TRUE I tried parameter as.is but it did not help and I did not find any clue other then I(....) but I can not resolve how to use it in that case. Is there any possibility how to handle different data types in one Excel sheet column directly by using ODBC connection and keep all values in such column as they are when moving data from Excel to R? Thank you. Petr Pikal Precheza a.s. Nabr.Dr.E.Benese 24 tel: 00420 641 25 2257 +420 (0)724 008 364 petr.pikal at precheza.cz p.pik at volny.cz -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.- r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html Send "info", "help", or "[un]subscribe" (in the "body", not the subject !) To: r-help-request at stat.math.ethz.ch _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
On Tue, 14 Aug 2001, Petr Pikal wrote:> Dear R users > > I use R (1.3.0) Windows version usually with connection to Excel > sheets via RODBC Version: 0.8-2. Everything is OK until I use mixed > data types (numeric and character) in one column.You can't have different data types in one column of a data frame. You are not telling us what you are doing (at all: not a single command you used), but I suspect you need to know about as="matrix", which will import the data as a character matrix for you.> In that case I will get a particular column of data frame with NA's. > > Example > > vzorek lpas apas > NA 51.240 20.26 > NA 51.340 20.42 > NA 49.770 22.05 > RB 12 49.840 19.30 > RB 12 49.880 19.32 > > But in actual Excel sheet instead of NA values were numbers 11 > (numeric). I can modify elevens in Excel as let say RB 11 or > something like that, but if there are more such columns and many > different values it can be quite complicated. > > The values are either considered character, converted to factor and > numeric values are changed to NA or numeric and character values > are converted to NA. > > > is.factor(pigment[,1]) > [1] TRUE > > is.numeric(pigment[,2]) > [1] TRUE > > is.numeric(pigment[,3]) > [1] TRUE > > I tried parameter as.is but it did not help and I did not find any clue > other then I(....) but I can not resolve how to use it in that case. > > Is there any possibility how to handle different data types in one Excel > sheet column directly by using ODBC connection and keep all values > in such column as they are when moving data from Excel to R?Not as such. Bu you can get them all as characters strings.> > Thank you. > Petr Pikal > Precheza a.s. > Nabr.Dr.E.Benese 24 > tel: 00420 641 25 2257 > +420 (0)724 008 364 > petr.pikal at precheza.cz > p.pik at volny.cz > > -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.- > r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html > Send "info", "help", or "[un]subscribe" > (in the "body", not the subject !) To: r-help-request at stat.math.ethz.ch > _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._ >-- Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272860 (secr) Oxford OX1 3TG, UK Fax: +44 1865 272595 -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.- r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html Send "info", "help", or "[un]subscribe" (in the "body", not the subject !) To: r-help-request at stat.math.ethz.ch _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
On 14 Aug 2001, at 16:49, Prof Brian D Ripley wrote:> On Tue, 14 Aug 2001, Petr Pikal wrote: > > > Dear R users > > > > I use R (1.3.0) Windows version usually with connection to Excel > > sheets via RODBC Version: 0.8-2. Everything is OK until I use mixed > > data types (numeric and character) in one column. > > You can't have different data types in one column of a data frame.I know. I have read the documentation. But I wonder if I can coerce transported data to stay same as they appear in Excel sheet, let say as a columns of characters. You> are not telling us what you are doing (at all: not a single command > you used), but I suspect you need to know about as="matrix", which > will import the data as a character matrix for you.Here are the commands I use with some sample data> > ppp_sqlQuery(con,"select * from [test$]") > pppvzor x y z tep 1 110 51.24 20.26 11.87 ac 2 110 51.34 20.42 11.90 ab 3 110 49.77 22.05 11.42 NA 4 NA 49.84 19.30 7.43 ac 5 NA 49.88 19.32 7.50 ab 6 NA 49.76 18.11 4.49 NA> ppp_sqlQuery(con,"select * from [test$]", as = "matrix") > pppvzor x y z tep [1,] "110.0" "51.24" "20.26" "11.87" "ac" [2,] "110.0" "51.34" "20.42" "11.9" "ab" [3,] "110.0" "49.77" "22.05" "11.42" "NA" [4,] "NA" "49.84" "19.3" "7.43" "ac" [5,] "NA" "49.88" "19.32" "7.5" "ab" [6,] "NA" "49.76" "18.11" "4.49" "NA" In Excel first column data was TP400 and in third column there was number 12 instead of NA. Adding as.is = TRUE doesn't make any difference. Most probably I will need to check consistency of my data directly in Excel before making a connection and transferring them to R. Thanks Petr Pikal Precheza a.s. Nabr.Dr.E.Benese 24 tel: 00420 641 25 2257 +420 (0)724 008 364 petr.pikal at precheza.cz p.pik at volny.cz -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.- r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html Send "info", "help", or "[un]subscribe" (in the "body", not the subject !) To: r-help-request at stat.math.ethz.ch _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._