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
HI, no sorry, so far nobody answer. So it probably does not have a solution. Excell is from you.know.who>===== Original Message From Meinhard Ploner <meinhard.ploner at univie.ac.at>====>Hello!>Did you resolve the problem? >I'm interested in the solution, too. >Meinhard > >On Thursday, March 13, 2003, at 07:21 PM, R. Heberto Ghezzo wrote: > >> 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 >> https://www.stat.math.ethz.ch/mailman/listinfo/r-help >>R. Heberto Ghezzo Ph.D. Meakins-Christie Labs McGill University Montreal - Que - Canada
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 https://www.stat.math.ethz.ch/mailman/listinfo/r-help