0

I run an R script which contains SQL query and import the .csv file and mail it to the manager and operations daily. But I am looking forward to automating this process so kindly help me with the steps I should do to automate.

The .csv file has the day stamp. 

mydb = dbConnect(MySQL(), user='*******', password='******', dbname='********', host='*****8***')

setwd('C:\\Users\\******\\Work\\2. ****\\26. Cluster_Bags')

#Change the data here 
startdate='2017-07-24'

sql_text=paste("SELECT ********************************,


FROM ********************
WHERE ***********
                ")
ClusterwiseBag=dbGetQuery(mydb,sql_text)

#Creates a file with the name "Clusterbag date.csv"           
b=paste("Clusterbag",startdate,".csv",sep="")

write.csv(ClusterwiseBag,b)
Gabriel Diez
  • 1,648
  • 2
  • 17
  • 26
Suraj Kunuku
  • 1
  • 2
  • 1
    Are you using `PHP`? If yes then use `cron job` for this. – Zaid Bin Khalid Aug 09 '17 at 09:39
  • 2
    You may 1) create a batch file that runs your script (it would be a .bat file that consists of something like `"c:\Program Files (x86)\R\R-3.3.0\bin\x64\R" CMD BATCH d:/Data/Test/YourScript.r` 2) Schedule that batch file to run once a day with Windows Task Scheduler (if using Windows; other OS have their own schedulers). Also, to send an e-mail from R, see https://stackoverflow.com/questions/23412265/how-do-you-send-email-from-r – Ape Aug 09 '17 at 11:20

1 Answers1

1

This is how I go about automating scripts:

First change startdate to:
startdate = Sys.Date()

If you like, add automatic email, for example with:
library(mailR)
send.mail(from = "from@gmail.com",
to = c("to1@gmail.com", "to2@gmail.com"),
subject = "Update", body = " ", authenticate = T,
smtp = list(host.name = "smtp.gmail.com", port = 465,
user.name = "from", passwd = "123456", ssl = T),
attach.files = c(b))

Then use the RStudio Add-in taskscheduleR to let the script run on a daily basis.

Samuel Reuther
  • 101
  • 1
  • 3