Relational databases via ODBC

With a different function (and often a different package) for almost every file format, it’s easy to feel overwhelmed—especially when juggling multiple arguments and dependencies. However, once you understand which tools to use for which data types, importing data into R becomes straightforward and efficient.
This guide is designed to be your one-stop reference. The next time you search for “How do I load XYZ file into R?”, you’ll know exactly where to look.

What This Tutorial Covers
We’ll walk through importing the most commonly used data formats in R, including:
TXT and CSV files
JSON and XML data
HTML tables
Excel workbooks
SAS, SPSS, STATA datasets
MATLAB and Octave files
Relational databases via ODBC
We’ll also share a handy importing hack for quick, ad-hoc analysis.
Let’s dive in.

Preparing Your R Workspace Before Importing Data
Setting the Working Directory
Most projects store related files in a single folder. Setting this folder as your working directory simplifies file imports.
getwd()
To change your working directory:
setwd("")
Once set, R will automatically look for files in this location—saving you from repeatedly typing long file paths.

Cleaning the Workspace
Leftover objects from previous sessions can cause subtle and frustrating errors. Starting clean is often best.
rm(list = ls())
This removes all objects from the current environment. Use this carefully, but deliberately.
Pro tip: Avoid saving the workspace on exit unless necessary. Fresh sessions reduce debugging headaches.

Loading TXT, CSV, and Other Delimited Files
Reading Text Files (.txt)
Delimited text files use separators such as tabs, commas, or semicolons.
Example structure:
Category V1 V2A 3 2B 5 6B 2 3A 4 8A 7 3
Use read.table():
df <- read.table("", header = TRUE)
For non-tab delimiters:
df <- read.table("", sep = ",")

Reading CSV Files
CSV files are typically comma-separated or semicolon-separated.
Comma-separated → read.csv()
Semicolon-separated → read.csv2()
df <- read.csv("")df <- read.csv2("")
Both are wrappers around read.table() with predefined defaults.
Equivalent calls:
read.table("", sep = ",")read.table("", sep = ";")

A Quick Import Hack: Clipboard
For fast, ad-hoc analysis:
Copy data from Excel or a document
Run:
df <- read.table("clipboard", header = TRUE)
This is a lifesaver for exploratory work, though formatting issues may occur.

Using Packages for Advanced Imports
Before using package-based import functions:
install.packages("")library()

Importing JSON Files
Use the rjson package:
install.packages("rjson")library(rjson)
Import from file or URL:
JsonData <- fromJSON(file = "")JsonData <- fromJSON(file = "")
Convert to data frame:
JsonDF <- as.data.frame(JsonData)

Importing XML and HTML Data
Use the XML and RCurl packages:
library(XML)library(RCurl)
Reading XML Files
xmlData <- xmlTreeParse("")xmldataframe <- xmlToDataFrame("")

Extracting HTML Tables
HtmlData <- readHTMLTable(getURL(""))
This is especially useful for scraping structured data from web pages.

Reading Excel Workbooks
The readxl package is the modern standard:
install.packages("readxl")library(readxl)
Read the first sheet:
df <- read_excel("")
Read a specific sheet:
read_excel("", sheet = "Sheet 3")read_excel("", sheet = 3)
Why readxl?
No Java or Perl dependencies
Fast and lightweight
Works across platforms

Importing Data from Statistical Software
SAS, SPSS, and STATA Files
Use the haven package:
install.packages("haven")library(haven)
read_sas("InputSAS.sas7bdat")read_sav("InputSPSS.sav")read_dta("InputStata.dta")
Haven leverages the ReadStat C library, making it fast and reliable.

MATLAB and Octave Files
MATLAB (.mat)
install.packages("R.matlab")library(R.matlab)data <- readMat("")
Octave
library(foreign)data <- read.octave("")

Importing Data from Relational Databases (ODBC)
Use the RODBC package:
install.packages("RODBC")library(RODBC)
Common ODBC Functions
odbcConnect() – establish connection
sqlFetch() – import entire tables
sqlQuery() – execute SQL queries
sqlSave() – write data to DB
odbcClose() – close connection
Example
con <- odbcConnect("dsn", uid = "userID", pwd = "123")SqlData1 <- sqlFetch(con, "Table1")SqlData2 <- sqlQuery(con, "SELECT * FROM Table2")odbcClose(con)

Tips for Making Data Import Easier in R
Ensure column names are unique
Avoid spaces and special characters in variable names
Use consistent naming conventions
Replace missing values with NA
Remove commented lines in raw files
Prefer shorter, meaningful variable names
R is case-sensitive—be consistent
Recommended style guides:
Tidyverse Style Guide
Google R Style Guide
R Journal Naming Conventions

End Notes
Importing data into R is just the first step in a much larger analytics journey—one that includes data cleaning, visualization, modeling, and deployment.
In this guide, we covered:
Flat files (TXT, CSV)
JSON, XML, and HTML
Excel spreadsheets
SAS, SPSS, STATA, MATLAB
Database connections via ODBC
R offers multiple ways to accomplish the same task, and choosing the right one depends on speed, scale, and maintainability.
If your next step involves building predictive models, scaling analytics, or deploying AI-driven solutions, explore our AI Consulting Services to accelerate outcomes.
Hope this article makes your importing tasks easy’R
At Perceptive Analytics, our mission is “to enable businesses to unlock value in data.” For over 20 years, we’ve partnered with more than 100 clients—from Fortune 500 companies to mid-sized firms—to solve complex data analytics challenges. Our services include delivering end-to-end tableau consulting services and operating as a trusted power bi consulting company, turning data into strategic insight. We would love to talk to you. Do reach out to us.