Importing and Exporting Data

Analysis starts with getting data into R and ends with getting results out. This lesson covers the most common formats: CSV, Excel, databases, and web APIs.

1. Reading and Writing CSV Files

Base R provides read.csv(), but the readr package (part of tidyverse) is faster and more consistent:

# Base R
df <- read.csv("data/sales.csv")
write.csv(df, "data/output.csv", row.names = FALSE)

# readr (tidyverse) — recommended
library(readr)
df <- read_csv("data/sales.csv")
write_csv(df, "data/output.csv")

2. Working with Excel Files

Use the readxl package to import .xlsx/.xls files, and writexl or openxlsx to export:

install.packages(c("readxl", "writexl"))
library(readxl)
library(writexl)

df <- read_excel("data/report.xlsx", sheet = "Sheet1")
excel_sheets("data/report.xlsx")   # list all sheet names

write_xlsx(df, "data/output.xlsx")

3. Connecting to Databases

R connects to relational databases using the DBI package with a database-specific driver (e.g., RSQLite, RPostgres, RMySQL).

install.packages(c("DBI", "RSQLite"))
library(DBI)

con <- dbConnect(RSQLite::SQLite(), "data/mydb.sqlite")
dbListTables(con)

result <- dbGetQuery(con, "SELECT * FROM customers WHERE age > 30")

dbDisconnect(con)

For PostgreSQL or MySQL, the pattern is nearly identical — swap the driver:

con <- dbConnect(RPostgres::Postgres(),
                  dbname = "mydb", host = "localhost",
                  port = 5432, user = "admin", password = "secret")

4. Pulling Data from APIs

The httr2 (or older httr) package handles HTTP requests, and jsonlite parses JSON responses.

install.packages(c("httr2", "jsonlite"))
library(httr2)
library(jsonlite)

resp <- request("https://api.example.com/users") |>
  req_headers(Authorization = "Bearer YOUR_TOKEN") |>
  req_perform()

data <- resp |> resp_body_json(simplifyVector = TRUE)
head(data)

5. Comparing Import Methods

6. Production-Ready Checklist

• ✅ Use readr for CSVs — faster and more predictable than base R.
• ✅ Use readxl/writexl for Excel — no need for Excel to be installed.
• ✅ Use DBI + driver packages for databases — always close connections with dbDisconnect().
• ✅ Secure API credentials — never commit tokens or passwords to source control.