Now I used the xslx2csv tool which I saw in other answers of similar questions on stackoverflow and run the code: xlsx2csv --all file.xlsx in my terminal. But all that does is print all the sheets of the xlsx file as a csv in my terminal instead of creating separate csv files in for each sheet my current directory.
This should be simple but I can't find simple answers..
How do I solve this? All help much appreciated!
Since you seem to be using R, here is an R-based solution. You need the {readxl}, {purrr}, and {readr} packages. I am “namespacing” all functions, so you know, where they are from. I am using the example Excel file from {readxl}.
path_to_xlsx <- readxl::readxl_example("datasets.xlsx")
This Excel file has 4 sheets. The names of the sheets are read by excel_sheets.
sheet_names <- readxl::excel_sheets(path_to_xlsx)
Now we import all excel sheets into one list.
sheets <- purrr::map(sheet_names, ~ readxl::read_excel(path_to_xlsx, sheet = .x))
We get a list of 4 data.frames or tibbles. Let’s name them.
base::names(sheets) <- sheet_names
Now export all tibbles from the list to separate CSVs in one go.
purrr::iwalk(sheets, ~ readr::write_excel_csv2(x = .x, path = paste0(.y, ".csv")))
list.files(pattern = "\\.csv$")
#> [1] "chickwts.csv" "iris.csv" "mtcars.csv" "quakes.csv"
