Extracting Excel sheets into csv

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!

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
1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

Thank you @toryn_stat!