[R] File to MYSQL

From: Nilza BARROS <nilzabarros_at_gmail.com>
Date: Mon, 16 May 2011 14:55:22 -0300


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]]



R-help_at_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. Received on Mon 16 May 2011 - 17:58:02 GMT

This quarter's messages: by month, or sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

All messages

Archive maintained by Robert King, hosted by the discipline of statistics at the University of Newcastle, Australia.
Archive generated by hypermail 2.2.0, at Mon 16 May 2011 - 20:20:07 GMT.

Mailing list information is available at https://stat.ethz.ch/mailman/listinfo/r-help. Please read the posting guide before posting to the list.

list of date sections of archive