1

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?

pogibas
  • 27,303
  • 19
  • 84
  • 117
nik
  • 2,500
  • 5
  • 21
  • 48
  • What do you mean by "get each file". Do you mean you want to get only the name of the file? Do you mean you want to open it in a data.frame? Or something different? Also, can you post the results of str(df.list)? I'm wondering if you already have a list of all of the files. I'm curious what happens when you run names(df.list) and whether that gives you the names of all of the files. – Adam Sampson Nov 28 '17 at 20:55

5 Answers5

4

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).

pogibas
  • 27,303
  • 19
  • 84
  • 117
4

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))
www
  • 38,575
  • 12
  • 48
  • 84
Seth Raithel
  • 296
  • 1
  • 7
  • I liked your answer – nik Nov 28 '17 at 20:58
  • @SethRaithel: possible to show how you would add a column with the source filename? – val Sep 25 '18 at 19:17
  • @SethRaithel: also - what does .x mean in path = .x? I don't follow this syntax. (learner) – val Sep 25 '18 at 19:28
  • 2
    @val Add a mutate statement with the file name. df.excel <- file.names %>% map_df( ~ read_excel(path = .x) %>% mutate("file.name" = .x)) See https://stackoverflow.com/questions/35272457/what-does-the-dplyr-period-character-reference/35273033 for info on the .x notation. It basically means the same thing as data$col1 – Seth Raithel Oct 04 '18 at 19:36
2

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())
})
JeanVuda
  • 1,738
  • 14
  • 29
1

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).

Greg Snow
  • 48,497
  • 6
  • 83
  • 110
0

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)
Aran
  • 145
  • 1
  • 9