Are you ever stuck on getting your spreadsheet file imported into R? As a data analyst, Microsoft Excel and R are some of the tools that you will work with from time to time. This tutorial will teach you how to import your data from Microsoft Excel into R.

Introduction

Microsoft Excel is a spreadsheet application developed by Microsoft which is used for storing, organizing, formatting, calculating, and analyzing data. It is a powerful tool that most data analysts use for data analysis and data visualization.

R is a statistical programming language developed by Ross Ihaka and Robert Gentleman. It is an implementation of the S programming language. It is used for statistical computing and reporting. It is a standard software used by data analysts, scientists, engineers, and other data professionals.

Requirements

These are the necessary things to put in place for the tutorial:

Download and install RStudio

Get your data.

For this tutorial, we will consider two datasets of different formats.

1. The superstore dataset (xlsx)
2. The Amazon data (csv)

File formats supported by Microsoft Excel

Microsoft Excel supports a quite number of file formats. The list below highlights some of them.

Format Extension Excel workbook .xlsx Microsoft Excel 5.0/95 Workbook .xls CSV (Comma delimited) .csv Text .txt DIF .dif SYLK ,slk OpenDocument Spreadsheet .ods Portable Document FIle .pdf DBF 3, DBF 4 .dbf XML data .xml

Steps involved

Launch Rstudio on your computer system

You can do this by clicking on its icon on your desktop or better still type RStudio on the start menu search

Press Ctrl+ L to clear the console

On the console, a description of R pops up. You can clear these words by pressing on the Ctrl key and L at the same time. However, you can go ahead with other instructions without clearing the console as it does not affect anything at all. Clearing the console just makes your work neater.

1. #setwd('default working location ') setwd("/Users/HP/Documents/R")

Set your working directory

The working directory is the location where R will load the files you want to work with and also store the files you have worked on. The good thing about setting up a working directory is that you do not need to write explicit paths when R wants to access the files you load.

You can set your working directory on the R console by using the setwd() function as illustrated below.

1. install.packages('readxl')

Install the readxl package.

The readxl package **is the package that allows you to import data files from Microsoft Excel (with xls and xlsx file extensions) to R.

You can install the package as illustrated below.

1. library(readxl)

Load the readxl library

1. mydata <- read_excel('Sample - Superstore.xls') #display the first 6 entries in the dataset head(mydata)

Load your dataset

mydata represents the first sheet in the sample superstore dataset.

Output

This result shows the top 6 entries of the first sheet in the “Sample -Superstore.xls”

1. #loading the second sheet mydata2 <- read_excel('Sample - Superstore.xls', sheet=2) #display the first 6 entries in the dataset head(mydata2) #loading the third sheet named **People** mydata3 <- read_excel('Sample - Superstore.xls', sheet="People") #display the first 6 entries in the dataset head(mydata3)

Load the other sheets contained in the dataset.

The “Sample - Superstore.xls” dataset contains actually three different sheets. In order to load the other sheets, you either

1. specify the name of the sheet in a string (do not forget to put the string in quotes)
2. the position of the sheet in numbers.

mydata2 represents the second sheet in the sample superstore dataset.

Output

This result shows the top 6 entries of the second sheet in the sample superstore dataset.

Output

This result shows the top 6 entries of the third sheet, named People in the sample superstore dataset.

1. install.packages('readr')

Install the readr package

The readr package is built to enable users to import data fast from delimited files such as comma-separated values, tab-separated values, and so on.

You can load the

1. library(readr)

Load the readr library

1. amazon <- read_csv('AMZN.csv', show_col_types = FALSE) #display the first 6 entries in the dataset head(amazon)

Load your dataset

amazon represents the amazon file

Output

This result shows the top 6 entries in the amazon dataset

I hope you found this tutorial helpful. Please feel free to drop any comments, questions, or recommendations.

Thank you!