R_MRT_FLOW.R

rm(list=ls())

library(readODS)
library(lubridate)
library(tidyr)
library(RODBC)

setFiles <- paste0(
  unique(gsub("-","",substr(ymd(seq.Date(as.Date('2015-01-01'),as.Date('2019-03-01'),by = "month")),1,7))),
  "_cht.ods"
)
setUrls <- paste0(
  "https://web.metro.taipei/RidershipPerStation/",
  setFiles
)
setSavePath <- paste0(
  "D:\\openData\\Taipei_MRT\\",
  setFiles
)
file.exists("D:\\openData\\Taipei_MRT\\201903_cht.ods")


MRTDat <- vector(mode = "list",length(setFiles))
for (i in 13:length(setFiles)) {
  if (!file.exists(setSavePath[i])) {
    download.file(setUrls[i], destfile=setSavePath[i], mode="wb")
    
    tmpInDat <- read_ods(setSavePath[i],"進站資料")
    tmpInDat <- tmpInDat[,which(names(tmpInDat)!="")]
    
    tmpOutDat <- read_ods(setSavePath[i],"出站資料")
    tmpOutDat <- tmpOutDat[,which(names(tmpOutDat)!="")]
    
    tmpDat <- rbind(
      cbind(gather(tmpInDat,key=key,value=value,names(tmpInDat)[-1]),data_type = '進站'),
      cbind(gather(tmpOutDat,key=key,value=value,names(tmpInDat)[-1]),data_type = '出站')
    )
    names(tmpDat) <- c('Date','stationName','flowNum','DataType')
    tmpDat$Date <- as.Date(ymd(tmpDat$Date))
    MRTDat[[i]] <- tmpDat
    rm(tmpDat)
  }
}

NewMRTDat <- do.call('rbind',MRTDat)
NewMRTDat <- NewMRTDat[which(!is.na(NewMRTDat$Date)),]

dbhandle <- odbcDriverConnect(
  'driver={SQL Server};
  server=;
  database=;uid=;pwd='
)

sqlClear(dbhandle,'F_TAIPEI_MRT_FLOW')
sqlSave(dbhandle,
        dat =  NewMRTDat,
        tablename = "F_TAIPEI_MRT_FLOW",
        rownames = F,
        addPK = T,
        append = T,
        fast = F,
        verbose = T)

odbcClose(dbhandle)