[R] RODBC to import/export xls files

[Henrique Dallazuanna]

see below:

On Tue, Feb 23, 2010 at 2:16 PM, Ivan Calandra
<ivan.calandra at uni-hamburg.de> wrote:
> Dear R users,
>
> I've learned today about RODBC package in order to import xls file to
> dataframes and export the dataframes to xls files.
>
> However I have some problems. Please excuse me if these are basic but as I
> said, I've just begun with this package.
> Also this email is quite long, but everything is related, so I don't think
> it would be better to split it. Moreover, there's a lot from an error
> message and dput(). I hope it won't discourage you. I believe I've
> compressed it as much as possible
>
> - to import, I do this:
> con <- odbcConnectExcel("D:\\R\\cdeg_SSFA_R.xls")
> cdeg <- sqlFetch(con, "cdeg")

 base <- sqlFetch(con, gsub("'|\\$", "", sqlTables(con)$TABLE_NAME[1]))

> close(con)
> It works great, but is there a way without giving the sheet name (here
> "cdeg")?
> The thing is that I almost always have a single sheet in my xls files, which
> is not really named (usually sheet1, sheet2, etc), so it would be great if I
> could just import the 1st sheet into the dataframe.
>
> - to export I do this (from Tal Galili's solution earlier today):
> xlsFile <- odbcConnectExcel("file", readOnly=F)
> save2excel <- function(x, t.name) sqlSave(xlsFile, x, tablename=t.name,
> rownames=F, addPK=T)
> odbcCloseAll()
> It also works great, but how can I append lines? This function allows me to
> append new sheets in the file, but I would also like to append new lines of
> data onto one of the sheets. How can I do that?

See apend argument from sqlSave funciton.


>
> - The last problem:
> I would like to export a dataframe, but I get this error message:
>> save2excel(truc, "test")
> Error in sqlSave(xlsFile, x, tablename = t.name, rownames = FALSE, addPK =
> T) :
>  HYS21 -1508 [Microsoft][Pilote ODBC Excel] Le champ 'Group' existe déjà
> dans la table 'test'.
> [RODBC] ERROR: Could not SQLExecDirect 'CREATE TABLE [test]  ("Group"
> NUMBER, "Group" NUMBER, "test" NUMBER, "crit" NUMBER, "se" NUMBER, "df"
> NUMBER)'
>
> Here is the object:
>> dput(truc)
> structure(list(Group = c(1, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 4, 4, 5), Group
> = c(2, 3, 4, 5, 6, 3, 4, 5, 6, 4, 5, 6, 5, 6, 6), test =
> c(0.907901716802262, 0.344080298502179, 0.780331168285069, 2.15033450900747,
> 0.194069709110157, 0.406313481811673, 0.116434832984871, 1.52727249397868,
> 0.579195268552845, 0.420998518182892, 1.62939679051865, 0.138892090710994,
> 1.11253003450962, 0.555496810201502, 1.78610263147685), crit =
> c(2.97744276941854, 2.98843563665370, 3.05786246930315, 3.00681172135752,
> 2.99644239171413, 3.00036808377006, 3.07797861408659, 3.02408697737205,
> 3.01060847439059, 3.03639474502811, 2.99664917266571, 2.99135011459308,
> 3.04177033698215, 3.04239401519992, 3.00305006434149), se =
> c(12.1028423223510, 15.1532795810610, 16.3991441261986, 15.3598663144688,
> 14.9271014453042, 14.2113088036481, 15.5329116369945, 14.4313862483545,
> 13.9698880958026, 18.0115083683957, 17.0706765780184, 16.6823518483263,
> 18.1856549192307, 17.8216415967046, 16.8702244895529), df =
> c(99.682919819226, 89.3039016709219, 50.9067902231679, 76.0647290534967,
> 83.0087667496558, 80.2356900159524, 43.4849997568059, 66.760479193059,
> 73.8040951542063, 61.4088750589742, 82.8579246229594, 86.904905246028,
> 58.9562752558462, 58.5971747302359, 78.4453136851244)), .Names = c("Group",
> "Group", "test", "crit", "se", "df"), row.names = c(NA, -15L), class =
> "data.frame")
>
> Any ideas?
>
> Thanks a lot for your help
> Ivan
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>



-- 
Henrique Dallazuanna
Curitiba-Paraná-Brasil
25° 25' 40" S 49° 16' 22" O