Upload multiple files to database using purrr.

[williaml]

How can I upload multiple files to a database using purrr?

I have tried this, but

# test directory - simulates the files
csv_file_names <- c("a.csv", "b.csv", "c.csv")

# filenames loaded in to the environment
names_in_env <-  csv_file_names %>% 
    str_remove(".csv")

# add the preliminary suffix to file names for upload
filenames_with_suffix <- csv_file_names %>%
  str_remove(".csv") %>%
  paste0("_preliminary")

# upload to database
map2(filenames_with_suffix, names_in_env,
     function(x, y) {
       dbWriteTable(CON, x, y)
       }
     )

The errors:

Error during wrapup: unable to find an inherited method for function ‘dbWriteTable’ for signature ‘"PqConnection", "character", "character"’
Error: no more error handlers available (recursive errors?); invoking 'abort' restart

I assume it is something with the names_in_env as these should be unquoted. I have tried {{y}} and that didn't work.

And using !!y or !!!y produces this error:

Error during wrapup: error in evaluating the argument 'value' in selecting a method for function 'dbWriteTable': invalid argument type

[nirgrahamuk]

DB write table wants to write values / y, which are dataframes to the name/x. Ok, you gave table names in x , but you just give other names for y rather than dataframes. At some point you need to read the csv and have that as a dataframe in order to write it out to a db

[williaml]

Sorry, I should have mentioned that the csv files are already in the environment.

csv_file_names %>%
  purrr::map(function(file_name){ # iterate through each file name
    assign(x = str_remove(file_name, ".csv"), # Remove file extension ".csv"
           value = read_csv(paste0(file_path, file_name)) %>% 
             clean_names(),
           envir = .GlobalEnv)
  })

[nirgrahamuk]

Ok, but still, when you use dbwritetable it should be dataframes being passed than the names of dataframes. Maybe get() the frame from the name

[williaml]

Thanks. That worked.

# upload to database
map2(filenames_with_suffix, names_in_env,
     function(x, y) {
       dbWriteTable(CON, x, get(y))
       }
     )