[R] RODBC and Excel in Widows

Tom Mulholland tmulholl at bigpond.net.au
Wed Mar 26 15:56:19 CET 2003

I routinely use Excel to store my data. I am using 1.6.2 and probably have
the latestet RODBC. I recall when I first started using it having similar
problems. If I recall correctly the solution (which I didn't identify
precisely) was to make sure that my spreadsheet didn't have any abnormal
cells. By that I mean I made sure all labels were textual on the first line,
no extra spaces anywhere (especially at the end of a cell) If I were using
numbers as codes I spelt them out.

So my advice is to try to start really small and make your way upwards. If
something doesn't work try putting quotes around it etc. When I get to work
tomorrow I'll see if I have any notes that I may have written when I was
plodding through this phase. I can recall a problem that I had with a
product from you.know.who that wouldn't work because a space was formatted
as bold. So it is quite possible for an excel sheet to have hidden "stuff"
that you can't see.

-----Original Message-----
From: r-help-bounces at stat.math.ethz.ch
[mailto:r-help-bounces at stat.math.ethz.ch]On Behalf Of R. Heberto Ghezzo
Sent: Friday, 14 March 2003 2:22 AM
To: r-help at stat.math.ethz.ch
Subject: [R] RODBC and Excel in Widows

Hello, I have some problems with RODBC and Excel in Win98
I am using R 1.6.2 and just upgraded RODBC to the last version on CRAN.
I have an Excel file with columns Number, Name, Sex, Age, FEV1 on Sheet
1 and Number, Age, FEV1, Name, Sex on Sheet 2.
Now I open the channel to the file
> chan1 <- odbcConnectExcel("c:/testOdbc.xls")
> tables(chan1)
and the list appears with the 2 tables
> aa -> sqlFetch(chan1,"Sheet1")
and aa has the Number, Name and Sex columns correct but Age and FEV1 are
all NAs
> bb -> sqlfetch(chan1,"Sheet2")
and bb is correct!
So all numeric columns after a column of characters become NAs
Is this an Excel problem or an sql problem.? I did not find anything in
the r-help archives relative to this problem.
Thanks for any help

R-help at stat.math.ethz.ch mailing list

More information about the R-help mailing list