# Configuration file # --- User Configuration --- # Specify the path to your MDB file mdb_file_path <- "path/to/your/database.mdb" # Specify the name of the table you want to read from the MDB file target_table_name <- "YourTableName" # Specify columns for plotting (REPLACE with actual column names from your table) scatter_x_col <- "ColumnX" scatter_y_col <- "ColumnY" hist_col <- "ColumnZ" box_x_col <- "CategoryColumn" # Often a character or factor box_y_col <- "ValueColumn" # Often numeric # Output directory for plots plot_dir <- "plots" # --- End User Configuration --- # Create plot directory if it doesn't exist if (!dir.exists(plot_dir)) { dir.create(plot_dir, showWarnings = FALSE, recursive = TRUE) message("Created directory: ", plot_dir) } # Utility functions #' Install and load required packages #' #' Checks if packages are installed, installs them if necessary, and loads them. #' #' @param packages A character vector of package names. #' @return Invisible NULL. Stops execution if a package cannot be loaded. #' @examples #' \dontrun{ #' ensure_packages(c("ggplot2", "Hmisc")) #' } ensure_packages <- function(packages) { # Check which packages are not installed not_installed <- packages[!sapply(packages, requireNamespace, quietly = TRUE)] # Install missing packages if (length(not_installed) > 0) { message("Installing missing packages: ", paste(not_installed, collapse = ", ")) install.packages(not_installed, dependencies = TRUE) } # Load packages for (pkg in packages) { if (!require(pkg, character.only = TRUE, quietly = TRUE)) { stop("Failed to load package: ", pkg, ". Please install it manually.") } } invisible(NULL) } # Functions for loading data from MDB files #' List tables available in an MDB file #' #' Uses Hmisc::mdb.get to list tables. Requires 'Hmisc' package and potentially #' 'mdbtools' command-line utility on Linux/macOS. #' #' @param file_path Path to the MDB file. #' @return A character vector of table names. Returns NULL if the file doesn't exist #' or if tables cannot be listed. #' @export list_mdb_tables <- function(file_path) { if (!file.exists(file_path)) { warning("MDB file not found: ", file_path) return(NULL) } message("Attempting to list tables in: ", file_path) message("Note: This requires the 'Hmisc' package.") message("On Linux/macOS, 'mdbtools' might also need to be installed via your system's package manager (e.g., apt, brew).") tryCatch({ # Use mdb.get with no table specified to list tables tables <- Hmisc::mdb.get(file_path, tables = TRUE) message("Available tables: ", paste(tables, collapse = ", ")) return(tables) }, error = function(e) { warning("Failed to list tables from MDB file: ", file_path, "\nError: ", e$message) message("Troubleshooting tips:") message("- Ensure 'Hmisc' is installed.") message("- On Linux/macOS, ensure 'mdbtools' is installed and accessible.") message("- Check file path and permissions.") message("- For complex or newer MDB formats, consider using the 'RODBC' package with a configured ODBC connection (more setup required).") return(NULL) }) } #' Load a specific table from an MDB file #' #' Uses Hmisc::mdb.get to load a table into a data frame. #' #' @param file_path Path to the MDB file. #' @param table_name Name of the table to load. #' @return A data frame containing the table data. Returns NULL if loading fails. #' @export load_mdb_table <- function(file_path, table_name) { if (!file.exists(file_path)) { warning("MDB file not found: ", file_path) return(NULL) } message("Attempting to load table '", table_name, "' from: ", file_path) tryCatch({ data <- Hmisc::mdb.get(file_path, tables = table_name, lowernames = TRUE) message("Successfully loaded table '", table_name, "' with ", nrow(data), " rows and ", ncol(data), " columns.") # Optional: Convert column names to lowercase for consistency # names(data) <- tolower(names(data)) return(data) }, error = function(e) { warning("Failed to load table '", table_name, "' from MDB file: ", file_path, "\nError: ", e$message) message("Troubleshooting tips:") message("- Verify the table name is correct (case-sensitive depending on backend). Use list_mdb_tables() to check.") message("- Ensure 'Hmisc' is installed and 'mdbtools' (Linux/macOS) if needed.") message("- Check file path and permissions.") message("- Consider using 'RODBC' for potentially better compatibility.") return(NULL) }) } # Functions for creating plots #' Create and save a scatter plot #' #' @param data Data frame containing the data. #' @param x_col Name of the column for the x-axis. #' @param y_col Name of the column for the y-axis. #' @param file_name Name of the file to save the plot (e.g., "scatter.png"). #' @param plot_dir Directory to save the plot in. #' @return The ggplot object (invisibly). #' @export create_scatter_plot <- function(data, x_col, y_col, file_name = "scatter_plot.png", plot_dir = "plots") { if (!all(c(x_col, y_col) %in% names(data))) { warning("Columns '", x_col, "' or '", y_col, "' not found in data. Skipping scatter plot.") return(invisible(NULL)) } p <- ggplot2::ggplot(data, ggplot2::aes_string(x = x_col, y = y_col)) + ggplot2::geom_point(alpha = 0.7) + ggplot2::labs( title = paste("Scatter Plot of", y_col, "vs", x_col), x = x_col, y = y_col ) + ggplot2::theme_minimal() save_path <- file.path(plot_dir, file_name) ggplot2::ggsave(save_path, plot = p, width = 7, height = 5) message("Saved scatter plot to: ", save_path) return(invisible(p)) } #' Create and save a histogram #' #' @param data Data frame containing the data. #' @param col Name of the column for the histogram. #' @param file_name Name of the file to save the plot (e.g., "histogram.png"). #' @param plot_dir Directory to save the plot in. #' @param bins Number of bins for the histogram (default: 30). #' @return The ggplot object (invisibly). #' @export create_histogram <- function(data, col, file_name = "histogram.png", plot_dir = "plots", bins = 30) { if (!col %in% names(data)) { warning("Column '", col, "' not found in data. Skipping histogram.") return(invisible(NULL)) } p <- ggplot2::ggplot(data, ggplot2::aes_string(x = col)) + ggplot2::geom_histogram(bins = bins, fill = "skyblue", color = "black") + ggplot2::labs( title = paste("Histogram of", col), x = col, y = "Frequency" ) + ggplot2::theme_minimal() save_path <- file.path(plot_dir, file_name) ggplot2::ggsave(save_path, plot = p, width = 7, height = 5) message("Saved histogram to: ", save_path) return(invisible(p)) } #' Create and save a box plot #' #' @param data Data frame containing the data. #' @param x_col Name of the categorical column for the x-axis. #' @param y_col Name of the numerical column for the y-axis. #' @param file_name Name of the file to save the plot (e.g., "boxplot.png"). #' @param plot_dir Directory to save the plot in. #' @return The ggplot object (invisibly). #' @export create_box_plot <- function(data, x_col, y_col, file_name = "box_plot.png", plot_dir = "plots") { if (!all(c(x_col, y_col) %in% names(data))) { warning("Columns '", x_col, "' or '", y_col, "' not found in data. Skipping box plot.") return(invisible(NULL)) } # Ensure x-axis is treated as a factor/category if it's not already if (!is.factor(data[[x_col]]) && !is.character(data[[x_col]])) { message("Converting column '", x_col, "' to factor for box plot.") data[[x_col]] <- as.factor(data[[x_col]]) } p <- ggplot2::ggplot(data, ggplot2::aes_string(x = x_col, y = y_col)) + ggplot2::geom_boxplot() + ggplot2::labs( title = paste("Box Plot of", y_col, "by", x_col), x = x_col, y = y_col ) + ggplot2::theme_minimal() + # Rotate x-axis labels if there are many categories if (length(unique(data[[x_col]])) > 10) { ggplot2::theme(axis.text.x = ggplot2::element_text(angle = 45, hjust = 1)) } else { ggplot2::theme() } save_path <- file.path(plot_dir, file_name) ggplot2::ggsave(save_path, plot = p, width = 7, height = 5) message("Saved box plot to: ", save_path) return(invisible(p)) } # Main script to load data from MDB and create plots # --- Setup --- # Source configuration and functions source("config.R") source("R/utils.R") source("R/data_loader.R") source("R/plotting.R") # Ensure required packages are installed and loaded # Hmisc is needed for mdb.get, ggplot2 for plotting required_packages <- c("Hmisc", "ggplot2") ensure_packages(required_packages) # --- Pre-check --- # Verify MDB file exists before proceeding if (!file.exists(mdb_file_path)) { stop("MDB file specified in config.R not found: ", mdb_file_path, "\nPlease update config.R with the correct path.") } # --- List Tables (Optional but Recommended) --- message("\n--- Listing Tables ---") available_tables <- list_mdb_tables(mdb_file_path) if (is.null(available_tables)) { message("Could not list tables. Please check previous warnings/messages.") message("Ensure the 'target_table_name' in config.R ('", target_table_name, "') is correct.") } else if (!target_table_name %in% available_tables) { warning("Target table '", target_table_name, "' not found in the MDB file. Available tables: ", paste(available_tables, collapse=", "), ". Please update config.R.") # Optionally stop execution if the target table is essential # stop("Execution halted: Target table not found.") } # --- Load Data --- message("\n--- Loading Data ---") # IMPORTANT: Update target_table_name in config.R if needed mdb_data <- load_mdb_table(mdb_file_path, target_table_name) # --- Process and Plot Data --- if (!is.null(mdb_data)) { message("\n--- Data Summary ---") print(str(mdb_data)) print(summary(mdb_data)) message("\n--- Creating Plots ---") message("IMPORTANT: Ensure column names in config.R (scatter_x_col, scatter_y_col, etc.) match actual columns in your data.") # Example Plots (using column names from config.R) # 1. Scatter Plot create_scatter_plot( data = mdb_data, x_col = scatter_x_col, y_col = scatter_y_col, plot_dir = plot_dir ) # 2. Histogram create_histogram( data = mdb_data, col = hist_col, plot_dir = plot_dir ) # 3. Box Plot create_box_plot( data = mdb_data, x_col = box_x_col, y_col = box_y_col, plot_dir = plot_dir ) message("\n--- Plotting Complete ---") message("Plots saved in directory: ", plot_dir) } else { message("\n--- Skipping Plotting ---") message("Data loading failed. Cannot create plots. Check previous warnings/messages.") } message("\n--- Script Finished ---") # R Project: Read MDB and Create Plots This project provides R scripts to read data from a Microsoft Access Database (.mdb) file and generate basic plots using `ggplot2`. ## Prerequisites 1. **R:** Ensure you have R installed (version 4.0+ recommended). 2. **R Packages:** The script will attempt to automatically install the required R packages: * `Hmisc`: Used for reading MDB files via `mdb.get()`. * `ggplot2`: Used for creating plots. 3. **System Dependencies (Potentially):** * **`mdbtools` (Linux/macOS):** The `Hmisc::mdb.get()` function often relies on the `mdbtools` command-line utility on non-Windows systems. You may need to install it using your system's package manager: * Debian/Ubuntu: `sudo apt-get update && sudo apt-get install mdbtools` * macOS (using Homebrew): `brew install mdbtools` * **ODBC Driver (Windows/Alternative):** If `Hmisc::mdb.get()` fails (e.g., on Windows, or with newer `.accdb` files or complex MDBs), you might need to use the `RODBC` package instead. This requires setting up an ODBC data source for your Access database through the Windows ODBC Data Sources administrator. This setup is more involved and not covered by default in these scripts. ## Setup 1. **Configure:** Open the `config.R` file and modify the following variables: * `mdb_file_path`: Set this to the full path of your `.mdb` file. * `target_table_name`: Set this to the exact name of the table you want to read from the database. * `scatter_x_col`, `scatter_y_col`, `hist_col`, `box_x_col`, `box_y_col`: **CRITICAL:** Replace these placeholder names with the actual column names from your `target_table_name` that you want to use for plotting. Check the output of `str(mdb_data)` in the R console after running the script if you are unsure of the exact names (R might convert them, e.g., to lowercase or replace spaces with dots). * `plot_dir`: The directory where generated plots will be saved (defaults to "plots"). ## How to Run 1. Open your R console or RStudio. 2. Set the working directory to the folder containing `main.R`, `config.R`, etc. R # Example: setwd("/path/to/your/project/folder") setwd("your_project_directory") 3. Run the main script: ```R source("main.R") ``` ## Output * The script will print messages to the console, including: * Package installation status. * Attempted file/table loading. * A summary (`str` and `summary`) of the loaded data frame (if successful). * Paths where plots are saved. * Plot image files (e.g., `scatter_plot.png`, `histogram.png`, `box_plot.png`) will be saved in the directory specified by `plot_dir` in `config.R` (default is `plots/`). ## Troubleshooting * **File Not Found:** Double-check the `mdb_file_path` in `config.R`. Ensure R has read permissions for the file and its directory. * **Table Not Found:** Verify the `target_table_name` in `config.R` is exactly correct (case sensitivity might matter). Run `source("main.R")` once; it attempts to list available tables which can help confirm the name. * **`Hmisc::mdb.get` Fails:** * Ensure `Hmisc` is installed. * On Linux/macOS, ensure `mdbtools` is installed and in the system's PATH. * The MDB file might be corrupted, password-protected (not supported by `mdb.get`), or in a newer format (`.accdb`) not fully supported by `mdbtools`. * Consider the `RODBC` alternative if `mdb.get` consistently fails. * **Plotting Errors / Warnings:** Ensure the column names specified in `config.R` (`scatter_x_col`, etc.) exist in the loaded data frame. Check the `str(mdb_data)` output printed to the console for the exact column names R is using. ```