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