0

Trying to write an R script that will convert multiple xlsx workbook files within a folder while also converting the sheets within the workbook as separate csv files. Looking for a single script to automatically apply code to all workbooks and their spreadsheets.

Rubén
  • 34,714
  • 9
  • 70
  • 166
NikkiBeaudoin
  • 1
  • 1
  • 1
  • Please see [How to make a great reproducible example in R](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) and update your question. – Mako212 Oct 10 '19 at 21:07

4 Answers4

0

For reading Excel files, there are several packages.

I personally am happy with the xlsx package, which you can use to read Excel files, as well as their individual sheets. This article looks like it will give you the gist of it.

Each worksheet you read out you should then be able to export to CSV files by using R's built-in write.csv (or write.csv2) method.

matt_jay
  • 1,241
  • 1
  • 15
  • 33
0

Below is an example to convert a single xlsx workbook to multiple csv files. Note that type conversions are not guaranteed to be correct.

xlsx_path <-"path_to_xlsx.xlsx"
sheet_names <- readxl::excel_sheets(xlsx_path)
# read from all sheets to a list of data frames
xlsx_data <- purrr::map(
    sheet_names,
    ~readxl::read_excel(xlsx_path,.x,col_types = "text",col_names = FALSE)
)
# write a list of data frame to csv files
purrr::walk2(
    xlsx_data,sheet_names,
    ~readr::write_csv(.x,paste0(xlsx_path,"-",.y,".csv"),col_names = FALSE)
)

# csv files will be saved as:
# path_to_xlsx-sheet1.xlsx, path_to_xlsx-sheet2.xlsx, ...

If you need to apply this function to many xlsx files. Use list.files() to get the path to all xlsx files. And write a for loop or use another map function to iterate this process.

yusuzech
  • 5,896
  • 1
  • 18
  • 33
  • Thank you so much for your help! Would you mind including where I would be inputting my own data? Today is honestly my first day of coding and I am very lost! – NikkiBeaudoin Oct 10 '19 at 23:44
  • As you mentioned it's your first day coding, you could need more guidance than a stack overflow answer. Please read @Alex 's answer as he included a tutorial, which should be very helpful to you. After you understand the basic workflows, you will find the answers here make more sense. – yusuzech Oct 10 '19 at 23:54
  • Just looking into that now! Of course stumbled across another array of errors but making process. Thanks! – NikkiBeaudoin Oct 11 '19 at 00:02
0

If you are using Rstudio it is possible that you already have the package readxl installed. They have many workflows for common usecases explained here: https://readxl.tidyverse.org/articles/articles/readxl-workflows.html

They also provide this nice code snippet to do what you are asking for:

read_then_csv <- function(sheet, path) {
  pathbase <- tools::file_path_sans_ext(basename(path))
  df <- read_excel(path = path, sheet = sheet)
  write.csv(df, paste0(pathbase, "-", sheet, ".csv"),
            quote = FALSE, row.names = FALSE)
  df
}
path <- readxl_example("datasets.xlsx")
sheets <- excel_sheets(path)
xl_list <- lapply(excel_sheets(path), read_then_csv, path = path)
names(xl_list) <- sheets
Alex
  • 106
  • 5
-2

If you go to here and put "excel" and "xls" in the search bar, you 'll get a list of packages and functions which might help.

sinecospi
  • 47
  • 2