How to Read Multiple Excel files in R?

In this post, we will look at how to use the R programming language to merge numerous/multiple Excel files in a folder.

Modules Required

dplyr package:

The dplyr package in R contains several tools for manipulating data in R that provide a uniform set of verbs, helping to resolve the most frequent data manipulation challenges.

Syntax:

install.packages(“dplyr”)

Output:

Installing package into ‘C:/Users/cirus/AppData/Local/R/win-library/4.2’
(as ‘lib’ is unspecified)
trying URL 'https://cloud.r-project.org/bin/windows/contrib/4.2/dplyr_1.0.9.zip'
Content type 'application/zip' length 1297402 bytes (1.2 MB)
downloaded 1.2 MB

package ‘dplyr’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
C:\Users\cirus\AppData\Local\Temp\Rtmpo5UGo0\downloaded_packages

plyr package:

In R, the “plyr” package is used to work with data, including enhancements/improvements and modifications.

Syntax:

install.packages("plyr")

Output:

Installing package into ‘C:/Users/cirus/AppData/Local/R/win-library/4.2’
(as ‘lib’ is unspecified)
trying URL 'https://cloud.r-project.org/bin/windows/contrib/4.2/plyr_1.8.7.zip'
Content type 'application/zip' length 1152098 bytes (1.1 MB)
downloaded 1.1 MB

package ‘plyr’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
C:\Users\cirus\AppData\Local\Temp\Rtmpo5UGo0\downloaded_packages

readxl package:

In R, the readxl package is used to work with excel files.

Syntax:

install.packages("readxl")

Output:

Installing package into ‘C:/Users/cirus/AppData/Local/R/win-library/4.2’
(as ‘lib’ is unspecified)
--- Please select a CRAN mirror for use in this session ---
trying URL 'https://cloud.r-project.org/bin/windows/contrib/4.2/readxl_1.4.0.zip'
Content type 'application/zip' length 1197616 bytes (1.1 MB)
downloaded 1.1 MB

package ‘readxl’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
C:\Users\cirus\AppData\Local\Temp\Rtmpo5UGo0\downloaded_packages

readr package:

The readr package in R is used to read files.

Syntax:

install.packages("readr")

Output:

Installing package into ‘C:/Users/cirus/AppData/Local/R/win-library/4.2’
(as ‘lib’ is unspecified)
trying URL 'https://cloud.r-project.org/bin/windows/contrib/4.2/readr_2.1.2.zip'
Content type 'application/zip' length 1207797 bytes (1.2 MB)
downloaded 1.2 MB

package ‘readr’ successfully unpacked and MD5 sums checked

The downloaded binary packages are in
C:\Users\cirus\AppData\Local\Temp\Rtmpo5UGo0\downloaded_packages

Functions Required

list.files() Function:

This gives a character vector of the names of files or directories in the named directory.

Syntax:

list.files(path = ".", pattern = NULL, all.files = FALSE,full.names = FALSE,
recursive = FALSE, ignore.case = FALSE, include.dirs = FALSE, no.. = FALSE)

lapply() Function:

The lapply() function can be used to do operations on list objects and returns a list object with the same length as the original set.

lappy() returns a list of the same length as the input list object, with each element the result of applying FUN to the corresponding element of list. Lapply in R accepts a list, vector, or data frame as input and returns a list as output.

Syntax:

lapply(X, FUN)

bind_rows() function:

The bind_rows() function is a fast implementation of the common pattern of do.call(rbind, dfs) or do.call(cbind, dfs) for combining/binding many data frames into one.

Syntax:

bind_rows(…, .id = NULL)

Let us use the below given two Excel Files here:

Excel Files

plotExcelFile.xlsx:

excel file with x and y axis

excelFile.xlsx:

sample-excel-file-sheet1-image

sampleExcelFile.xlsx:

sample excel file 6 columns

How to Merge Multiple Excel files in R

Approach:

  • Import plyr package using the library() function.
  • Import dplyr package using the library() function.
  • Import readr package using the library() function.
  • Import readxl package using the library() function.
  • Pass the location of the Folder that contains excel files, pattern ( In our case it is xlsx files) to the files() function
    apply lapply() and bindrows to it.
  • Print the above result Data
  • The Exit of the Program.

Below is the implementation:

# Import plyr package using the library() function                                                
library(plyr) 
# Import dplyr package using the library() function
library(dplyr)
# Import readr package using the library() function                                        
library(readr)
# Import readxl package using the library() function
library(readxl)

# Pass the location of Folder that containing excel files, pattern ( In our case it is xlsx files) to the files() function
# apply lapply() and bindrows to it
resultData <- list.files(path = "C:/Users/cirus/Downloads/excelFilesFolder",    
                    pattern = "*.xlsx",
                    full.names = TRUE) %>%
lapply(read_excel) %>%                                        
bind_rows                                                    
# Print the above result Data
resultData

Output:

# A tibble: 31 × 14
   `Website name`  Name       Age City   State Student Employed Married Scholorship    Id     x     y
   <chr>           <chr>    <dbl> <chr>  <chr> <chr>   <chr>    <chr>   <chr>       <dbl> <dbl> <dbl>
 1 Sheets Tips     Vikram      21 Hyder… Tela… Yes     No       No      Not Applic…  1234    NA    NA
 2 Python-Programs Ashritha    22 Pune   Mumb… Yes     No       No      Already Ta…  2344    NA    NA
 3 BTechGeeks      Manish      19 Indore Madh… Yes     No       No      Already Ta…  1345    NA    NA
 4 PythonArray     Anish       20 Rajkot Guja… Yes     No       No      Already Ta…  1356    NA    NA
 5 Sheets Tips     Vishal      18 Ranchi Jhar… Yes     No       No      Already Ta…  1938    NA    NA
 6 Python-Programs Pavan       23 Luckn… Utta… No      Yes      No      Not Applic…  2345    NA    NA
 7 Sheets Tips     John        32 Los A… Amer… No      Yes      Yes     Not Applic…  5622    NA    NA
 8 Python-Programs Virat       34 Agra   Delhi No      Yes      No      Not Applic…  1734    NA    NA
 9 BTechGeeks      Cary        36 Tokyo  Japan No      Yes      Yes     Already Ta…  4562    NA    NA
10 PythonArray     Tara        39 Helsi… Finl… No      Yes      Yes     Not Applic…  2365    NA    NA
# … with 21 more rows, and 2 more variables: Gender <chr>, Passport <chr>

 

Leave a Comment