How can I open multiple excel dataset from a folder and combine them in a unique dataset in R?

user_18541166 0
Viewed 28

I have a folder with multiple files, some of them are .xlsx and other are pdf. and each file has a different name but have the same information. My excel files contain dataset that I need to work on but I have about 150 different files. I want to know how to open all the excel files and combine them in a unique and new excel file. But, there is a tricky part, I also need to open an specific sheet from each excel file. This is the command I've been using:

episour_data <- list.files(path = "C:/Users/mvergara/ownCloud/Matias/donnees/21-11_carnet_appro/Carnets Episourire/",    
                       pattern = "*.xlsx",
                       full.names = TRUE) %>% 
  lapply(read_excel (sheet = "SAISIE", startRow = 8 )) %>%                                           
  bind_rows                                                      
episour_data

I also tried:

dir("C:/Users/mvergara/ownCloud/Matias/donnees/21-11_carnet_appro/Carnets Episourire/", full.names = T) %>% 
  map_df(read_excel)

But neither of them work.

I also tried a loop:

wd <- "C:/Users/mvergara/ownCloud/Matias/donnees/"
campaign <- "21-11"
metadata <- read.xlsx(paste0(wd,campaign,"_questionnaire/21-11_metadata.xlsx"))
meta <- tibble(groupe = NA, aliment = NA, produit = NA, poids = NA, unites = NA, prix = NA, lieu = NA)
for (i in 1:nrow(metadata)) {
  epi <- metadata$epicerie[i]
  id <- metadata$no[i]
  data <- read.xlsx(paste0(wd,campaign,"_carnet_appro/Carnets ",epi,"/",id,"_",epi,".xlsx"), sheet = "SAISIE", startRow = 8)
  meta <- bind_rows(meta, data[, 3:9])

Nothing seems to work, help please

r
1 Answers

Try the following. Both solutions use package readxl.

Base R with lapply

library(readxl)

so_dir <- "./Temp"

list.files(path = so_dir, pattern = "\\.xlsx", full.names = TRUE) |>
  (\(x) lapply(x, read_excel, sheet = "SAISIE", skip = 7))() |>
  (\(x) do.call(rbind, x))()
## A tibble: 6 x 2
#      a     b
#  <dbl> <dbl>
#1     4     7
#2     5     8
#3     6     9
#4     1     4
#5     2     5
#6     3     6

Base R for loop

xl_files <- list.files(path = so_dir, pattern = "\\.xlsx", full.names = TRUE)

meta <- vector("list", length = length(xl_files))
names(meta) <- tools::file_path_sans_ext(basename(xl_files))
for(x in xl_files){
  i <- tools::file_path_sans_ext(basename(x))
  meta[[i]] <- read_excel(x, sheet = "SAISIE", skip = 7)
}
do.call(rbind, meta)
## A tibble: 6 x 2
#      a     b
#  <dbl> <dbl>
#1     4     7
#2     5     8
#3     6     9
#4     1     4
#5     2     5
#6     3     6

tidyverse with purrr::map_dfr.

library(tidyverse)
library(readxl)

list.files(path = so_dir, pattern = "\\.xlsx", full.names = TRUE) %>%
  map_dfr(read_excel, sheet = "SAISIE", skip = 7, .id = "data")
## A tibble: 6 x 3
#  data      a     b
#  <chr> <dbl> <dbl>
#1 1         4     7
#2 1         5     8
#3 1         6     9
#4 2         1     4
#5 2         2     5
#6 2         3     6
user_82454060
Related