使用Access数据库

library(readr)
library(purrr)
library(lubridate)
library(dplyr)
library(RSQLite)
library(Hmisc) 
system("sudo apt-get install -y mdbtools")
library(aws.s3)
Sys.setenv("AWS_ACCESS_KEY_ID" = "X",
           "AWS_SECRET_ACCESS_KEY" = "X",
           "AWS_DEFAULT_REGION" = "eu-central-1")


# FROM ACCESS IN AWS TO CSV
access_to_csv <- function(file, bucket) {
  # Functions
  import_fom_s3 <- function(file, bucket) {
    save_object(object = file,
                bucket = bucket,
                file = file)
  }
  
  get_access_table <- function(name, database = file) {
    path <- sprintf("extracted_tables/%s.csv", name)
    if (!file.exists(path)) {
      df <- mdb.get(database, name)
      write_csv(df, path)
      print(path)
    }
  }
  
  import_fom_s3(file, bucket)
  table_names <- mdb.get(file, tables = TRUE)
  
  if (dir.exists("extracted_tables") == FALSE) {
    dir.create("extracted_tables")
  }
  
  map(table_names, get_access_table)
}

# CONSTRUCT SQLITE DATABASE
csv_to_sqlite <- function(name, path = "extracted_tables") {
  
  #Functions
  despace_name <- function(name){
    tolower(
      gsub(" ","_",name)
    )
  }
  add_to_sqlite <- function(file) {
    print(route)
    
    name_table <- gsub(".csv", "", despace_name(file)) 
    print(name_table)
    
    data_table <- read_csv(paste0(path, "/", file))
    
    if (!(name_table %in% c("punten", "info"))) {
      data_load <- data_table %>% 
        mutate(datetime = mdy_hms(Systeemtijd),
               datetime = force_tz(datetime, tz = "CET"),
               datetime = as.integer(datetime)) %>% 
        rename(value = Waarde) %>% 
        select(datetime, value)
    } else {
      data_load <- data_table
    }
    
    dbWriteTable(con,
                 name = name_table,
                 value=data_load, 
                 overwrite = TRUE)
  }
  
  
  # Execute
  con <- dbConnect(SQLite(), name)
  csv_files <- list.files(route)
  map(csv_files, possibly(add_to_sqlite, NULL))
  
}