Import Data in R: Read CSV, Excel, SPSS, Stata, SAS Files

Data could exist in various formats. For each format R has a specific function and argument. This tutorial explains how to import data to R.

Read CSV Files

The most widely used data format is .csv, comma-separated values. R loads an array of libraries during the start-up, including the utils package. That package provides read.csv(), the standard way to open a CSV file. Here is the syntax:

read.csv(file, header = TRUE, sep = ",")

Argument:

• file: PATH where the file is stored
• header: confirm if the file has a header or not, by default, the header is set to TRUE
• sep: the symbol used to split the variable. By default, `,`.

We will read the data file name mtcats. The csv file is stored online. If your .csv file is stored locally, you can replace the PATH inside the code snippet. Don’t forget to wrap it inside ‘ ‘. The PATH needs to be a string value.

For mac user, the path for the download folder is:

 "/Users/USERNAME/Downloads/FILENAME.csv"

For windows user:

"C:\Users\USERNAME\Downloads\FILENAME.csv"

Note that, we should always specify the extension of the file name.

• .csv
• .xlsx
• .txt
• …

PATH <- 'https://raw.githubusercontent.com/guru99-edu/R-Programming/master/mtcars.csv'                
df <- read.csv(PATH, header =  TRUE, sep = ',')
length(df)

Output:

## [1] 12

class(df$X)

Output:

## [1] "factor"

In R versions before 4.0.0, read.csv() converted every character column into a factor, which is why class(df$X) returns “factor” above. You could switch it off with stringsAsFactors = FALSE.

PATH <- 'https://raw.githubusercontent.com/guru99-edu/R-Programming/master/mtcars.csv'
df <-read.csv(PATH, header =TRUE, sep = ',', stringsAsFactors =FALSE)
class(df$X)

Output:

## [1] "character"

The class for the variable X is now a character.

read.csv() vs read_csv(): Which Should You Use?

read.csv() ships with base R and needs no package. The readr package adds read_csv(), which is faster and makes fewer decisions on your behalf.

library(readr)
df <- read_csv(PATH)

# For very large files, data.table::fread() is faster still
library(data.table)
df <- fread(PATH)

Use read.csv() for small files and scripts that must run without extra packages. Use read_csv() when the file is large or when preserving the exact column names matters.

Read Excel Files

Excel files are very popular among data analysts. Spreadsheets are easy to work with and flexible. R is equipped with a library readxl to import Excel spreadsheet.

Use this code

require(readxl)

to check if readxl is installed in your machine. If you install r with r-conda-essential, the library is already installed. You should see in the command window:

Output:

Loading required package: readxl.

If the package is not installed, you can install it with the conda library or in the terminal, use conda install -c mittner r-readxl.

Use the following command to load the library to import excel files.

library(readxl)

readxl_example()

We use the examples included in the package readxl during this tutorial.

Use code

readxl_example()

to see all the available spreadsheets in the library.

To check the location of a particular spreadsheet, pass its name:

readxl_example("geometry.xls")

If you install R with conda, the spreadsheets are located in Anaconda3/lib/R/library/readxl/extdata/filename.xls

read_excel()

read_excel() opens both the .xls and .xlsx extensions and picks the right parser automatically.

The syntax is:

read_excel(PATH, sheet = NULL, range= NULL, col_names = TRUE)
arguments:
-PATH: Path where the excel is located
-sheet: Select the sheet to import. By default, all
-range: Select the range to import. By default, all non-null cells
-col_names: Select the columns to import. By default, all non-null columns

We can import the spreadsheets from the readxl library and count the number of columns in the first sheet.

# Store the path of `datasets.xlsx`
example <- readxl_example("datasets.xlsx")
# Import the spreadsheet
df <- read_excel(example)
# Count the number of columns
length(df)

Output:

## [1] 5

excel_sheets()

The file datasets.xlsx is composed of 4 sheets. We can find out which sheets are available in the workbook by using excel_sheets() function

example <- readxl_example("datasets.xlsx")

excel_sheets(example)

Output:

[1] "iris"     "mtcars"   "chickwts" "quakes"

If a worksheet includes many sheets, it is easy to select a particular sheet by using the sheet arguments. We can specify the name of the sheet or the sheet index. We can verify if both function returns the same output with identical().

example <- readxl_example("datasets.xlsx")
quake <- read_excel(example, sheet = "quakes")
quake_1 <-read_excel(example, sheet = 4)
identical(quake, quake_1)

Output:

## [1] TRUE

We can control what cells to read in 2 ways

1. Use n_max argument to return n rows
2. Use range argument combined with cell_rows or cell_cols

For example, we set n_max equals to 5 to import the first five rows.

# Read the first five row: with header
iris <-read_excel(example, n_max =5, col_names =TRUE)

If we change col_names to FALSE, R creates the headers automatically.

# Read the first five row: without header
iris_no_header <-read_excel(example, n_max =5, col_names =FALSE)

iris_no_header

In the data frame iris_no_header, readxl generated five placeholder names. Older versions produced X__1 to X__5, while current versions produce …1 to …5.

We can also use the argument range to select rows and columns in the spreadsheet. In the code below, we use the excel style to select the range A1 to B5.

# Read rows A1 to B5
example_1 <-read_excel(example, range = "A1:B5", col_names =TRUE)
dim(example_1)

Output:

## [1] 4 2

example_1 returns 4 rows and 2 columns. The range A1:B5 covers five spreadsheet rows, but the first is consumed as the header, which is why the dimension is 4 by 2.

In the second example, we use the function cell_rows() which controls the range of rows to return. If we want to import the rows 1 to 5, we can set cell_rows(1:5). Note that, cell_rows(1:5) returns the same output as cell_rows(5:1).

# Read rows 1 to 5
example_2 <-read_excel(example, range =cell_rows(1:5),col_names =TRUE)			
dim(example_2)

Output:

## [1] 4 5

example_2, by contrast, is 4 by 5. cell_rows(1:5) again treats the first row as the header, so four data rows are returned across all five columns.

In case we want to import rows which do not begin at the first row, we have to include col_names = FALSE. If we use range = cell_rows(2:5), it becomes obvious our data frame does not have header anymore.

iris_row_with_header <-read_excel(example, range =cell_rows(2:3), col_names=TRUE)
iris_row_no_header <-read_excel(example, range =cell_rows(2:3),col_names =FALSE)

You can also select columns by letter, exactly as in Excel:

# Select columns A and B
col <- read_excel(example, range = cell_cols("A:B"))
dim(col)

Output:

## [1] 150   2

Note : range = cell_cols(“A:B”), returns output all cells with non-null value. The dataset contains 150 rows, therefore, read_excel() returns rows up to 150. This is verified with the dim() function.

The na argument tells read_excel() which values to treat as missing. Count them with sum() and is.na():

1. sum
2. is.na

Here is the code

iris_na <-read_excel(example, na ="setosa")
sum(is.na(iris_na))

Output:

## [1] 50

We have 50 values missing, which are the rows belonging to the setosa species.

Import Data from Other Statistical Software

Files from other statistical packages are imported with the haven package, which supports SAS, STATA and SPSS. We can use the following function to open different types of dataset, according to the extension of the file:

• SAS: read_sas()
• STATA: read_dta() (or read_stata(), which are identical)
• SPSS: read_sav() or read_por(). We need to check the extension

Each of these functions needs only one argument, the PATH where the file is stored, and each accepts a URL as readily as a local path.

library(haven)

haven comes with conda r-essential otherwise go to the link or in the terminal conda install -c conda-forge r-haven

Read SAS Files

For our example, we are going to use the admission dataset from IDRE.

PATH_sas <- 'https://github.com/guru99-edu/R-Programming/blob/master/binary.sas7bdat?raw=true'
df <- read_sas(PATH_sas)
head(df)

Output:

## # A tibble: 6 x 4
##   ADMIT   GRE   GPA  RANK
##   <dbl> <dbl> <dbl> <dbl>
## 1     0   380  3.61     3
## 2     1   660  3.67     3
## 3     1   800  4.00     1
## 4     1   640  3.19     4
## 5     0   520  2.93     4
## 6     1   760  3.00     2

Read STATA Files

For STATA data files you can use read_dta(). We use exactly the same dataset but store in .dta file.

PATH_stata <- 'https://github.com/guru99-edu/R-Programming/blob/master/binary.dta?raw=true'
df <- read_dta(PATH_stata)
head(df)

Output:

## # A tibble: 6 x 4				
##   admit   gre   gpa  rank				
##   <dbl> <dbl> <dbl> <dbl>				
## 1     0   380  3.61     3				
## 2     1   660  3.67     3				
## 3     1   800  4.00     1				
## 4     1   640  3.19     4				
## 5     0   520  2.93     4				
## 6     1   760  3.00     2

Read SPSS Files

read_sav() opens an SPSS file, which carries the .sav extension.

PATH_spss <- 'https://github.com/guru99-edu/R-Programming/blob/master/binary.sav?raw=true'
df <- read_sav(PATH_spss)
head(df)

Output:

## # A tibble: 6 x 4				
##   admit   gre   gpa  rank				
##   <dbl> <dbl> <dbl> <dbl>				
## 1     0   380  3.61     3				
## 2     1   660  3.67     3			
## 3     1   800  4.00     1				
## 4     1   640  3.19     4				
## 5     0   520  2.93     4				
## 6     1   760  3.00     2

Best Practices for Data Import

Running through the checklist below before importing saves most of the cleaning work afterwards:

• The typical format for a spreadsheet is to use the first rows as the header (usually variables name).
• Avoid blank spaces in a file or column name, because they can be read as a column separator. Use an underscore instead.
• Short names are preferred
• Do not use symbols in a name. Write exchange_rate_dollar_euro rather than exchange_rate_$_€.
• Encode missing values as NA rather than as blanks, dashes, or sentinel numbers such as -99, which otherwise have to be cleaned afterwards.

Importing Data in R: Function Reference

The table below lists the import function for each file type, the library that supplies it, and its default arguments:

The second table shows the ways to import a selection with read_excel():

FAQs