Dear Jerome,
I am a little ashamed about myself because the solutions is very easy. I
thought I have already read help about write.table.
But in fact I would like to use the RMysql to feed my database if you know
something about I'd like to know.
Bye and Thanks.
On Mon, May 16, 2011 at 5:01 PM, Nilza BARROS <nilzabar...@gmail.com> wrote:
> Hi, Jerome
>
> I was trying to use RMYSQL
>
> for (i in length(Query)) {
> rs1<-dbSendQuery(con,Query[i])
> }
>
> But although the Query have several lines the command above just feed my
> database with the first one.
>
> > Query
> [1] "INSERT INTO OBS
> (date,T_2M,TMAX_2M,TMIN_2M,TD_2M,PS,FF_10M,DD_10M,TOT_PRCP,CLCL,Station_NO)
> VALUES (2011051312,26,NULL,20.6,19.4,1014.8,2.91,220,0.00,6,836490);"
> [2] "INSERT INTO OBS
> (date,T_2M,TMAX_2M,TMIN_2M,TD_2M,PS,FF_10M,DD_10M,TOT_PRCP,CLCL,Station_NO)
> VALUES (2011051312,17,NULL,15.5,15.7,912.8,9.91,180,0.00,8,836920);"
> [3] "INSERT INTO OBS
> (date,T_2M,TMAX_2M,TMIN_2M,TD_2M,PS,FF_10M,DD_10M,TOT_PRCP,CLCL,Station_NO)
> VALUES (2011051312,21.6,NULL,18.1,19.7,1003,1.94,140,0.00,2,836950);"
>
> Thanks,
>
>
>
> On Mon, May 16, 2011 at 4:46 PM, Jerome Asselin <
> jerome.asselin.s...@gmail.com> wrote:
>
>> On Mon, 2011-05-16 at 14:55 -0300, Nilza BARROS wrote:
>> > Dear R-user,
>> >
>> > I have to feed my database using some SQL commands. I have already read
>> a
>> > data frame with the data I need but
>> > after that these data should be write in a file wtih SQL commands.
>> >
>> > 1) My dataframe:
>> >
>> > dput(Alldados)
>> >
>> > structure(list(Station_NO = c(836490, 836920, 836950, 836980,
>> > 837380, 837460), TMAX_2M = c("NULL", "NULL", "NULL", "NULL",
>> > "NULL", "NULL"), TMIN_2M = c("20.6", "15.5", "18.1", "19.9",
>> > "17", "21.5"), TD_2M = c("19.4", "15.7", "19.7", "20.1", "17.5",
>> > "20.4"), PS = c("1014.8", "912.8", "1003", "1014.4", "967.8",
>> > "NULL"), FF_10M = c("2.91", "9.91", "1.94", "4.08", "0", "6.02"
>> > ), DD_10M = c(220, 180, 140, 180, 0, 320), date2 = c("2011051312",
>> > "2011051312", "2011051312", "2011051312", "2011051312", "2011051312"
>> > )), .Names = c("Station_NO", "TMAX_2M", "TMIN_2M", "TD_2M", "PS",
>> > "FF_10M", "DD_10M", "date2"), row.names = c(108L, 112L, 113L,
>> > 114L, 119L, 120L), class = "data.frame")
>> >
>> >
>> > 2) My script
>> >
>> > outfile<- with(as.data.frame(Alldados),sprintf("INSERT INTO OBS
>> > (date,TMAX_2M,TMIN_2M,TD_2M,PS,FF_10M,DD_10M,Station_NO) VALUES (%s, %s,
>> %s,
>> > %s,%s,%s,%s,%s)",date2,TMAX_2M,TMIN_2M,TD_2M,PS,Station_NO))
>> >
>> write.table(outfile,file=paste(dat.dir,"outfile_13mai.txt",sep=""),append=FALSE,col.names=FALSE,
>> > row.names=FALSE)
>> >
>> >
>> > 3) The commands above works but the problem is that the
>> oufile_13mai.txt
>> > have several quotes (") . So when I am going to feed my database using
>> the
>> > command
>> > /usr/bin/mysql -uxx -pxxx ormverif < ~/ormverif/syn/outfile_13mai.txt
>> > ERROR 1064 (42000) at line 1: You have an error in your SQL syntax;
>> check
>> > the manual that corresponds to your MySQL server version for the right
>> > syntax to use near '"INSERT INTO OBS
>> > (date,T_2M,TMAX_2M,TMIN_2M,TD_2M,PS,FF_10M,DD_10M,TOT_PRCP,CLCL' at line
>> 1
>> >
>> >
>> > "INSERT INTO OBS (date,TMAX_2M,TMIN_2M,TD_2M,PS,Station_NO) VALUES
>> > (2011051312, NULL, 20.6,19.4,1014.8,836490)"
>> > "INSERT INTO OBS (date,TMAX_2M,TMIN_2M,TD_2M,PS,Station_NO) VALUES
>> > (2011051312, NULL, 15.5,15.7,912.8,836920)"
>> >
>> >
>> > So I need a file like below:
>> >
>> >
>> > INSERT INTO OBS (date,TMAX_2M,TMIN_2M,TD_2M,PS,Station_NO) VALUES
>> > (2011051312, NULL, 20.6,19.4,1014.8,836490)
>> > INSERT INTO OBS (date,TMAX_2M,TMIN_2M,TD_2M,PS,Station_NO) VALUES
>> > (2011051312, NULL, 15.5,15.7,912.8,836920)
>>
>> Have you considered the "quote=FALSE" option in write.table?
>>
>> Another option would be to use the sqlUpdate() command from the RODBC
>> package. That would allow you to insert data into your SQL server
>> directly from R.
>>
>> HTH,
>> Jerome
>>
>>
>
>
> --
> Abraço,
> Nilza Barros
>
--
Abraço,
Nilza Barros
[[alternative HTML version deleted]]
______________________________________________
R-help@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.
