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 _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
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 0as 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 _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
Sorry for previous post...
I find the reasons of my problems,
Windows used uppercase letter and i must precise it in my connection:
odbcConnect("dBASE Files",case="toupper")
And it is ok for excel Files.
For dbf files, there is another problem: by default, the size of the
column is set to 255 in the function sqlSave (V7 in data.frame
newname). This seems to be too much and when i change this value
(e.g. v7=40) the problem is solved.
Sincerely,
--
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
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._