[SOLVED] Copy Multiple data frames to SQLite db in R

Copy Multiple data frames to SQLite db in R

I have ~ 250 csv files I want to load into SQLite db. I've loaded all the csv into my global environment as data frames. I'm using the following function to copy all of them to db but get Error: df must be local dataframe or a remote tbl_sql

library(DBI)
library(odbc)
library(rstudioapi)
library(tidyverse)
library(dbplyr)
library(RSQLite)
library(dm)


# Create DB Instance ---------------------------------------------

my_db <- dbConnect(RSQLite::SQLite(), "test_db.sqlite", create = TRUE)

# Load all csv files ---------------------------------------------

filenames <- list.files(pattern = ".*csv")
names <- substr(filenames, 1, nchar(filenames)-4)

for (i in names) {
  filepath <- file.path(paste(i, ".csv", sep = ""))
  assign(i, read.csv(filepath, sep = ",")) 
}

# Get list of data.frames ----------------------------------------

tables <- as.data.frame(sapply(mget(ls(), .GlobalEnv), is.data.frame))

colnames(tables) <- "is_data_frame"

tables <- tables %>% 
  filter(is_data_frame == "TRUE")

table_list <- row.names(tables)

# Copy dataframes to db ------------------------------------------
for (j in table_list) {
  copy_to(my_db, j)
}

Solution

I have had mixed success using copy_to. I recommend the dbWriteTable command from the DBI package. Example code below:

DBI::dbWriteTable(
    db_connection,
    DBI::Id(
        catalog = db_name,
        schema = schema_name,
        table = table_name
    ),
    r_table_name
)

This would replace your copy_to command. You will need to provide a string to name the table, but the database and schema names are likely optional and can probably be omitted.