[R] RODBC and Excel Files

[Petr Pikal]

On 18 Jul 2002 at 14:49, Stephane Dray wrote:

> 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"

I am not sure if sqlFetch works with Excel files. I used

data.frame.name <- sqlQuery(connection,"select * from [listname$]")

(the same as you :-)
that is maybe the reason why sqlSave does not work too

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

as a workaround you can save your data frames (or everything:-) using sink

sink("name of the file.txt")
your.data.frame
sink()

see ?sink


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

Best regardsPetr Pikal
petr.pikal at precheza.cz
p.pik at volny.cz


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