inst/doc/dealing_with_DSV_files.R

In RSQLite.toolkit: Load Data in SQLite from Tabular Files

## ----include = FALSE----------------------------------------------------------
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

## ----setup--------------------------------------------------------------------
library(RSQLite.toolkit)

## ----download_fun-------------------------------------------------------------
download_release_asset <- function(file, dest = tempdir(),
                                   repo = "fab-algo/RSQLite.toolkit-tests",
                                   tag = "latest") {
  asset_name <- utils::URLencode(file, reserved = TRUE)
  download_url <- if (identical(tag, "latest")) {
    sprintf("https://github.com/%s/releases/latest/download/%s",
            repo, asset_name)
  } else {
    sprintf("https://github.com/%s/releases/download/%s/%s",
            repo, tag, asset_name)
  }

  destfile <- file.path(dest, basename(file))
  download_problem <- NULL
  result <- tryCatch(
    withCallingHandlers(
      utils::download.file(download_url, destfile = destfile, mode = "wb",
                           quiet = TRUE),
      warning = function(warning) {
        download_problem <<- conditionMessage(warning)
        invokeRestart("muffleWarning")
      }
    ),
    error = function(error) {
      download_problem <<- conditionMessage(error)
      NA_integer_
    }
  )

  if (!identical(result, 0L) || !file.exists(destfile)) {
    message <- sprintf(
      "Failed to download '%s' from '%s'. Check that the release asset exists and that network access to GitHub is available.",
      file, repo
    )

    if (!is.null(download_problem)) {
      message <- sprintf("%s Original error: %s", message, download_problem)
    }

    stop(message, call. = FALSE)
  }

  invisible(destfile)
}

## ----getdata------------------------------------------------------------------
download_release_asset("DOSE_V2.10.zip")

unzip(zipfile = file.path(tempdir(), "DOSE_V2.10.zip"), exdir = tempdir())
dir(file.path(tempdir(), "DOSE_V2.10"))

data_file <- file.path(tempdir(), "DOSE_V2.10/DOSE_V2.10.csv")

## ----inspect_file2------------------------------------------------------------
n_rows <- length(count.fields(data_file))
n_rows

## ----dbcon--------------------------------------------------------------------
dbcon <- dbConnect(RSQLite::SQLite(), file.path(tempdir(), "tests.sqlite"))

## ----label=loaddata1, error=TRUE----------------------------------------------
try({
## do not run: error
dbTableFromDSV(input_file = data_file, dbcon = dbcon, table_name = "DOSE")
})

## ----schema-------------------------------------------------------------------
f_schema1 <- file_schema_dsv(input_file = data_file,
                             quote = "\"", na.strings = "",
                             comment.char = "", fileEncoding = "UTF-8")

## ----schema_head--------------------------------------------------------------
f_schema1$schema[1:8, ]

## ----loaddata2----------------------------------------------------------------
dbTableFromDSV(input_file = data_file, dbcon = dbcon, table_name = "DOSE",
               drop_table = TRUE, quote = "\"", na.strings = "",
               comment.char = "", fileEncoding = "UTF-8")

## ----check_db-----------------------------------------------------------------
dbListTables(dbcon)

dbListFields(dbcon, "DOSE")[1:8]

dbGetQuery(dbcon, "SELECT COUNT(*) AS n_records FROM DOSE")

## ----getdata2-----------------------------------------------------------------
download_release_asset("Blockchain_Banking_Scopus_Dataset_2015_2025.zip")

unzip(zipfile = file.path(tempdir(),
                          "Blockchain_Banking_Scopus_Dataset_2015_2025.zip"),
      exdir = file.path(tempdir(), "Blockchain"))
dir(file.path(tempdir(), "Blockchain"))

data_file <- file.path(tempdir(),
                       "Blockchain/Blockchain_Banking_Scopus_Dataset_2015_2025.csv") # nolint

## ----inspect_colnames---------------------------------------------------------
f_schema2 <- file_schema_dsv(input_file = data_file,
                             quote = "\"", na.strings = "",
                             comment.char = "", fileEncoding = "UTF-8")

f_schema2$schema[1:10, ]

## ----inspect_colnames2--------------------------------------------------------
f_schema2 <- file_schema_dsv(input_file = data_file,
                             quote = "\"", na.strings = "",
                             id_quote_method = "SQL_SERVER",
                             comment.char = "", fileEncoding = "UTF-8")

f_schema2$schema[1:10, ]

## ----loaddata3----------------------------------------------------------------
dbTableFromDSV(input_file = data_file,
               dbcon = dbcon, table_name = "BLOCKCHAIN_BANKING",
               quote = "\"", na.strings = "",
               comment.char = "", fileEncoding = "UTF-8",
               id_quote_method = "SQL_SERVER",
               drop_table = TRUE)

## ----check_db2----------------------------------------------------------------
dbListTables(dbcon)

dbListFields(dbcon, "BLOCKCHAIN_BANKING")[1:8]

dbGetQuery(dbcon, "SELECT COUNT(*) AS n_records FROM BLOCKCHAIN_BANKING")

## ----close_db-----------------------------------------------------------------

dbDisconnect(dbcon)