1

I want to import multiple excel files. Each file contains 3 tabs with same name with other files. Eg. Studentname,studentdivision, studentmarks.

I want to import all files and bind them in unique tabs and export one file with these 3 tabs data

Need help I tried but it won't work

Phil
  • 7,287
  • 3
  • 36
  • 66
Bhushan Nalawade
  • 11
  • 1

1 Answers1

0

There is R read_excel or readxl Multiple Files with Multiple Sheets - Bind, but it only contains information on how to read a single sheet from multiple files and combine the results to one data frame.

This answer is extending that approach to a situation with multiple different sheets across files, resulting in multiple dataframes.

First of all, set your working directory to the folder with the files.

Second step: List all .xlsx files in that folder

filenames <- list.files(pattern = "*.xlsx", full.names = TRUE)

Third step: create empty list

df <- list()

Fourth step: run a for loop to:

  • read in all sheets number i across all files (using lapply())
  • combine all sheets number i with rbind()
  • store the results in a previously created empty list (df)

Note: all column names must be the same in the same sheets, otherwise rbind() wont work. And sheet order has to be similar across all files.

for (i in 1:3) {
  df_list <- lapply(filenames, readxl::read_excel, sheet = i)
  df[[i]] <- do.call(rbind, df_list)
}

Now you have one data frame per sheet, and all are stored inside a list.

Either you work with that, or you unlist them to your environment as described here: Unlist a list of dataframes

uke
  • 462
  • 1
  • 11