R help - Jan 2010 - Problem associated with importing xlsx data file (Excel 2007)

Hi all,


I have imported xlsx file (Excel 2007) into R using the following scripts.


*library(RODBC)
*
*setwd(".......") *

*query <- odbcConnectExcel2007(xls.file = "GI 2010.xlsx", readOnly
= TRUE)

dat <- sqlQuery(query, "select * from [sheet1$]", as.is = TRUE,
na.strings "exp")*


*dat* contain one column consisting of intergers and characters
(unique value which is "exp").

However, R recognises the class of this column as 'numeric' instead of
'character' (i.e via sapply(dat, class)).

In addition, all the values of this column that are supposed to be class of
'character' are presented as 'NA'.

Interestingly, when the file is saved in csv format and imported into R,
this problem does not occur.

Any advice on this problem?

Thank you as always.


-- 
Steven

	[[alternative HTML version deleted]]

On Jan 27, 2010, at 9:41 PM, Steven Kang wrote:
> Hi all,
>
>
> I have imported xlsx file (Excel 2007) into R using the following  
> scripts.
>
>
> *library(RODBC)
> *
> *setwd(".......") *
>
> *query <- odbcConnectExcel2007(xls.file = "GI 2010.xlsx",
readOnly =
> TRUE)
>
> dat <- sqlQuery(query, "select * from [sheet1$]", as.is =
TRUE,
> na.strings > "exp")*
>
>
> *dat* contain one column consisting of intergers and characters
> (unique value which is "exp").
>
> However, R recognises the class of this column as 'numeric' instead
of
> 'character' (i.e via sapply(dat, class)).
>
> In addition, all the values of this column that are supposed to be  
> class of
> 'character' are presented as 'NA'.
If the the vector is of type numeric then NO values in the  
vector(column) are supposed to be (or even can be) of type character.  
R does not have a mixed type vector.  You have told sqlQuery that  
"exp" should be converted to NA at the time of input.
>
> Interestingly, when the file is saved in csv format and imported  
> into R,
> this problem does not occur.
>
Then that vector must be of character type. (So it's less interesting  
than you might have thought.)
> Any advice on this problem?
>
> Thank you as always.
>
>
> -- 
> Steven
>
> 	[[alternative HTML version deleted]]
>
> ______________________________________________
> 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.
David Winsemius, MD
Heritage Laboratories
West Hartford, CT