7 Importing and Exporting Data in R: A Comprehensive Guide

Interacting with external data sources is a fundamental skill for any data analyst or scientist. In R, you have a rich ecosystem of functions and packages designed to read and write data in formats ranging from plain text CSVs to Excel workbooks, and even full-fledged SQL databases. This post dives deep into the mechanics, best practices, and advanced techniques for importing and exporting data in R. You’ll learn:

• How to use read.csv() and write.csv() for tabular data

• When and why to leverage the readr package for faster I/O

• Best practices for reading and writing Excel files with readxl, writexl, and openxlsx

• How to establish database connections via DBI and RSQLite, run queries, and manage transactions

• Tips for handling large datasets, ensuring reproducibility, and optimizing performance

By mastering these tools, you’ll build reproducible pipelines, eliminate manual data wrangling, and streamline collaboration across teams. Let’s get started.

Table of Contents

1. Working with CSV Files

Comma-separated values (CSV) files remain the lingua franca of data interchange. They are human-readable, software-agnostic, and easy to version-control. R provides base functions read.csv() and write.csv(), while the readr package from the tidyverse offers readr::read_csv() and readr::write_csv() for faster performance and consistent behavior.

1.1 Using read.csv()

The base R function read.csv() is essentially a wrapper around read.table() with sensible defaults for CSVs:

r

data <- read.csv(
  "data/raw/sales_data.csv",
  header      = TRUE,
  sep         = ",",
  stringsAsFactors = FALSE,
  na.strings  = c("", "NA", "NULL"),
  colClasses  = c(Date = "Date", Sales = "numeric")
)

Key parameters:

• file: path to your CSV file

• header: whether the first row contains column names

• sep: field separator ("," by default)

• stringsAsFactors: convert character columns to factors (legacy behavior, usually FALSE)

• : strings treated as NA

• colClasses: predefine column types to accelerate parsing and prevent misclassification

• fileEncoding: specify encoding (e.g., "UTF-8", "latin1") for non-ASCII text

Common Challenges

• Mixed data types: explicit colClasses prevents columns from defaulting to factor or character.

• Large files: read.csv() loads the entire file into memory, which can be slow or infeasible for multi-GB datasets.

1.2 Leveraging readr::read_csv()

The readr package addresses speed and memory concerns. Its read_csv() uses C++ under the hood and streams data efficiently:

r

library(readr)

sales <- read_csv(
  "data/raw/sales_data.csv",
  col_types = cols(
    Date = col_date(format = "%Y-%m-%d"),
    Sales = col_double(),
    Region = col_character()
  ),
  na = c("", "NA", "None")
)

Advantages:

• Speed: benchmarks show readr functions are 2–5× faster than base R for typical CSVs.

• Consistent parsers: uniform behavior for numbers, dates, and missing values.

• Progress bar: built-in progress indicator for large files.

• Memory efficiency: streams in chunks rather than reading the entire file at once.

1.3 Writing CSV Files

After analysis or data cleaning, export your results with write.csv() or readr::write_csv():

r

# Base write.csv
write.csv(
  sales_summary,
  file     = "data/processed/sales_summary.csv",
  row.names = FALSE,
  na       = ""
)

# readr write_csv
write_csv(
  sales_summary,
  "data/processed/sales_summary.csv"
)

Best practices:

• = FALSE: avoid non-tabular row names.

• quote: control quoting of character data (write.csv quotes by default).

• na: specify how NA values should appear in the output.

2. Reading and Writing Excel Files

Excel remains pervasive in enterprise environments. R accommodates Excel interaction through multiple packages, each with distinct strengths.

2.1 Importing with readxl

The readxl package (by the tidyverse team) reads both .xls and .xlsx formats without external dependencies:

r

library(readxl)

# List available sheets
excel_sheets("data/raw/customer_data.xlsx")
#> [1] "2023_Q1" "2023_Q2" "Summary"

# Read a specific sheet
customer_q1 <- read_excel(
  "data/raw/customer_data.xlsx",
  sheet      = "2023_Q1",
  col_names  = TRUE,
  col_types  = c("date", "text", "numeric", "numeric"),
  skip       = 1
)

Key features:

• No Java dependency: unlike Java-based readers, readxl installs and works out of the box.

• Automatic type guessing: R infers column types but allows overrides via col_types.

• Range-based import: specify range = "A2:D100" to load subsets of large workbooks.

Handling quirks

• Merged cells: readxl reads the first cell’s value; avoid merged headers when possible.

• Hidden sheets: still detectable by excel_sheets() but omitted by default in some versions.

2.2 Writing with writexl

For straightforward Excel exports, writexl provides write_xlsx():

r

library(writexl)

write_xlsx(
  list(
    Q1 = customer_q1,
    Q2 = customer_q2,
    Summary = summary_table
  ),
  path = "data/processed/customer_report.xlsx"
)

Benefits:

• Multiple sheets: supply a named list of data frames.

• No external dependencies: pure R implementation.

• Fast: optimized for minimal memory overhead.

2.3 Advanced Excel with openxlsx

When you need custom formatting, formulas, or styling, openxlsx is the go-to package:

r

library(openxlsx)

wb <- createWorkbook()
addWorksheet(wb, "Summary")
writeData(wb, "Summary", summary_table, startRow = 2, startCol = 1)
addStyle(
  wb, "Summary",
  style = createStyle(fontSize = 12, textDecoration = "Bold"),
  rows = 2, cols = 1:ncol(summary_table), gridExpand = TRUE
)
saveWorkbook(wb, "data/processed/customer_report_formatted.xlsx", overwrite = TRUE)

Advanced capabilities:

• Cell formatting: fonts, colors, borders

• Conditional formatting: highlight cells based on values

• Formulas: embed Excel formulas directly

• Data validation: dropdowns, input restrictions

3. Other Tabular Formats (Optional)

While CSV and Excel cover most use cases, R’s ecosystem extends to modern formats optimized for speed and interoperability.

• JSON: jsonlite::fromJSON() / toJSON() for hierarchical data.

• Feather/Parquet: arrow::read_feather() / write_feather() and arrow::read_parquet() for columnar storage with zero-copy reads.

• HDF5: rhdf5 for large, hierarchical datasets.

Choose these formats when working with big data pipelines, cross-language sharing, or performance-critical applications.

4. Database Connections with DBI and RSQLite

For transactional data and multi-user environments, relational databases offer robustness, indexing, and concurrent access. R’s DBI package standardizes database interfacing, while RSQLite provides a lightweight, file-based engine.

4.1 Installing and Loading Packages

r

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

4.2 Establishing a Connection

r

# In-memory database
conn_mem <- dbConnect(RSQLite::SQLite(), ":memory:")

# File-based database
conn_file <- dbConnect(
  RSQLite::SQLite(),
  dbname = "data/database/sales_analysis.sqlite"
)

4.3 Listing and Inspecting Tables

r

dbListTables(conn_file)
#> [1] "customers" "orders" "products"
dbListFields(conn_file, "orders")

4.4 Reading Data

• dbReadTable() imports an entire table:

  r

  orders <- dbReadTable(conn_file, "orders")
  

• dbGetQuery() runs SQL queries and returns a data frame:

  r

  top_customers <- dbGetQuery(
    conn_file,
    "SELECT customer_id, SUM(amount) AS total_spent
     FROM orders
     GROUP BY customer_id
     ORDER BY total_spent DESC
     LIMIT 10"
  )
  

4.5 Writing Data

• dbWriteTable() writes a data frame to a table:

  r

  dbWriteTable(
    conn_file,
    "new_sales",
    sales_data,
    overwrite = TRUE
  )
  

• dbCreateTable() and dbAppendTable() for finer control.

4.6 Parameterized Queries and Transactions

r

# Parameterized query
stmt <- dbSendQuery(
  conn_file,
  "SELECT * FROM orders WHERE order_date BETWEEN ? AND ?"
)
dbBind(stmt, list("2023-01-01", "2023-03-31"))
quarter_orders <- dbFetch(stmt)
dbClearResult(stmt)

# Transactions
dbBegin(conn_file)
dbExecute(conn_file, "UPDATE products SET stock = stock - 1 WHERE product_id = 1001")
dbCommit(conn_file)

4.7 Performance Tips

• Indexes: create indexes on frequently filtered columns:

  r

  dbExecute(conn_file, "CREATE INDEX idx_date ON orders(order_date)")
  

• Chunked reads: fetch large results in batches via dbSendQuery() and dbFetch(n = 1000).

• Disconnect when done: dbDisconnect(conn_file) to release resources.

4.8 Connecting to Other Databases

DBI also supports MySQL, PostgreSQL, SQL Server, and more via dedicated backends:

• RMySQL / RMariaDB

• RPostgres

• odbc for ODBC-compliant sources

Example (PostgreSQL):

r

library(RPostgres)
pg_conn <- dbConnect(
  RPostgres::Postgres(),
  dbname   = "analytics",
  host     = "db.server.com",
  port     = 5432,
  user     = "dbuser",
  password = "securepass"
)

5. Best Practices and Considerations

1. Explicitly define column types when importing to avoid surprises.

2. Version-control your raw and processed data separately; never overwrite originals.

3. Automate workflows using R scripts or R Markdown to ensure reproducibility.

4. Document file provenance: record source URLs, timestamps, and extraction code.

5. Secure credentials: use environment variables or key management packages (keyring).

6. Monitor performance: profile I/O operations (profvis) and optimize slow reads/writes.

7. Validate imported data: check dimensions, column names, summary statistics.

6. Conclusion and Next Steps

You now have a robust toolkit for importing and exporting data in R:

• CSV: read.csv(), write.csv(), readr for speed

• Excel: readxl, writexl, openxlsx for formatting

• Databases: DBI, RSQLite, plus connectors for MySQL, PostgreSQL, and more

In the next post, we’ll explore Data Manipulation with dplyr and tidyr—transforming raw tables into analytical gold via filtering, joining, and reshaping. If you have questions about CSV quirks, Excel challenges, or database connections, drop a comment below. Share your own tips and package recommendations to help the community thrive. Happy importing and exporting!