In this article we will work on importing .xlsx (Excel) files into R from your computer directory using read.xlsx() command from xlsx package.


Theory

.xlsx is a Microsoft Excel Spreadsheet file. The data is stored in cells, with the cell containing either text or numerical data, or both.

It is one of the most popular formats you will come across when working with data in sales analytics and/or for storing entries and is widely used across various platforms.



Application

Below are the steps we are going to take to make sure we do master the skill of importing .xlsx files into R:

  1. Installing readxl package
  2. Basic read_excel() command description
  3. Importing .xlsx file from your computer



Part 1. Installing readxl package

As R doesn’t have this command built in, we will need an additional package in order to import .xlsx file into R.

You can learn more about readxl package here.

In order to install and “call” the package into your workspace, you should use the following code:


install.packages("readxl")
library(readxl)



Part 2. Basic read_excel() command description

The very brief theoretical explanation of the function is the following:

read_excel(path, sheet = NULL, range = NULL, col_names = TRUE)

 

“path” part of the function requires you to enter the directory of .xlsx file you are importing.

“sheet” requires you to input the numeric order number of the sheet in the file (keep NULL if you are importing the first sheet or if there is only one sheet in the file).

“range” requires you to input the range of cells to import from (for example: A1:D24). If you are importing the whole file, keep “range” equal to NULL.

“col_names” can take two values: TRUE or FALSE. If you keep it equal to TRUE, R will read the column names that are in the Excel file.

If you would like to learn the full description of the command, you can read about it here.



Part 3. Importing .xlsx from the computer

In order to import a .xlsx file from the computer, we need the exact location of the destination file.

So how do we do that? Let’s find out!

First of all, we need to know where the file is stored on your computer.

In my case, I use Mac OS and my file is stored on my desktop. In order to find the location of the file on Mac OS you can right click on the file and choose “Get Info”; on Windows you can right click on the file and choose “Properties”.

In my case, the location of the file in R format is: /Users/DataSharkie/Desktop/TitanicSurvival.csv

Use this local path in the file path in the read.csv() command to import the file. Don’t forget that you need to define a variable into which you will be importing the dataset (I called mine “mydata”).


mydata<-read_excel("/Users/DataSharkie/Desktop/Myfile.xlsx", sheet=NULL,
range=NULL, col_names=TRUE)



If you are interested to learn more about importing different data formats into R, you can find more articles here.