Import multiple Excel files with names in R as a list

Question

Consider I have two Excel files in my subdirectionary:

.../Myfolder/File1.xlsx
.../Myfolder/File2.xlsx

I know that I can read them into R as a list using the following formular:

data      <- list.files(path = "./Myfolder/", pattern="*.xlsx", full.names = T)
data.list <- lapply(data, read_excel)

However, I want to name my objects in the list according to the file name. That is, the first objects name shall be "File1" and the second one should be "File2". I can use:

names(data.list) <- data

But then I get the full name (because I use full.names = T).

Answer 1

You can do :

names(data.list) <- sub('\\.xlsx', '', basename(data))

Or without any regex :

names(data.list) <- tools::file_path_sans_ext(basename(data))

Answer 2

This is what you're asking.

library(tidyverse)
library(stringr)
library(readxl)

(list.files('folder_with_sheets') %>%
    keep(~ str_detect(.x, '.xlsx')) %>%
    set_names(.) %>%
    map(read_excel) ->
    data)

But supposing they all have the same columns in each:

library(tidyverse)
library(stringr)
library(readxl)

(list.files('folder_with_sheets') %>%
    keep(~ str_detect(.x, '.xlsx')) %>%
    map_dfr(~ read_excel(.x) %>% mutate(sheet = .x)) ->
    data)

Supposing they all share an identification column and represent different data about the same individuals:

library(tidyverse)
library(stringr)
library(readxl)

(list.files('folder_with_sheets') %>%
    keep(~ str_detect(.x, '.xlsx')) %>%
    map(read_excel) %>%
    reduce(left_join) -> # or reduce(~ left_join(.x, .y, by = 'key_variable_name')
    data)

Either way, with set_names you can pipe in name assignment, which is preferable to having two expressions, one to create data, other to label it.

P.S:

This is how I'd do it nowadays:

library(tidyverse)
library(readxl)
library(fs)

fs::dir_ls(
  path = "folder/",
  glob = "*.xlsx") %>%
  purrr::set_names(
    x = purrr::map(., readxl::read_excel),
    nm = .)

# or maybe within a tibble?

tibble::tibble(
  path = fs::dir_ls(
    path = "folder/",
    glob = "*.xlsx"),
  data = purrr::map(path, readxl::read_excel))

Answer 3

I had to modify with. However, it does keep the final path name extension in the list names, which I don't like.

(list.files(path = 'filepath ', pattern = "\\.xlsx$", full.names = TRUE) %>%
    keep(~ str_detect(.x, '\\.xlsx$')) %>%
    set_names(.) %>%
    map(read_excel) ->
    data)