Importing csv and excel data (.xls and .xlsx) using readxl package into R

Read csv file and excel files (.xls and .xlsx) into R using ‘readxl’ package

Even today, most of the companies use Microsoft Excel for storing their information. It is convenient, data is formatted in a tabular fashion, and needs no training as most of the employees know excel very well.

Excel files can be stored either as excel workbook.(.xls , .xlsx) or as Comma Separated values (.csv).

Before importing data into R, it would be helpful to set working directory where files are stored. This avoids providing the entire path for reading each file. It can be done using the following command

#knowing current working directory
#setting a new working directory
setwd("C:/...imaginary file path")

Notice the use of forward slash while providing working directory

Importing csv data into R

csv data can be read into a dataframe in R using the following command

mydata = read.csv('mydata.csv', header=TRUE)

Notice that as we have already set working directory, we need not provide the entire path where file is stored

Importing excel data into R
There are multiple packages to read excel data into R such as ‘xlsx’, ‘gdata’, ‘xlsReadWrite’ and the one which is illustrated here ‘readxl’

#install package readxl
#load package
#read excel file (with .xls or .xlsx extension) into r dataframe
mydata = read_exce('myfile.xlsx', sheet=1) #reading first sheet
#for xls file replace .xlsx with .xls above

The advantage of ‘readxl’ package is that it has no external dependencies and is easy to install and use on all operating systems.

Author: Krishna

Data Analytics Enthusiast

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s