Reading and writing data

Load CSV, Excel and database data with the right defaults, and choose between a portable CSV and a faithful RDS when you save.

Reading flat files

# base R
df_base <- read.csv("sales.csv", stringsAsFactors = FALSE, fileEncoding = "UTF-8-BOM")

# readr: faster, no row names, no silent factor conversion
library(readr)
df <- read_csv("sales.csv", col_types = cols(
  date = col_date(),
  amount = col_double(),
  region = col_character()
))

# quoting and separators need to be explicit when they are not the defaults
wide <- read_delim("export.psv", delim = "|", quote = "'", locale = locale(encoding = "latin1"))

Reader	Package	Good for
`read.csv`	base	Quick inspection; returns a data frame
`read_csv`	readr	Speed, tibbles, explicit column types
`read_delim`	readr	Non-standard delimiters and quoting
`read_excel`	readxl	Excel workbooks, sheets and cell ranges
`read_sheet`	googlesheets4	Google Sheets by URL
`dbGetQuery`	DBI plus a driver	Databases, with SQL done server-side

Column types matter. If you let the reader guess, a column of postal codes becomes numeric and loses its leading zeros, and a column of mixed values becomes text. Declare the types you expect.

Databases

library(DBI)
con <- dbConnect(RPostgres::Postgres(),
                 dbname = "app", host = "localhost",
                 user = Sys.getenv("DB_USER"), password = Sys.getenv("DB_PASSWORD"))

df <- dbGetQuery(con, "SELECT id, region, amount FROM orders WHERE amount > $1",
                 params = list(100))

# use parameters, never paste strings into SQL
dbDisconnect(con)

Parameter placeholders differ by driver: $1 for PostgreSQL, ? for SQLite and MySQL.
dbplyr lets you write dplyr verbs against a database and translates them to SQL, which is useful when the table is too large to pull into memory.
Always close connections; a leaked connection holds a server-side slot until the session ends.

Saving

# CSV: portable, but loses types and factor levels
write.csv(df, "out.csv", row.names = FALSE, na = "", fileEncoding = "UTF-8")

# RDS: exact R object, compressed, keeps types, factors and attributes
saveRDS(df, "out.rds")
df2 <- readRDS("out.rds")

# RData: several objects at once, into the global environment
save(df, model, file = "workspace.RData")

# write into a subdirectory that may not exist yet
fs::dir_create("build")
write_csv(df, "build/out.csv")

⚠️

write.csv writes row names by default, which adds an unnamed first column that reappears as data when someone reads the file back. Always pass row.names = FALSE for CSV, and prefer readr::write_csv, which never writes them.

FAQ

CSV or RDS for intermediate results?

RDS when the data only ever goes back into R: it preserves column types, factor levels, time zones and list columns exactly. CSV when a human or another tool needs to read it, accepting that types are lost.

My file has a BOM and the first column name looks wrong. What now?

The byte-order mark is being treated as part of the first header. Read with fileEncoding = "UTF-8-BOM" in base R, or use readr, which handles the BOM by default.

Vectors, factors and data frames Tidyverse workflow: dplyr and tidyr

Last refreshed 2026-09-18.

Reading and writing data

Reading flat files

Databases

Saving

FAQ

Related