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)
Thanks in advance,
Nilza Barros
[[alternative HTML version deleted]]
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
On Mon, 16-May-2011 at 02:55PM -0300, Nilza BARROS wrote:
|> Dear R-user,
[...]
|> 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,
Have you tried quote = FALSE also?
|> row.names=FALSE)
[...]
HTH
--
~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.
___ Patrick Connolly
{~._.~} Great minds discuss ideas
_( Y )_ Average minds discuss events
(:_~*~_:) Small minds discuss people
(_)-(_) ..... Eleanor Roosevelt
~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.~.