R/from_xlsx_to_project_csv.R

In patelm9/projektoR:

#' Converts all the xlsx files in a dir/NEW folder to csv files in the same folder and removes the XLSX file
#' @param xlsx_fn full path to xlsx filename
#' @param dir_name name of the dir
#' @importFrom openxlsx getSheetNames
#' @importFrom  openxlsx read.xlsx
#' @export

from_xlsx_to_project_csv <-
        function(xlsx_fn, path_folder) {

                ##Setting up other variables
                xlsx_bn <- basename(xlsx_fn)
                xlsx_name <- mirroR::strip_fn(xlsx_fn)
                xlsx_full_fn <- paste0("./", path_folder, "/", xlsx_bn)

                dir_name <- dirname(path_folder)

                ##Creating log information
                log_csv_full_fn <- paste0(dir_name, "/", tolower(dir_name), "_log.csv")

                tabnames <- openxlsx::getSheetNames(xlsx_full_fn)
                for (i in 1:length(tabnames)) {
                        tab    <- tabnames[i]
                        x      <- openxlsx::read.xlsx(xlsx_full_fn, sheet = tab) %>%
                                        somersaulteR::call_mr_clean()

                        csv_basename <- paste0(xlsx_name, "_", tab)

                        projektoR::write_project_csv(x,
                                                     csv_basename = csv_basename,
                                                     timestamp = FALSE,
                                                     path_folder = path_folder
                        )

                        projektoR::append_csv(log_csv_full_fn,
                                        data.frame(LOAD_TIMESTAMP            = "",
                                                   LOAD_COMMENT              = "",
                                                   PARENT_FN                 = xlsx_fn,
                                                   SOURCE_COMMENT            = "",
                                                   FN                        = paste0(path_folder, "/",csv_basename, ".csv"),
                                                   FN_MD5SUM                 = tools::md5sum(paste0(path_folder, "/",csv_basename, ".csv")),
                                                   LOG_ID                    = "",
                                                   ROBJ_NAME                 = "",
                                                   RDATA_FN                  = "",
                                                   RDATA_FN_MD5SUM           = "",
                                                   LOG_TIMESTAMP             = "",
                                                   LOG_COMMENT               = "",
                                                   WRITE_TIMESTAMP           = mirroR::get_timestamp(),
                                                   WRITE_COMMENT             = "",
                                                   WRITE_FULL_FN             = "",
                                                   WRITE_FULL_FN_MD5SUM      = "",
                                                   TO_DB_TIMESTAMP           = "",
                                                   TO_DB_COMMENT             = "",
                                                   TO_DB_NAME                = "",
                                                   TO_DB_SCHEMA              = "",
                                                   TO_DB_TABLE               = "",
                                                   FROM_DB_TIMESTAMP         = "",
                                                   FROM_DB_COMMENT           = "",
                                                   FROM_DB_NAME              = "",
                                                   FROM_DB_SCHEMA            = "",
                                                   FROM_DB_TABLE             = "") %>%
                                                        dplyr::mutate_all(as.character) %>%
                                                        dplyr::mutate_all(trimws, "both") %>%
                                                        dplyr::mutate(LOG_ID = ifelse(nchar(LOG_ID) == 1, paste0("0", LOG_ID), LOG_ID))
                                        )

                }
                file.remove(xlsx_fn)
        }