Spaces:
Running
Running
File size: 1,748 Bytes
0480ef8 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 | library(tidyverse)
library(lubridate)
library(DBI)
library(RPostgres)
# 1. Connect to DB
con <- dbConnect(RPostgres::Postgres(), dbname = "sales_db",
host = "localhost", port = 5433,
user = Sys.info()[["user"]], password = "")
# 2. Read and Clean CSV
raw_data <- read_csv("Bid_SalesAnalysisByEstimatorByMonth_121125.csv", 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
))
# 3. Update Estimators Table
unique_estimators <- unique(clean_data$clean_name)
for(name in unique_estimators){
# Check if exists, if not, insert
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))
}
}
# 4. Upload 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 (Append mode)
tryCatch({
dbWriteTable(con, "monthly_sales", upload_ready, append = TRUE, row.names = FALSE)
print("Data Successfully Uploaded to Port 5433!")
}, error = function(e) {
print("Data likely already exists. (Duplicate key error expected if running twice).")
})
dbDisconnect(con) |