[R] Problem associated with importing xlsx data file (Excel 2007)

[David Winsemius]

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