How to read and import Text andExcel files in R with tidyverse?

by

Use the readxl package to import contents of an Excel file from your file into a DataFrame

library ("readxl")
xlsx_path = path_to_excel
sheet_name = sheet_to_import
your_df <-read_excel (xlsx_path, sheet_name)

Make sure to install the readxl library (or the tidyverse library) into RStudio or other R development environment before calling it from your script.

Import Excel files into RStudio – Practical Example

Step 1: Import the readxl library

First off, ensure that the readxl library, which is part of Tidyverse, is installed in RStudio, Jupyter or your R dev tool of choice.

if (!require(readxl)) {
  install.packages("readxl")
}

Note: If Tidyverse is installed in your environment, you can omit this step.

Step 2: Define the path to your Excel file

Next we will create a variable storing the path and optionally the sheet name/s to import. In our case we will import a simple dataset containing BI courses information. 

library ("readxl")
xlsx_path = "C:\\Temp\\Courses_Data.xlsx" 
sheet_name = "Courses"

Step 3: Import your Excel sheet

We will now use the read_excel function to import the contents of the workbook and worksheet defined in the previous step:

read_excel (xlsx_path, sheet_name)

The RStudio console will now display the contents of our file.

Step 4: Save your Excel worksheet content as a DataFrame

The read_excel function returns a DataFrame object we can persist as needed and use for analysis and data visualization:

courses_df <- read_excel (xlsx_path, sheet_name)

Step 5: Check the contents of your DataFrame in RStudio

From the Data section in your RStudio Environment Tab, hit your DataFrame name and browse the data.

Read text files using the Tidyverse package

Step 1: Import the readr package

First off, make sure that the Tidyverse readr package is installed in your RStudio (or Jupyter) dev environment , and install it if required:

if (!require(readr)) {
  install.packages("readr")
}

Next import the readr library for using t in your development environment:

library ("readr")

Step 2: Define path to text / csv file

Next define a path to the file you would like to import. The path could be either in your local file system or a remote server location.

In our case we will import some simple course related information from the local directory.

txt_path = "C:\\Temp\\Courses_Data.csv"

Step 3: Import text file as DataFrame

We can now import the text file as a DataFrame:

courses_data <- read_csv(txt_path, col_names = TRUE)

Note: the col_names attribute signals that the file has column headers. If that’s not the case, make sure to set col_names=FALSE.

Note: You can browse your DataFrame content from the Environment tab in RStudio.

Import text and csv files with R base

Use the following procedure for reading the contents of a text file line by line into a DataFrame:

Step 1: Define path to csv/txt file

As shown above, the first step is to define the file that we would like to import into our R development environment.

txt_path = "C:\\Temp\\Courses_Data.csv"

Step 2: Read file as DataFrame

Next step is to invoke the R base read.csv function to get the text file contents into a DataFrame.

courses_data <- read.csv (txt_path)

We can easily verify that courses_data is a DataFrame object by using the following function:

is.data.frame(courses_data)

This will return the value TRUE in the console.

Get text files with read.table

An alternative option to get rectangular data stored as txt or csv into R is using the read.table method:

courses_data <- read.table (txt_path, header = TRUE, sep =",", dec=".")

Note that in this case, you need to specify the delimiting character (sep) that is used in your file as well the decimal separator (dec)