I have a R script that runs fine in R studio . The idea is to run a batch file that has R script. But this is not working. I have described the problem below
This is the R file below
Update_Price_Table.R
library(RODBC)
library(rlang)
library(shiny)
library(jsonlite)
library(rjson)
library(stringr)
require(reshape2)
library(dplyr)
library(tidyverse)
require(reshape2)
library(readxl)
library(lubridate)
library(zoo)
library(shinyalert)
library(DT)
library(plotly)
# library(rhandsontable)
# library(kableExtra)
# library(reactable)
library(glue)
library(shinyjs)
library(shinycssloaders)
library(data.table)
## Connnection to sql server
conn <- odbcDriverConnect(connection = paste0("XXXXXXXXXXX"))
update_Price_Table_to_sql <- function(r_DataFrame, table_Name){
sqlQuery(conn,paste0("drop table ", table_Name))
sqlSave(conn, r_DataFrame, tablename = table_Name,varTypes=c(description = "nvarchar(MAX)", name = "nvarchar(MAX)",time = "datetime2"),rownames = FALSE)
}
nullToNA <- function(x) {
x[sapply(x, is.null)] <- NA
return(x)
}
get_Links_int <- function(Description, from, to){
com <- sqlQuery(conn,paste0("Select symbol, company_Name from companiesList where Location = 'India' ORDER BY slNo desc OFFSET ",from, " ROWS FETCH NEXT ", to, " ROWS ONLY"))
com_symbol <- capture.output(cat(com$symbol, sep = ","))
Link <- sqlQuery(conn,paste0("select Link+API as Link from APIlist where [Description] in (","'", Description, "'", ")"))
return(gsub("companies_to_be_passed", com_symbol, Link))
}
main_Data_Get <- function(Description, from, to){
datalist = list()
for (j in c(1:5)){
i <- from
asd <- c(get_Links_int(Description, i, to))
while (i < 2208) {
asd <- c(asd, get_Links_int(Description, i, to))
i <- i + to
}
asd <- asd[-1]
datalist[[j]] <- rbindlist(lapply(rjson::fromJSON(file = asd[j]), nullToNA), fill = TRUE)
}
return(do.call(rbind, datalist))
}
main_data <- as.data.frame(main_Data_Get('To get latest day prices', from = 0, to = 499))
main_company_info <- as.data.frame(main_Data_Get('To get Company Information', from = 0, to = 499))
main_real_Time_price <- as.data.frame(main_Data_Get('To get Real Time Price', from = 0, to = 499))
main_Stock_price_Change <- as.data.frame(main_Data_Get('To get Stock Price Change', from = 0, to = 499))
#historical_Price <- lapply(rjson::fromJSON(file = gsub("2021-09-24", Sys.Date(), gsub("2021-08-24", Sys.Date()-20, get_Links('To get historical prices', TRUE)))), nullToNA) %>% pluck("historicalStockList") %>% map_dfr(~ as_tibble(.x) %>% unnest_wider(historical))
main_data['yearHigh - dayHigh'] <- round(main_data['yearHigh'],0) - round(main_data['dayHigh'], 0)
main_data['eps'] <- round(main_data['eps'],2)
company_info_Columns <- c('description', 'industry', 'sector', 'isin', 'isActivelyTrading', 'fullTimeEmployees')
real_Time_price_Columns <- c('prevClose', 'low')
Stock_price_Change_Columns <- c('1D', '5D')
main_data <- inner_join(main_data, main_company_info[c(company_info_Columns, 'symbol')], by='symbol')
main_data <- inner_join(main_data, main_real_Time_price[c(real_Time_price_Columns, 'symbol')], by='symbol')
main_data <- inner_join(main_data, main_Stock_price_Change[c(Stock_price_Change_Columns, 'symbol')], by='symbol')
required_Columns <- c('symbol', 'name', 'price', 'yearHigh - dayHigh', 'dayHigh', 'eps', 'marketCap', 'pe', 'open', 'volume', 'priceAvg50', 'priceAvg200', company_info_Columns, real_Time_price_Columns, Stock_price_Change_Columns)
hidden_Columns <- c("symbol", "marketCap", "pe", "description", "industry", "sector", "isin", "prevClose", "low", "open", "pc", "1D", "5D", "volume", "isActivelyTrading", "priceAvg50", "priceAvg200", "time")
main_data <- main_data[required_Columns][order(main_data['yearHigh - dayHigh'], decreasing = FALSE),] %>% mutate_at(c('fullTimeEmployees'),
as.numeric) %>% filter(low > 0,
marketCap > 0,
volume > 0,
isActivelyTrading == TRUE
# between(`yearHigh - dayHigh`, input$getDiff_id[1], input$getDiff_id[2]),
# between(dayHigh, input$getDHP_id[1], input$getDHP_id[2]),
# between(`1D`, input$getID_id[1], input$getID_id[2]),
# between(`5D`, input$get5D_id[1], input$get5D_id[2])
)
main_data$pc <- do.call(paste, c(main_data[c('1D', '5D')], sep = '_'))
main_data$time <- Sys.time()
update_Price_Table_to_sql(main_data, "Price_Table")
Above code actually updates a dataframe in sql table.
However when the same above R file is run using bat file as below, there is an error that occurs
"C:\Program Files\R\XXXX\XXX\XXX\Rscript.exe" Update_Price_Table.R
pause
Error below
Not sure why is this? Can anyone help me here
I have also added my working directory here
