CODEWITHSUNDEEP
r
nik
nik

Reputation: 2584

Load multiple excel files and name object after a file name

I have read several questions related to this but none is what I am looking for.

The best one is by far using the readxlpackage 

library(readxl)
file.list <- list.files(pattern='*.xlsx')
df.list <- lapply(file.list, read_excel)

but as it is explained, it gives a list. what I want is to get each file by their name in work directory

what I am doing is to get setwdinto the directory I have all the xls files then I load them one by one based on their name for example 

mydf1 <- read_excel("mydf1.xlsx")
mydfb <- read_excel("mydfb.xlsx")
datac <- read_excel("datac.xlsx")

Is there any other way to get them without repeating the name over and over again?

Upvotes: 1

Views: 1483

Answers (5)

Aran
Aran

Reputation: 145

Building on the purrr approach by @SethRaithel, this provides column with the file names.

library(tidyverse)
library(readxl)

# create a list of files matching a regular expression
# in a defined path
file_list <- fs::dir_ls(temp_path, regexp="*.xls")

data_new <- file_list %>%
  # convert to a tibble
  as_tibble() %>%
  # create column with just file name for reference
  mutate(file = fs::path_file(value)) %>%
  # uses map to read all the files and then return a single df
  mutate(data = purrr::map(value, .f=readxl::read_excel)) %>%
  unnest(cols=data) %>%
  janitor::clean_names() %>%
  select(-value)

Upvotes: 0

Seth Raithel
Seth Raithel

Reputation: 296

This is a perfect use case for the purrr package:

library(readxl)
library(tidyverse) #loads purrr
#for each excel file name, read excel sheet and append to df
df.excel <- file.names %>% map_df( ~ read_excel(path = .x))

Upvotes: 4

Greg Snow
Greg Snow

Reputation: 49650

You only think that you want each one loading into the global environment. As you become more experienced with R you will find that in most (if not all) cases it is better to keep related objects like this together in a list.

If they are all in a list then you can use lapply or sapply to run the same command on every element instead of trying to create a new loop where you get each object and process it.

The list approach is less likely to overwrite other objects that you may want to keep or cause other programming at a distance bugs (which can be very hard to track down).

Upvotes: 1

JeanVuda
JeanVuda

Reputation: 1778

You could use something like this in your loop:

lapply(seq_along(file.list), function(x){
  df<-read_excel(x)
  y<-gsub("\\..*","",x)
  assign(y, df, envir=globalenv())
})

Upvotes: 2

pogibas
pogibas

Reputation: 28369

You can use assign with for loop:

library(readxl)
file.list <- list.files(pattern = "*.xlsx")
for(i in file.list) {
    assign(sub(".xlsx", "", i), read_excel(i))
}

PS.: you need sub to remove file extension (otherwise you would get object mydf1.xlsx instead of mydf1).

Upvotes: 4

Related Questions