[R] RODBC and Excel Files

[Stephane Dray]

>
>I am not sure if sqlFetch works with Excel files. I used
>
>data.frame.name <- sqlQuery(connection,"select * from [listname$]")

Yes, I think that there is a problem with sqlFetch and Excel Files. 
So, I try with dbf file:

  > library(RODBC)
  > conection<-odbcConnect("dBASE Files")
  > sqlTables(conection)
    TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
1 F:\\THESE\\R          NA      doubs      TABLE      NA
  > sqlFetch(conection,"doubs")[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

So it works with no problem...
Then i try the sqlSave:

  > sqlSave(conection,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


So it seems that there are problems to create the new table. Then i 
create an empty dbf file ("fau01.dbf") and try another time the 
function:

  > sqlTables(conection)
    TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
1 F:\\TH»SE\\R          NA      doubs      TABLE      NA
2 F:\\TH»SE\\R          NA      fau01      TABLE      NA
  > sqlFetch(conection,"doubs")[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
  > sqlSave(conection,fau01,verbose=T)
[1] "DELETE FROM fau01"
[1] "INSERT INTO fau01 ( 
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, ) VALUES ( 
?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )"
[1] 0
[1] "DROP TABLE fau01"
[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

The table fau01.dbf has been removed but nothing created:
  > sqlTables(conection)
    TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
1 F:\\THESE\\R          NA      doubs      TABLE      NA

So it appears that i have only problems to export a dataframe. I 
don't know if it particular to my system...

  > version
          _             
platform i386-pc-mingw32
arch     x86           
os       Win32         
system   x86, Win32    
status                 
major    1             
minor    4.1           
year     2002          
month    01            
day      30
language R

-- 
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
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._