Extracting Excel sheets into csv

tidyverse
1

I have an excel workbook that contains some sheets. Few of these sheets are very similar, but some are not. I am trying to learn better practice of using csv instead. I was trying to read these as csv as below:

path <- "Data/df.xlsx"
read_then_csv <- function(sheet, path) {
pathbase <- path %>%
basename() %>%
tools::file_path_sans_ext()
path %>%
read_excel(sheet = sheet) %>%
write_csv(paste0(pathbase, "-", sheet, ".csv"))
}

file <- path%>%
excel_sheets() %>%
set_names() %>%
map(read_then_csv, path = path)

All of these sheets are read properly. However, my concerns are:

  1. They are automatically saved in the main directory. I would like for them to be not saved at all or if they have to be, then under Data folder as seen in the path.

  2. I would also like to go to each sheet and wrangle it. But not sure how to manipulate each sheet separately.

Thanks for your help!

2

The sheets are being saved because of the write_csv function in read_then_csv. You can remove it to not save the csv.

path <- "Data/df.xlsx"
read_then_csv <- function(sheet, path) {
pathbase <- path %>%
basename() %>%
tools::file_path_sans_ext()
path %>%
read_excel(sheet = sheet) 
}

For the next question, file is a list of tibbles. You can access certain elements of file by pluck, list indexing ($ name or [[ position ]]), or you can wrangle with a map_at using names or positions:

sheet1 <- file %>%
    pluck(1) # this saves the first sheet as the tibble called sheet1

sheet1 <- file[[1]] # this also saves the first sheet as the tibble called sheet1

file <- file %>%
    map_at(1, ~ .x %>% mutate(row = row_number())) # adds a column row to sheet 1, but saves changes within the list file
3

Thank you @toryn_stat!