Cacher is the code snippet organizer for pro developers

### loading from excel
install.packages("readxl")
library(readxl)
library(stats)
library(purrr)

###########

file_path ="//phsabc/root/BCCDC/Groups/Data_Surveillance/STI/Syphilis/Surveillance Monthly_syphistory Website Case and Screening Count/d_results"

files = list.files(path = file_path, pattern = ".xlsx" )
files <- files[ !grepl("^~", files) ]

### advice on stack exchange: https://stackoverflow.com/questions/51200887/how-to-import-multiple-sheets-from-multiple-excel-files-into-one-list-readxl-r


# option A
# load names of excel files 

file_path =""
#files = list.files(path = file_path, full.names = TRUE, pattern = ".xlsx") # cant load full path right away- need to remove ~$ temp file. 
files = list.files(path = file_path, full.names = FALSE, pattern = ".xlsx")
files <- files[ !grepl("^~", files) ]
files = paste0(paste0(file_path,"/", files))

# create function to read multiple sheets per excel file
read_excel_allsheets <- function(filename, tibble = FALSE) {
  sheets <- readxl::excel_sheets(filename)
  tibble_list <- lapply(sheets, function(sh) readxl::read_excel(filename, sheet = sh)) # does other bracket go here?
                        df_list <- lapply(tibble_list, as.data.frame)
                        names(df_list) <- sheets                 
                        
                        return(df_list)
}

# execute function for all excel files in "files"
all_data <- lapply(files, read_excel_allsheets)
# remove layer of list...
all_data = flatten(all_data)


rm(list = ls(pattern = "^file"))


#############################
# now clean data in list - pull out specific data we want in a loop or lapply
# note: these data show total tests 
test = all_data[1]

test = as.data.frame(test)