How to import Excel file into R?

In business, perhaps the most widely used file format to store the data is the excel workbook. An excel workbook contains tables which can easily perform calculations. Just as CSV file can be imported using read.csv() function, Excel file can be imported using read_excel() function. In this article we will look at various options to import excel file into R. So let’s get started!

Load the Packages

Although no built in function is provided to work with the excel file, there are several R packages designed to work seamlessly with the Excel data. We will make use of read_excel() function from readxl package to import the excel file. Lets start with installing and loading readxl package. We will also load tidyverse package to use some of its functions.

install.packages("readxl",dependencies = TRUE)
library(readxl)
library(tidyverse)

Create the Excel file

Create the excel file with three worksheets as follows:

Save the excel file as employee_data.xlsx

Import the Excel file

We can import the excel file using read_excel() function by supplying excel file name with the extension. We will store the data into object named emp_data

emp_data <- read_excel('employee_data.xlsx')
emp_data

Employee_ID Employee_Name
<dbl>       <chr>
10    Rahul
20    Saket
30    Rohini

By default, the read_excel function imports first excel worksheet.

If we want to import specific worksheet, we can use sheet argument. If we want to import second worksheet, we will use sheet=2 in the read_excel function. We will import second workbook and store it in a object named emp_data1

emp_data1 <- read_excel('employee_data.xlsx',sheet=2)
emp_data1

Employee_ID Employee_qualification
<dbl>  <chr>
10     Engineer
20     CA
30     MBA

As you can observe, we have imported second worksheet from the excel file.

Skip the number of rows

Before importing excel worksheet, we can also skip number of rows.

Let’s import the third worksheet and store it into object called emp_data2

emp_data2 <- read_excel('employee_data.xlsx',sheet=3)
emp_data2

this is employee data      ...2
<chr>                      <chr>
this is employee data   NA
this is employee data   NA
Employee_ID           Employee_Designation
10                   Manager
20                   VP
30                   Trainee

The initial few rows is a text data which we want to skip. This text data is not useful for our analysis, so we won’t import it. You may also notice that the first row is converted into column names. This also needs to be corrected.

We will use skip argument to skip the initial rows from worksheet. Since we want to skip 3 rows, we will pass skip=3 argument to read_excel() function. We will store the data in object called emp_data3

emp_data3 <- read_excel('employee_data.xlsx',sheet=3,skip=3)
emp_data3

Employee_ID  Employee_Designation
<chr>       <dbl>
10          Manager
20          VP
30         Trainee

Import multiple excel sheets

Imagine you have 100s of excel worksheets in your excel file. Calling separate read_excel() function for all these worksheets shall be repetitive and cumbersome task. (you will have to write the same line of code 100 times) Can we automate this boring stuff?

The apply function comes to our rescue. We can create a character vector of all excel worksheets and pass it on to the read_excel() function. Using lapply function, we can loop over the read_excel() function on all the worksheets and import them one by one in a list.

This is how it works

work_sheet <- excel_sheets('employee_data.xlsx')
work_sheet

[1] "Sheet1" "Sheet2" "Sheet3"

The character vector work_sheet contains the name of worksheets in the excel file. We can use lapply function on this character vector to import all the worksheets in excel file.

emp_data_all <- lapply(work_sheet, function(X) readxl::read_excel('employee_data.xlsx', sheet = X))

emp_data_all

emp_data_all consists list with three elements. Each list element is a data from a worksheet. Let’s check the data from worksheet 1

You can further append all the data into one spread sheet table using reduce function from purrr package. Using merge function and by supplying common column name we can merge all the separate excel worksheets together and produce one single consolidated excel spreadsheet.

Since we had to remove the initial rows from data, We will drop the third worksheet to maintain the consistency in column names.

lapply(work_sheet[-3], function(X) readxl::read_excel('employee_data.xlsx', sheet = X))%>%
  purrr::reduce(merge,by="Employee_ID")

Summary

In this article we used read_excel function from readxl package to import the excel file. We also explored various options from read_excel function used while importing the data.
Finally we used lapply function along with read_excel to import multiple excel worksheet at once. We combined these worksheets into one excel spreadsheet using reduce function from purr package.