# 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("<physical path to directory>")`

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 V2  
`A 3 2`  
`B 5 6`  
`B 2 3`  
`A 4 8`  
`A 7 3`

Use `read.table()`:

`df <- read.table("<path/file.txt>", header = TRUE)`

For non-tab delimiters:

`df <- read.table("<file.txt>", sep = ",")`

---

### Reading CSV Files

CSV files are typically comma-separated or semicolon-separated.

* **Comma-separated** → `read.csv()`
    
* **Semicolon-separated** → `read.csv2()`
    

`df <- read.csv("<file.csv>")`  
`df <- read.csv2("<file.csv>")`

Both are wrappers around `read.table()` with predefined defaults.

Equivalent calls:

`read.table("<file.csv>", sep = ",")`  
`read.table("<file.csv>", sep = ";")`

---

### A Quick Import Hack: Clipboard

For fast, ad-hoc analysis:

1. Copy data from Excel or a document
    
2. 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("<package-name>")`  
`library(<package-name>)`

---

## Importing JSON Files

Use the **rjson** package:

`install.packages("rjson")`  
`library(rjson)`

Import from file or URL:

`JsonData <- fromJSON(file = "<inputJsonFile.json>")`  
`JsonData <- fromJSON(file = "<URL>")`

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("<inputXMLfile.xml>")`  
`xmldataframe <- xmlToDataFrame("<inputXMLfile.xml>")`

---

### Extracting HTML Tables

`HtmlData <- readHTMLTable(getURL("<url>"))`

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("<inputExcelFile.xlsx>")`

Read a specific sheet:

`read_excel("<inputExcelFile.xlsx>", sheet = "Sheet 3")`  
`read_excel("<inputExcelFile.xlsx>", 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("<path/to/file.mat>")`

#### Octave

`library(foreign)`  
`data <- read.octave("<path/to/file>")`

---

## 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**](https://www.perceptive-analytics.com/tableau-consulting/) and operating as a trusted [**power bi consulting company**](https://www.perceptive-analytics.com/power-bi-consulting/), turning data into strategic insight. We would love to talk to you. Do reach out to us.