Convert Excel file to CSV

This uses the workflow in here: https://readxl.tidyverse.org/articles/articles/readxl-workflows.html

This is preferred to converting Excel files by hand to avoid human error.

If files are in the visitor folder you might need to be complete the following while logged into visitor

Load the necessary packages

library(magrittr)
library(readxl)
library(readr)

Get a list of files that we want to convert

xlsx <- dir("directory/of/files/here", pattern = "*xls", recursive = T, full.names = T)

Create a function that iterates through the excel files and saves it as a csv in the same folder the excel file was in

read_then_csv <- function(sheet, path) {
  #remove the file extension
  pathbase <- path %>%
    tools::file_path_sans_ext()
  
  #change file extension to csv and save with the sheet name
  for(i in 1:length(sheet)){
    path %>%
      read_excel(sheet = i) %>%
      write_csv(paste0(pathbase, "_", sheet[i], ".csv")) 
  }
  
}

Get the sheet name(s)

sheets <- xlsx %>%
  map(excel_sheets) %>%
  set_names()

Iterate over all the xlsx files for each of the sheets

df <- map2(sheets, xlsx, ~read_then_csv(.x, path = .y))

Check the files afterwards to make sure everything was converted properly

csv <- dir("directory/of/files/here", pattern = "*csv", recursive = T, full.names = T)

Check to see if there are any empty tables and get a list of files that were empty

files <- map(csv, read_csv)
n <- which(map(files, nrow) == 0)

Remove those empty table files

lapply(csv[n], file.remove)

Remove xlsx files if everything looks in order

lapply(xlsx, file.remove)