How to Read Excel Files in R [3 Ways]

by
Featured Image of Reading Excel Files in R

No matter how much technology has improved over the years for storing and sharing data, one format is still widely used in the industry: Excelsheet, which has file extensions like “.xls” or “.xlsx.“

Here are three ways to import Excel files in R:

  1. Using the readxl package (Efficient way)
  2. Using xlsx package
  3. RStudio’s Import Dataset tool

For this tutorial’s practical, we will import and read the below Excel file:

Screenshot of excel file used in this practical

Method 1: Using the readxl package

The most efficient way to import and read external Excel sheets into R is using the readxl package’s read_excel() method. It reads xls and xlsx files and detects the format from the extension.

Here is the step-by-step guide:

Step 1: Install the readxl package

You will need to install this package first if you haven’t already done so.

To install the readxl package:

install.packages("readxl")

Step 2: Load the package

Before using the package’s functions, we must import it using the library() function.

library(readxl)

Syntax

read_excel("path/to/your/file.xlsx")

Step 3: Use read_excel() function

It is time to pass the external data source Excel file “simple_data_frame.xlsx” to the read_excel() function. This function will return a tibble, an enhanced version of the data frame.

# Load the readxl package
library(readxl)

# Read an Excel file into a data frame
my_data <- read_excel("simple_data_frame.xlsx")

# Display the first few rows of the data
head(my_data)

Output

Output of Use read_excel() function

We got the 3X3 tibble in the output, and the data is the same as the Excel file. Ensure that you don’t lose any rows or columns while you are importing.

Pros

  1. Since the readxl library is written in C++, it is the fastest for large files.
  2. The syntax is straightforward to learn.
  3. It can handle various Excel file formats and inconsistencies well.

Cons

  1. You cannot edit or modify the content using this library. It is just for reading the file.

Method 2: Using the xlsx package

If you want to import, read, and modify Excel files in the R environment, install the “xlsx” package and use the read_xlsx() function. 

The read_xlsx() function accepts an “xlsx” file and converts it into a Data Frame, which we can modify further to get the desired output.

library(xlsx)

df <- read.xlsx("simple_data_frame.xlsx", sheetIndex = 1) # Read the first sheet

print(df)

Output

Output screenshot of using the xlsx package

Pros

  1. It allows reading and writing Excel files, making it a complete package for Excel manipulation.
  2. It provides ample control over formatting when writing to Excel files.
  3. You can import and export data from R to Excel files and vice-versa.

Cons

  1. It requires JAVA to be installed on your machine.
  2. It becomes slower as the file size grows.

Method 3: RStudio’s Import Dataset tool

If you are using RStudio, you can also use the built-in menu options to import Excel files:

Step 1: Navigate to the Menu Bar

Go to the menu bar at the top and click on File -> Import Dataset -> From Excel.

Navigate to the Menu Bar

Step 2: Choose File

A window will pop up. Navigate to the Excel file you wish to import and select it.

Screenshot of Choosing a File

Step 3: Preview and Import

You’ll see a preview of the data. If it looks correct, click the Import button to import the data into R.

Screenshot of Preview and Import

You can see that we successfully imported data into the data frame using RStudio’s Import Dataset tool.

Pros

  1. It provides a user-friendly graphical user interface.
  2. It allows us to preview the data and adjust import options before importing.

Cons

  1. This approach is not suitable for automated scripts.
  2. It does not provide full control over import options.

That’s all!

Leave a Comment