how to make for-loop order to rbind all excel files in the same directory

Question

i want to rbind all excel files (regardless the total amount of files) in a directory and make a new column that is filled with the name of files, so then i could identify where the data came from. For example, i have template of file like this

promo  stock
  a     200
  b     200
  c     200

to be like this

store  promo  stock
file1    a     200
file1    b     200
file1    c     200
file2    a     200
file2    b     200
file2    c     200
file3    a     200
file3    b     200
file3    c     200

how to make a for-loop order for this case? thanx in advance

file1.xlsx,file2.xlsx and so on. Those files have the same template — Nicodemus Sigit Sutanto, May 27 '19 at 01:27
Related: https://stackoverflow.com/questions/32888757/how-can-i-read-multiple-excel-files-into-r or the more general https://stackoverflow.com/questions/11433432/how-to-import-multiple-csv-files-at-once or https://stackoverflow.com/questions/48519464/how-to-import-multiple-excel-files-with-multiple-sheet-in-r or https://stackoverflow.com/questions/33771402/how-to-read-in-multiple-xlsx-files-to-r or https://stackoverflow.com/questions/21159387/importing-multiple-excel-files-with-filenames-in-r etc etc etc — thelatemail, May 27 '19 at 01:52
if my excel file has several sheets in it and i want to merge it all first and then merge it with other excel file, how to do it? — Nicodemus Sigit Sutanto, May 27 '19 at 02:13
@NicodemusSigitSutanto - I imagine the first step is to start writing or adapting some code, and then ask a specific question if something goes wrong. One of the questions I've linked above ( https://stackoverflow.com/questions/48519464/how-to-import-multiple-excel-files-with-multiple-sheet-in-r ) is nearly exactly what you describe. — thelatemail, May 27 '19 at 03:12

Ronak Shah · Answer 1 · 2019-05-27T01:46:24.243

3

We could find the full path name to the files and then use lapply to read each file with readxl::read_excel and add a new column store with the name of the file

file_paths <- list.files("/path/to/files", pattern = ".xlsx$", full.names = TRUE)

do.call(rbind, lapply(file_paths, function(x) 
    transform(readxl::read_excel(x), store = sub(".xlsx$", "", basename(x)))))

#  promo stock store                                            
#1     a   200 file1
#2     b   200 file1
#3     c   200 file1
#4     a   200 file2
#5     b   200 file2
#6     c   200 file2

We can also use purrr::map_df

purrr::map_df(file_paths, ~transform(readxl::read_excel(.), 
                            store = sub(".xlsx$", "", basename(.))))

edited May 27 '19 at 01:46

answered May 27 '19 at 01:36

Ronak Shah

377,200
20
156
213

how to remove that ".xlsx" in the store column, so i just get the name without the extension? – Nicodemus Sigit Sutanto May 27 '19 at 01:43
@NicodemusSigitSutanto You could use `tools::file_path_sans_ext` to get only the file name without extension or use `sub` as updated above in the answer. – Ronak Shah May 27 '19 at 01:47
what is the difference between ".xlsx$" and ".xlsx"? – Nicodemus Sigit Sutanto May 27 '19 at 04:44
@NicodemusSigitSutanto `.xlsx$` removes `xlsx` only if it is present in the last part of the string, using it without `$` would remove it from anywhere in the string. – Ronak Shah May 27 '19 at 04:50

score 2 · Answer 2 · answered May 27 '19 at 01:28

2

This should do it although Reduce(rbind) might be slow depending on how many files you have. Also you may need to modify the code to read your file based on the extension. Let me know -

# set working directory to where the files are using setwd()

result <- lapply(dir(), function(x) {
  cbind(store = x, read.csv(paste0(x, ".csv"), header = T, stringsAsFactors = F))
}) %>% 
  {Reduce(rbind, .)}

answered May 27 '19 at 01:28

Shree

10,835
1
14
36

my files are in xlsx extension. Is it applicable? So then I just replace csv with xlsx? – Nicodemus Sigit Sutanto May 27 '19 at 01:32
Just use any package that lets you read xlsx and replace my `read.csv` with that. – Shree May 27 '19 at 01:33

how to make for-loop order to rbind all excel files in the same directory

2 Answers2