[R] sqlSave

[Jacques VESLOT]

Dear Tobias,

I finally succeeded in exporting my dataframes from R into Access with 
this script:

library(RODBC)
canal <- odbcConnectAccess("D:/Floristique.mdb")
sqlSave(channel=canal, dat=flore, tablename="Floristique", rownames=F,
    safer=F, fast=F,
    varTypes=c(dates="Date"))
odbcClose(canal)

My problem in exporting my dataframe "flore" seems to be closely linked 
with the name of the column of dates in my dataframe, which was formerly 
"date" and which I changed into "dates". Since my exporting worked (?).

The varTypes argument works as described in the sqlSave() help page: "an 
optional named character vector giving the DBMSs datatypes to be used 
for some (or all) of the columns if a table is to be created". However, 
I have only been able to export my dataframe with the "dates" column as 
Date; not as POSIX, though dates are in POSIX when importing into R with 
sqlQuery (?).

I have still questions about the functionning of these functions, but I 
have to acknowledge that I haven't yet reviewed many things about the 
subject.

Best regards,

jacques



Brandt, T. (Tobias) a écrit :

> Dear Jacques
>
> I refer to your post on r-help on 20 March 2006.
>
> I see that you had some trouble with sqlSave.  I also noticed that you 
> said you tried to use varTypes but didn't understand it properly.  
> I've had the same problem in that I'm trying to use sqlSave to save my 
> dataframes which contain dates to a database but have run into 
> problems.  My solution until now has been to save the dates as 
> characters and then convert them to dates with a query.  However I 
> would prefer to import them as dates in the first place.  Perhaps this 
> can be done with varTypes but I have been unable to figure out how to 
> use this properly. 
>
> (...)
>
> I look forward to your response.
>
> Kind regards
> Tobias Brandt
>  
>
> >-----Original Message-----
> >From: r-help-bounces at stat.math.ethz.ch
> >[mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of Jacques VESLOT
> >Sent: 20 March 2006 12:30 PM
> >To: r-help at stat.math.ethz.ch
> >Subject: Re: [R] sqlSave
> >
> >OK, I finally found what's wrong - "date" column name.
> >
> >
> >Jacques VESLOT a écrit :
> >
> >>Dear R-users,
> >>
> >>I tried to export a dataframe form R to Access, that way:
> >>
> >>library(RODBC)
> >>channel <- odbcConnectAccess("d:/test.mdb") sqlSave(channel=channel,
> >>flore, rownames=F)
> >>odbcClose(channel)
> >>
> >>But I always got this error message:
> >>
> >>Erreur dans sqlSave(channel = channel, flore, "Florist") :
> >>[RODBC] ERROR: Could not SQLExecDirect
> >>37000 -3553 [Microsoft][Pilote ODBC Microsoft Access] Erreur
> >de syntaxe
> >>dans la définition de champ.
> >>
> >>
> >>Note that I succeeded in exporting this dataframe into Excel and then
> >>import Excel file from Access.
> >>
> >>I tried to find where the problem comes from by exporting columns one
> >>by one, as follows:
> >>
> >>library(RODBC)
> >>canal <- odbcConnectAccess("d:/test.mdb") for (i in names(flore)) {
> >>cat(i)
> >>sqlSave(channel=canal, dat=flore[i]) }
> >>odbcClose(canal)
> >>
> >>I could export all columns but one, named "date", which
> >consists of dates.
> >>
> >>I tried to export this column as POSIX, as Date and even as
> >character,
> >>but without success. I still had the same error message:
> >>
> >>dateErreur dans sqlSave(channel = canal, dat = flore[i]) :
> >>        [RODBC] ERROR: Could not SQLExecDirect 37000 -3553
> >>[Microsoft][Pilote ODBC Microsoft Access] Erreur de syntaxe dans la
> >>définition de champ.
> >>
> >>I also tried with varTypes, though I am not sure how to use this
> >>argument correctly. I did:
> >>
> >>sqlSave(channel=canal, dat=flore, varTypes=c(date="Date"))
> >>sqlSave(channel=canal, dat=flore, varTypes=c(date="Date/Heure"))
> >>
> >>But still the same error message.
> >>
> >>Maybe it's in Windows, but I don't understand...
> >>
> >>Thanks for helping,
> >>
> >>jacques
> >>
> >> > R.version
> >>         _            
> >>platform i386-pc-mingw32
> >>arch     i386         
> >>os       mingw32      
> >>system   i386, mingw32
> >>status                
> >>major    2            
> >>minor    2.1          
> >>year     2005         
> >>month    12           
> >>day      20           
> >>svn rev  36812        
> >>language R
> >>
> >>______________________________________________
> >>R-help at stat.math.ethz.ch mailing list
> >>https://stat.ethz.ch/mailman/listinfo/r-help
> >>PLEASE do read the posting guide!
> >>http://www.R-project.org/posting-guide.html
> >>
> >> 
> >>
> >
> >______________________________________________
> >R-help at stat.math.ethz.ch mailing list
> >https://stat.ethz.ch/mailman/listinfo/r-help
> >PLEASE do read the posting guide!
> >http://www.R-project.org/posting-guide.html
> >
>
> ------------------------------------------------------------------------
> Nedbank Limited Reg No 1951/000009/06. The following link displays the 
> names of the Nedbank Board of Directors and Company Secretary 
> <http://www.nedbank.co.za/terms/DirectorsNedbank.htm>.
> This email is confidential and is intended for the addressee only. The 
> following link will take you to _Nedbank's legal notice 
> <http://www.nedbank.co.za/terms/EmailDisclaimer.htm>_.
> ------------------------------------------------------------------------