Ruixin,
I think there are a number of issues here:
FIrst of all, ODBC is data base talk. In a data base there are ususally a
number of tables. Hence you should tell R which table you want to read.
the function sqlTables can tell you which tables are available.
Second, I seem to remember that the $ in some of these table names caused me
some trouble
Perhaps
sqlFetch(cnct,'Sheet1') or sqlFetch(cnct,'Sheet1$') could do the
trick and
avoid you the query
Third, it seems that the driver is determining variable types and passing
these to R. It is not very good at that. You might have unexpected results.
The bright side is, SQL/ODBC is powerfull. After using named regions in my
Excel sheetI had the following query running :)
oCE <- odbcConnectExcel('blockdata.2.xls')
#sqlTables(oCE)
# keep for reference
q1 <- sqlQuery(oCE,'select * from linksblok , ((select * from blok1 union
select * from blok2 union select * from blok3 union select * from blok4 union
select * from blok5 union select * from blok6) as dit), rechtsblok, seco
where linksblok.mainid=dit.mainid and linksblok.mainid=rechtsblok.mainid and
dit.oldmonscode=seco.oldmonscode ')
On Thursday 24 May 2007 04:43, Ruixin ZHU wrote:> Hi, everyone,
>
> I try to run as follows:
> Z>library("RODBC")
> Z>cnct<-odbcConnectExcel("Forbes2000.xls")
> Z>cnct
> RODB Connection 1
> Details:
> case=nochange
> DBQ=C:\Program Files\R\R-2.5.0\Forbes2000.xls
> DefaultDir=C:\Program Files\R\R-2.5.0
> Driver={Microsoft Excel Driver (*.xls)}
> DriverId=790
> MaxBufferSize=2048
> PageTimeout=5
> Z>sqlQuery(cnct, "select * from \"Forbes2000\\$\"")
>
> However, I got error like this:
> [1] "[RODBC] ERROR: Could not SQLExecDirect"
>
> [2] "42S02 -1305 [Microsoft][ODBC Excel Driver] Microsoft Jet
> ??????????
> ??????????'Forbes2000\\$'??????????????????????????
>???????????????????? ????"
>
> I do can find Forbes2000.xls in directory, C:\Program Files\R\R-2.5.0.
> Would anybody help me out?
>
> Thanks for any suggestions!
> _____________________________________________
> Dr.Ruixin ZHU
> Shanghai Center for Bioinformation Technology
> rxzhu at scbit.org
> zhurx at mail.sioc.ac.cn
> 86-21-13040647832
>
>
> [[alternative HTML version deleted]]