From my experience (somewhat of a guess):

1.

Excel uses the first 16 rows of data to determine if a column is numeric or
character. The data type which is most common in the first 16 rows will then
be used for the whole column. If you sort the data so that at least the
first 9 rows have character data, you may find this allows the data to be
interpreted as character. There is supposedly a registy setting that can
control how many lines to use (instead of 16), but I have not had success
with the setting. I suspect that ODBC uses JET4, which may be the real
source of the problem. See more here:
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

2.

The gregmisc bundle has a different read.xls function that uses a Perl
script (xls2csv) and seems to be safer with mixed-type columns.
Requires a working version of Perl.

Best,

Kevin Wright



The first column in my Excel sheet has mostly numbers but I need to treat it
as character data:

> library(RODBC)
<http://tolstoy.newcastle.edu.au/R/help/05/09/11324.html#14938qlink1>
*> channel <- odbcConnectExcel("U:/efg/lab/R/Plasmid/construct list.xls") *
*> plasmid <- sqlFetch(channel,"Sheet1", as.is=TRUE) *
*> odbcClose(channel) *

> names(plasmid)

[1] "Plasmid Number" "Plasmid" "Concentration" "Comments" "Lost"

# How is the type decided? I need a character type.
> class(plasmid$"Plasmid Number")

[1] "numeric"
> typeof(plasmid$"Plasmid Number")

[1] "double"

> plasmid$"Plasmid Number"[273:276]

[1] 274 NA NA 276

The two NAs are supposed to be 275a and 275b. I tried the "as.is=TRUE" but
that didn't help.

I consulted Section 4, Relational databases, in the R Data Import/Export
document (for Version 2.2.0).

Section 4.2.2, Data types, was not helpful. In particular, this did not seem
helpful: "The more comprehensive of the R interface packages hide the type
conversion issues from the user."

Section 4.3.2, Package RODBC, provided a "simple example of using ODBC ..
with a(sic) Excel spreadsheet" but is silent on how to control the data type
on import. Could the documentation be expanded to address this issue?

I really need to show "Plasmid 275a" and "Plasmid 275b" instead of "Plasmid
NA".

Thanks for any help with this.

efg

--
Earl F. Glynn
Scientific Programmer
Bioinformatics Department

	[[alternative HTML version deleted]]


