Upload multiple files to database using purrr.

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

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

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)
  })

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

1 Like

Thanks. That worked.

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

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.