Spaces:
Running
Running
Download force_load.R from phantomas34/perfect-margin: direct link, hf CLI and curl.
- Browser
- Download file 2.84 kB
-
https://huggingface.co/spaces/phantomas34/perfect-margin/resolve/main/force_load.R
- Command line
-
hf download hf://spaces/phantomas34/perfect-margin/force_load.R
-
curl -L -o force_load.R https://huggingface.co/spaces/phantomas34/perfect-margin/resolve/main/force_load.R
2.84 kB
| library(DBI) | |
| library(RPostgres) | |
| library(tidyverse) | |
| library(lubridate) | |
| library(readr) | |
| # --- 1. SETTINGS --- | |
| CSV_FILENAME <- "Bid_SalesAnalysisByEstimatorByMonth_121125.csv" | |
| DB_PORT <- 5433 | |
| print("--- DIAGNOSTIC START ---") | |
| # --- 2. CHECK FILE EXISTENCE --- | |
| # This checks if RStudio is looking in the right folder | |
| if (file.exists(CSV_FILENAME)) { | |
| print(paste("✅ SUCCESS: Found file:", CSV_FILENAME)) | |
| } else { | |
| print(paste("❌ ERROR: Could not find file:", CSV_FILENAME)) | |
| print(paste(" R is currently looking in:", getwd())) | |
| stop("Stoppping script. Please move the CSV to the folder listed above.") | |
| } | |
| # --- 3. CONNECT TO DATABASE --- | |
| tryCatch({ | |
| con <- dbConnect(RPostgres::Postgres(), | |
| dbname = "sales_db", | |
| host = "localhost", | |
| port = DB_PORT, | |
| user = Sys.info()[["user"]], | |
| password = "") | |
| print("✅ SUCCESS: Connected to Database.") | |
| }, error = function(e) { | |
| print("❌ ERROR: Could not connect to Database. Make sure Postgres.app is running.") | |
| stop(e) | |
| }) | |
| # --- 4. LOAD DATA --- | |
| print("--- Attempting Data Load ---") | |
| raw_data <- read_csv(CSV_FILENAME, show_col_types = FALSE) | |
| clean_data <- raw_data %>% | |
| mutate(report_date = ym(MoYear)) %>% | |
| mutate(across(c(AmtBid, AmtBooked), ~ as.numeric(gsub("[\\$,]", "", .)))) %>% | |
| mutate(clean_name = case_when( | |
| Estimator == "Scott W. Hutchings" ~ "Scott Hutchings", | |
| Estimator == "SW" ~ "Scott Hutchings", | |
| Estimator == "SH" ~ "Scott Hutchings", | |
| TRUE ~ Estimator | |
| )) | |
| # Insert Estimator Names | |
| unique_estimators <- unique(clean_data$clean_name) | |
| count_new_names <- 0 | |
| for(name in unique_estimators){ | |
| exists <- dbGetQuery(con, "SELECT id FROM estimators WHERE primary_name = $1", params = list(name)) | |
| if(nrow(exists) == 0){ | |
| dbExecute(con, "INSERT INTO estimators (primary_name) VALUES ($1)", params = list(name)) | |
| count_new_names <- count_new_names + 1 | |
| } | |
| } | |
| print(paste(" Processed Estimators. Added", count_new_names, "new names.")) | |
| # Insert Sales Data | |
| est_map <- dbGetQuery(con, "SELECT id, primary_name FROM estimators") | |
| upload_ready <- clean_data %>% | |
| left_join(est_map, by = c("clean_name" = "primary_name")) %>% | |
| select(estimator_id = id, report_date, amt_bid = AmtBid, qty_bid = QtyBid, | |
| amt_booked = AmtBooked, qty_booked = QtyBooked) | |
| # Write to DB | |
| tryCatch({ | |
| dbWriteTable(con, "monthly_sales", upload_ready, append = TRUE, row.names = FALSE) | |
| print(paste("✅ SUCCESS: Uploaded", nrow(upload_ready), "sales records.")) | |
| }, error = function(e) { | |
| print("⚠️ NOTE: Data write skipped (likely duplicate data).") | |
| }) | |
| # --- 5. FINAL VERIFICATION --- | |
| row_count <- dbGetQuery(con, "SELECT count(*) FROM monthly_sales") | |
| print(paste("--- FINAL DATABASE STATUS: The table now has", row_count, "rows. ---")) | |
| dbDisconnect(con) | |