[R] File to MYSQL

[Jerome Asselin]

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