There are a few changes I'm recommending:
Your function is not named, so while it is defined, it is immediately discarded and not available. Store it with a name (I'll use Load_Data_func just to differentiate it from the Load_Data.R file and the function you thought you had, feel free to rename it) so that it can persist and be used.
Your function is breaching scope by assuming that the data it needs is already created in a parent environment; it's typically much better (safer, recommended, etc) to pass its data to it explicitly. The function also returns something ... a static string, which you then store into a reactive-value, which seems unnecessary. I'd think this function should return either the data itself or something to indicate that it was correctly stored in the database.
I'll go one (big) step forward here: the dbWriteTable thing seems a little fragile. As it is now, the default arguments are append=FALSE, overwrite=FALSE; if the table exists, then this will error. You have two options here, I'm going to implement #1:
- Add
append=TRUE; you may choose overwrite=TRUE instead if you do not intend to persist data, purging the previous data each time.
- Create a randomized table name and return that table name from the function. If you go this route, then you will need to use a
reactiveVal to store the table name separately from the data returned. (This option is not demonstrated here.)
I'm generalizing the function a little so that it is responsible for using readxl. The assumed intent of the function is to do something with the data, i.e., store it in the database. Whether the data is passed explicitly (perhaps you would want sample data available, chosen elsewhere in the shiny interface) or passed as a file (which is the default), the function should do that part. My change here is to remove the file-reading from the main shiny file, instead just passing the path/filename. (Note that the function now really should be called with all arguments named, not just positional arguments. Not strictly required, but for clarity it makes sense.)
Read in the data into its own reactive, and then use that reactive within the output$contents block (and optionally anywhere you want the raw data and do not intent to "query" using DBI).
The observeEvent on the button press is out of place; I think the initial data read should be dependent on the button press, not on changing the filename, since it appears you don't want to read in the file until the button press. For this, I'm changing the reactive flow a bit.
Under the assumption that in addition to a Load_Data.R file, you may have other files that do things with the data ... I've moved most library loading to the main file. (The exception being readxl, which is only needed by Load_Data_func, so it remains in Load_Data.R. It doesn't hurt to move it to server.R if you want.)
I relocated the definition of the sqlite database file location to the top-level (within server.R), since in my eyes the function should not hard-code things like that.
Again, assuming that you will be doing more things with the sqlite database than just dump data into it, you have two options:
- Store the database connection object (your name
BD_CA_IDAAN) in the main server.R file so that all other functions/reactives (not included here) can have access to the already-opened database; or
- Open it, store the data, then close it immediately. My version of your function will support both: either pass the database connection object (and the data will be appended, database connection not closed), or pass the filename and the db will be opened, data appended, and db closed within the function.
Optional: I don't know if the sqlite database is intended just for permanent storage of the data, or if you intend to run SQL queries against it. If the latter, than you might not need to use mydata(), though much of the shiny flow here should still remain. (If you intend to use all R functions for subsetting/reshaping/recalculating/... on mydata(), then keep mydata() as-is and work with it as a frame.)
## ui.R
navbarPage(
"Ingreso de Data a Base de Datos",
fileInput('file1', 'Choose xlsx file',
accept = c(".xlsx")
),
actionButton("Load_DB_Button",
"Load Data",
style = "bordered",
width = "100%"),
mainPanel(
tableOutput('contents')
)
)
## server.R
library(RSQLite)
library(dplyr)
Load_Data <- source("Load_Data.R")
BD_CA_IDAAN <- "C:/Users/CBarrios/Desktop/Ex_Files_Data_Apps_R_Shiny/Exercise Files/07_03/Database/DB_CA_IDAAN.db"
### Optional:
# BD_CA_IDAAN <- DBI::dbConnect(RSQLite::SQLite(), BD_CA_IDAAN)
function(input, output, session) {
mydata <- eventReactive(input$Load_DB_Button, {
req(input$file1$datapath) # this is isolated
dat <- Load_Data_func(path = input$file1$datapath, sheet = 1, db = BD_CA_IDAAN)
showModal(modalDialog("Calculation Finished!"))
dat
})
output$contents <- renderTable({
req(mydata())
})
}
## Load_Data.R
library(readxl)
Load_Data_func <- function(path, sheet = 1, db, data) {
message("Running Code")
if (missing(data)) {
data <- readxl::read_excel(path, sheet = sheet)
}
if (is.character(db)) {
# we were passed a file path
db <- DBI::dbConnect(RSQLite::SQLite(), db)
on.exit({ DBI::dbDisconnect(db); }, add = TRUE)
}
# this either returns 'TRUE' or it fails with an error, nothing else
dbWriteTable(db, "Test Table", data, append = TRUE)
}
Caveat: I really haven't tested this extensively.
