[R] RODBC and Excel Files

[Stephane Dray]

Hello,
I am trying to play with RODBC library and Excel Files. In my file 
(doubs.xls) there are 2 spreadsheets:

>  library(RODBC)
>  connection<-odbcConnect("Excel Files")
>  sqlTables(connection)
          TABLE_CAT TABLE_SCHEM TABLE_NAME   TABLE_TYPE REMARKS
1 F:\\Thèse\\R\\Doubs          NA     Faune$ SYSTEM TABLE      NA
2 F:\\Thèse\\R\\Doubs          NA    Milieu$ SYSTEM TABLE      NA

I have two problems.


(1) For loading the tables in R, I use

>  sqlFetch(connection,"Faune$")
[1] "37000 -3506 [Microsoft][Pilote ODBC Excel] Erreur de syntaxe 
dans la clause FROM."
[2] "[RODBC]ERROR: Could not SQLPrepare"

There is a syntax error (probably to name the table) but i don't find 
the answer.. (if you know it..)
To solve this problem, I use an alternative:
>  sqlQuery(connection,"select * from [Faune$]")[1:5,]
   a b c d e f g h i j k l m n o p q r s t u v w x y z +
1 0 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
2 0 5 4 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
3 0 5 5 5 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0
4 0 4 5 5 0 0 0 0 0 1 0 0 1 2 2 0 0 0 0 1 0 0 0 0 0 0 0
5 0 2 3 2 0 0 0 0 5 2 0 0 2 4 4 0 0 2 0 3 0 0 0 5 0 0 0

and it works fine....

(2) I have another problem to save a data frame in my xls file and 
for this case i do not have any alternatives:

>  sqlSave(connection,fau01,verbose=T)
[1] "CREATE TABLE fau01  (a varchar(255)  ,b varchar(255)  ,c 
varchar(255)  ,d varchar(255)  ,e varchar(255)  ,f varchar(255)  ,g 
varchar(255)  ,h varchar(255)  ,i varchar(255)  ,j varchar(255)  ,k 
varchar(255)  ,l varchar(255)  ,m varchar(255)  ,n varchar(255)  ,o 
varchar(255)  ,p varchar(255)  ,q varchar(255)  ,r varchar(255)  ,s 
varchar(255)  ,t varchar(255)  ,u varchar(255)  ,v varchar(255)  ,w 
varchar(255)  ,x varchar(255)  ,y varchar(255)  ,z varchar(255)  , 
varchar(255)  )"
Error in sqlColumns(channel, tablename) : fau01 :table not found on channel 0


How can i save my dataframe ???

Thanks in advance.

-- 
Stéphane DRAY
---------------------------------------------------------------
Biométrie et Biologie évolutive - Equipe "Écologie Statistique"
Universite Lyon 1 - Bat 711 - 69622 Villeurbanne CEDEX - France

Tel : 04 72 43 27 56			   Fax : 04 78 89 27 19
       04 72 43 27 57 	   E-mail : dray at biomserv.univ-lyon1.fr 
---------------------------------------------------------------
ADE-4               http://pbil.univ-lyon1.fr/ADE-4/ADE-4F.html
---------------------------------------------------------------
-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._