Re: [R] File to MYSQL

From: Jerome Asselin <jerome.asselin.stat_at_gmail.com>
Date: Mon, 16 May 2011 15:46:45 -0400

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



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 - 19:48:55 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