Why we have two generations of import tools

R ships with import functions like read.csv() in every installation — no packages needed. These work, but they have quirks that cause silent errors: older versions silently converted text columns to factors, they produce data.frame objects with sometimes-awkward behavior, and they give you no feedback when something goes wrong.

The tidyverse import functions (read_csv(), read_excel(), etc.) were built to fix these problems. They are faster, more consistent, and — most importantly — they tell you when something doesn’t parse correctly instead of silently producing NA. We will use these throughout the course.

The import toolkit at a glance

Quick-reference: the tidyverse import toolkit

Before you read: look at the raw file

The single most important import habit is inspecting your file before you try to read it. Opening a CSV in a plain-text editor (not Excel) or running read_lines() in R lets you answer five questions that determine how your import call should be written:

1. What delimiter is used? (comma, semicolon, tab, pipe?)
2. How many rows of metadata or comments appear before the column names?
3. What string does the file use for missing values? (NA, -9999, “N/A”, blank, something else?)
4. Are the column names in the first row, or is there a units row below them?
5. What format are any date or time columns in?

# Read the first 10 lines as raw text — no parsing, no interpretation
read_lines("data/air_quality.csv", n_max = 10)

The basics of read_csv()

Once you know the file structure, you can write an informed import call. Start with the minimal version and build up:

library(readr)

# Step 1: minimal call — readr guesses types and prints the spec
df <- read_csv("data/air_quality.csv")

# Step 2: ALWAYS inspect immediately after reading
glimpse(df)       # one line per column: type + first few values
head(df)          # first 6 rows
problems(df)      # any parsing failures — check this every time

Finding your file path with file.choose()

Typing a file path by hand is error-prone: a wrong slash, a typo, or not knowing where a file actually lives on your machine all produce the same unhelpful cannot open file error. file.choose() opens your operating system’s file-picker dialog and returns the full path as a string.

# Run this ONCE in the Console — not inside a chunk that knits
my_path <- file.choose()

# R prints the full path, e.g.:
# [1] "/Users/marc/Desktop/EA030/field_data.csv"
# On Windows:
# [1] "C:\\Users\\marc\\Desktop\\EA030\\field_data.csv"

my_path   # type the object name to see the path — then copy it

1. Run my_path <- file.choose() in the Console. Navigate to your file and click Open.
2. Type my_path in the Console and press Enter. R prints the full path.
3. Copy that path. In your Rmd chunk, paste it as a hard-coded string.
4. Use the object name (or the string directly) in every subsequent read_csv() call.

# In your Rmd chunk: hard-code the path you discovered in the Console
my_path <- "/Users/marc/EA030/data/field_data.csv"

df <- read_csv(my_path)

Locking in column types

After your first successful import, you should make the column types explicit so the import behaves identically no matter what future rows look like. The easiest way is to copy the spec that readr already printed:

# After reading once and confirming the types look correct,
# copy the spec and paste it into col_types = cols(...)
df <- read_csv(
  "data/air_quality.csv",
  col_types = cols(
    site_id      = col_character(),
    date         = col_date(format = "%Y-%m-%d"),
    pm25_ug_m3   = col_double(),
    flag         = col_character(),
    notes        = col_character()
  )
)

Declaring NA strings

Field data files use every imaginable placeholder for missing values. If you do not list them, R will read them as character strings — silently converting your numeric column to chr type:

df <- read_csv(
  "data/field_data.csv",
  na = c("", "NA", "N/A", "n/a", "n.d.", "ND", "–", "—",
         "999", "-9999", ".", "NULL", "null", "missing")
)

Skipping junk rows

Many instrument exports have metadata before the column names. Use skip = to jump past them:

# Skip 4 metadata lines, then treat row 5 as the column-name header
df <- read_csv("data/licor_export.csv", skip = 4)

# If there are comment lines marked with "#" mixed in with the data
df <- read_csv("data/sensor_log.csv", skip = 3, comment = "#")

# If there is NO header row at all — you must supply names manually
df <- read_csv("data/raw_logger.csv",
               col_names = c("datetime", "temp_C", "rh_pct", "lux"),
               skip = 1)

Why this matters in environmental work

Field monitoring projects routinely generate one file per sensor, per site, per deployment, or per day. A water-quality study might produce 50 CSV files — one per sampling location. An air-quality network might produce one file per sensor per month. Loading these one at a time with fifty copy-pasted read_csv() calls is tedious, fragile, and error-prone.

The tidyverse approach is to treat the collection of file paths as a vector and use purrr::map() to apply read_csv() to each one. You then bind all the results into a single data frame for analysis.

Step-by-step: reading a folder of CSV files

Suppose you have a folder data/sensors/ containing one CSV per sensor station, all with the same column structure:

data/sensors/
  site_A_pm25.csv
  site_B_pm25.csv
  site_C_pm25.csv
  ...

Here is the workflow, piece by piece:

# ── Step 1: build a vector of file paths ─────────────────────────────────────
csv_files <- list.files(
  path       = "data/sensors/",    # folder to search
  pattern    = "\\.csv$",          # only files ending in .csv
  full.names = TRUE                # return the full path, not just the filename
)

csv_files   # inspect: you should see a character vector of paths

# ── Step 2: read each file and stack into one data frame ─────────────────────
library(purrr)

all_data <- csv_files |>
  map(\(f) read_csv(f, col_types = cols(.default = col_character()))) |>
  list_rbind()

# col_types = cols(.default = col_character()) reads everything as text on
# the first pass. This prevents one file's format quirks (e.g., a flag string
# in a numeric column) from forcing that column to NA across all files.

# ── Step 3: re-infer types on the combined data frame ────────────────────────
all_data <- all_data |>
  type_convert()   # readr re-guesses types now that all rows are present

# ── Step 4: inspect ───────────────────────────────────────────────────────────
glimpse(all_data)
nrow(all_data)      # should be the sum of rows across all files
problems(all_data)  # any parsing failures?

Checking for column-name consistency

# Read only the first row of each file — fast way to check column names
column_names_per_file <- csv_files |>
  map(\(f) names(read_csv(f, n_max = 1, show_col_types = FALSE)))

# Are all files identical?
all(sapply(column_names_per_file, identical, column_names_per_file[[1]]))
# TRUE = safe to stack; FALSE = find which files differ

# Find the differing files
column_names_per_file[!sapply(column_names_per_file,
                              identical, column_names_per_file[[1]])]

The problem: where did each row come from?

After stacking dozens of files with map() and list_rbind(), every row looks identical in structure — there is nothing in the data itself that tells you which file a row came from. But the file name often encodes critical metadata: the site ID, the deployment date, the sensor serial number, the sampling location.

Consider this folder:

data/sensors/
  LakeElsinore_2023-06.csv
  LakeElsinore_2023-07.csv
  Riverside_2023-06.csv
  Riverside_2023-07.csv

The site name and month are encoded in the file name. If you stack these files without extracting that information, you lose it.

Method 1: add a source_file column, parse it later

The simplest approach: add the full file path (or just the file name) as a column, then use stringr or tidyr to extract the pieces you need.

library(purrr)
library(stringr)

csv_files <- list.files("data/sensors/", pattern = "\\.csv$", full.names = TRUE)

all_data <- csv_files |>
  # Name the vector with just the bare file name (no path, no extension)
  set_names(~ tools::file_path_sans_ext(basename(.x))) |>
  map(\(f) read_csv(f, col_types = cols(.default = col_character()),
                    show_col_types = FALSE)) |>
  list_rbind(names_to = "source_file")
  # names_to = "source_file" creates a column using the names we set above

Method 2: parse file name components directly into columns

When the file name follows a predictable pattern (e.g., SITE_YYYY-MM.csv), you can extract the components inside the same pipeline using mutate() and str_extract() or separate():

all_data <- csv_files |>
  set_names(~ tools::file_path_sans_ext(basename(.x))) |>
  map(\(f) read_csv(f, col_types = cols(.default = col_character()),
                    show_col_types = FALSE)) |>
  list_rbind(names_to = "source_file") |>
  # ── Parse the file name into site and year-month ────────────────────────
  mutate(
    site      = str_extract(source_file, "^[^_]+"),          # everything before first _
    yearmonth = str_extract(source_file, "\\d{4}-\\d{2}$")   # YYYY-MM at end
  ) |>
  # ── Convert types now that we have clean columns ────────────────────────
  type_convert()

Method 3: inject metadata from a lookup table

Sometimes the file name contains a code (e.g., S01, S02) rather than a human-readable label. You can match codes to a lookup table with left_join():

# A lookup table you create separately
site_lookup <- tibble::tribble(
  ~file_code, ~site_name,        ~latitude, ~longitude,
  "S01",      "Lake Elsinore",   33.668,    -117.332,
  "S02",      "Riverside",       33.953,    -117.396,
  "S03",      "Mira Loma",       33.986,    -117.519
)

csv_files <- list.files("data/sensors/", pattern = "\\.csv$", full.names = TRUE)

all_data <- csv_files |>
  set_names(~ tools::file_path_sans_ext(basename(.x))) |>
  map(\(f) read_csv(f, col_types = cols(.default = col_character()),
                    show_col_types = FALSE)) |>
  list_rbind(names_to = "file_code") |>
  # ── Bring in site metadata from lookup table ────────────────────────────
  left_join(site_lookup, by = "file_code") |>
  type_convert()

Method 4: inject metadata inside the reading function

If the column you want is not in the file name but can be computed from the path, you can add it inside the reading function before the files are stacked:

# Example: add a column recording the file's modification date
read_with_meta <- function(filepath) {
  read_csv(filepath,
           col_types = cols(.default = col_character()),
           show_col_types = FALSE) |>
    mutate(
      source_file   = basename(filepath),
      file_mod_date = file.mtime(filepath)
    )
}

all_data <- csv_files |>
  map(read_with_meta) |>
  list_rbind() |>
  type_convert()

Putting it all together: a complete pattern

Here is the full recommended workflow for a realistic environmental dataset where files are named SITE_YYYY-MM.csv:

library(tidyverse)   # includes purrr, dplyr, stringr, readr

# 1. Find files
csv_files <- list.files(
  "data/sensors/",
  pattern    = "\\.csv$",
  full.names = TRUE
)

# 2. Verify names are consistent across all files
stopifnot(
  length(unique(
    map(csv_files, \(f) names(read_csv(f, n_max = 1, show_col_types = FALSE)))
  )) == 1
)

# 3. Read, stack, extract filename metadata, convert types
all_data <- csv_files |>
  set_names(~ tools::file_path_sans_ext(basename(.x))) |>
  map(\(f) read_csv(f, col_types = cols(.default = col_character()),
                    show_col_types = FALSE)) |>
  list_rbind(names_to = "source_file") |>
  mutate(
    site      = str_extract(source_file, "^[A-Za-z]+"),
    year      = as.integer(str_extract(source_file, "\\d{4}")),
    month     = as.integer(str_extract(source_file, "(?<=-)\\d{2}$"))
  ) |>
  type_convert() |>
  select(-source_file)   # drop once parsed, or keep for provenance

# 4. Sanity checks
glimpse(all_data)
n_distinct(all_data$site)    # how many sites?
range(all_data$date)          # plausible date range?
sum(is.na(all_data$pm25_ug_m3))  # NAs in key column

Basic usage

library(readxl)

# Step 1: list sheets before committing to one
excel_sheets("data/field_data.xlsx")

# Step 2: read a specific sheet by name (more robust than by position)
df <- read_excel("data/field_data.xlsx", sheet = "Site_A")

# Step 3: inspect immediately
glimpse(df)
problems(df)

Reading a specific range

When the data does not start at cell A1 — common with lab-formatted spreadsheets that have a title block at the top:

# Specify an Excel range directly (in Excel A1 notation)
df <- read_excel("data/lab_results.xlsx",
                 sheet = "Results",
                 range = "B4:H120")

# Or skip rows and limit the number of data rows
df <- read_excel("data/lab_results.xlsx",
                 skip    = 3,
                 n_max   = 200,
                 col_names = TRUE)

Forcing column types

readxl guesses types from the first few rows — if row 5 has "< LOD" in a numeric column, the whole column becomes chr:

# Provide one string per column in left-to-right order
df <- read_excel(
  "data/water_quality.xlsx",
  sheet     = "Data",
  col_types = c(
    "text",    # sample_id
    "date",    # collection_date  ← readxl uses "date" not col_date()
    "numeric", # nitrate_mg_l
    "numeric", # phosphorus_ug_l
    "text",    # flag
    "skip"     # internal_notes — skip entirely
  )
)

The Excel serial-date trap

Excel stores dates as the number of days since 1900-01-00. When a date column is read as numeric, you get values like 45290 instead of 2024-01-15:

# If you get mysterious 5-digit numbers in a date column:
df <- read_excel("data/old_data.xls") |>
  mutate(date_fixed = as.Date(date_col, origin = "1899-12-30"))
# Excel's epoch is 1899-12-30 due to a deliberate Lotus 1-2-3 compatibility
# bug that Microsoft kept for 40 years.

# See the conversion in action
excel_serial <- c(45292, 45293, 45294)
as.Date(excel_serial, origin = "1899-12-30")

## [1] "2024-01-01" "2024-01-02" "2024-01-03"

Merged cells and multiline headers

# Strategy 1: skip the merged-header rows, supply names manually
df <- read_excel(
  "data/merged_headers.xlsx",
  skip      = 2,
  col_names = c("site", "date", "temp_mean", "temp_sd",
                "ph_mean", "ph_sd", "do_mean", "do_sd")
)

# Strategy 2: read raw, then rename with janitor::clean_names()
df_raw <- read_excel("data/merged_headers.xlsx", col_names = FALSE)
# inspect: print(df_raw[1:3, ])
# then supply names manually based on what you see

Non-standard delimiters

# Tab-separated (common from GIS software and some instruments)
df <- read_tsv("data/gps_tracks.txt")

# Pipe-delimited
df <- read_delim("data/licor_6800.txt", delim = "|")

# Semicolon-delimited with European decimal comma
df <- read_csv2("data/hobo_logger.csv")   # ; sep, , decimal

# Space-separated with multiple spaces (fixed-width style)
df <- read_table("data/met_station.dat",
                 col_names = c("year","doy","time","temp","rh","ws","wd"))

Instrument files with metadata headers

Dataloggers often prepend metadata before the data rows. Use read_lines() to inspect first, then read with the right skip = value:

# Read the first 15 lines as raw text to understand the structure
read_lines("data/campbell_cr1000.dat", n_max = 15)

# Campbell Scientific CR1000: 4-line header (station name, field names,
# units, processing type); data starts on row 5
df <- read_csv(
  "data/campbell_cr1000.dat",
  skip      = 4,
  col_names = c("timestamp", "record_num", "temp_C", "rh_pct",
                "precip_mm", "windspeed_ms", "winddir_deg",
                "batt_v", "panel_temp"),
  na        = c("", "NA", "NAN", "7999", "-7999")
)

# HOBO Pendant / Onset loggers: 1-2 line header + units row
df <- read_csv("data/hobo_pendant.csv", skip = 1) |>
  janitor::clean_names()   # fixes "#", spaces, "()" in auto-generated names

Mixed data in one column

A common field data problem: a column that should be numeric contains occasional text flags ("< LOD", "offline", "> range"):

# Simulate the problem
mixed <- tibble::tibble(
  site    = c("A", "A", "A", "A", "A"),
  no3_mgl = c("1.2", "0.8", "< LOD", "1.5", "offline")
)

# Wrong approach: as.numeric() silently coerces to NA without preserving why
mixed |> dplyr::mutate(no3_num = as.numeric(no3_mgl))

## # A tibble: 5 × 3
##   site  no3_mgl no3_num
##   <chr> <chr>     <dbl>
## 1 A     1.2         1.2
## 2 A     0.8         0.8
## 3 A     < LOD      NA  
## 4 A     1.5         1.5
## 5 A     offline    NA

# Better approach: extract the flag, then convert
mixed |>
  dplyr::mutate(
    flag    = dplyr::case_when(
                stringr::str_detect(no3_mgl, "^[<>]")  ~ "below/above detection",
                no3_mgl == "offline"                   ~ "instrument offline",
                TRUE                                   ~ NA_character_
              ),
    no3_num = suppressWarnings(as.numeric(no3_mgl))
  )

## # A tibble: 5 × 4
##   site  no3_mgl flag                  no3_num
##   <chr> <chr>   <chr>                   <dbl>
## 1 A     1.2     <NA>                      1.2
## 2 A     0.8     <NA>                      0.8
## 3 A     < LOD   below/above detection    NA  
## 4 A     1.5     <NA>                      1.5
## 5 A     offline instrument offline       NA

Trailing commas and blank columns

Some instruments export a trailing comma on every row, creating a phantom extra column in R:

# Symptom: last column is named "...8" or similar and is all NA
df <- read_csv("data/sensor_with_trailing_comma.csv")
names(df)   # look for "...N" column names

# Fix: drop any column whose name starts with "..."
df <- df |>
  dplyr::select(!dplyr::starts_with("..."))

The three encodings you will encounter

Detecting encoding

# Option 1: readr's built-in guesser (works most of the time)
readr::guess_encoding("data/field_notes.csv")

# Option 2: the stringi package (more thorough)
stringi::stri_enc_detect(readr::read_file_raw("data/field_notes.csv"))

Specifying encoding on import

# For a Latin-1 file (common from older Windows programs)
df <- read_csv(
  "data/legacy_data.csv",
  locale = locale(encoding = "latin1")
)

# For Windows-1252 (EPA AQS downloads, some USGS exports)
df <- read_csv(
  "data/epa_aqs_download.csv",
  locale = locale(encoding = "windows-1252")
)

The UTF-8 BOM problem

Some Windows applications prepend a Byte Order Mark (BOM) — three invisible bytes — to UTF-8 files. R may read the first column name as "site_id" or "\xef\xbb\xbfsite_id":

# readr handles this automatically with UTF-8-BOM:
df <- read_csv("data/bom_file.csv",
               locale = locale(encoding = "UTF-8-BOM"))

# Or strip the BOM after the fact:
names(df)[1] <- stringr::str_remove(names(df)[1], "^\xef\xbb\xbf")

Why datetimes are hard

The lubridate parsing approach

lubridate provides functions named after the order of the components:

library(lubridate)

ymd("2024-03-15")               # ISO 8601 — always prefer this format

## [1] "2024-03-15"

mdy("03/15/2024")               # US format

## [1] "2024-03-15"

dmy("15/03/2024")               # European format

## [1] "2024-03-15"

mdy("March 15, 2024")           # Long month name

## [1] "2024-03-15"

ymd_hms("2024-03-15 14:32:00")  # datetime with seconds

## [1] "2024-03-15 14:32:00 UTC"

mdy_hm("03/15/2024 14:32")      # datetime, no seconds

## [1] "2024-03-15 14:32:00 UTC"

# Works on vectors — safe to use inside mutate()
# Returns NA for unparseable rows (with a warning) — does NOT crash
dates <- c("2024-01-15", "2024-02-20", "2024-03-05", "not-a-date")
ymd(dates)

## [1] "2024-01-15" "2024-02-20" "2024-03-05" NA

The strptime / format-string reference

When you must use format = inside read_csv() col_date():

as.Date("15/03/2024",    format = "%d/%m/%Y")

## [1] "2024-03-15"

as.Date("Mar 15, 2024",  format = "%b %d, %Y")

## [1] "2024-03-15"

as.Date("20240315",      format = "%Y%m%d")

## [1] "2024-03-15"

lubridate::ymd_hms("2024-03-15 14:32:00", tz = "America/Los_Angeles")

## [1] "2024-03-15 14:32:00 PDT"

Timezone nightmares

t_utc  <- ymd_hms("2024-03-15 22:00:00", tz = "UTC")
t_pst  <- with_tz(t_utc, "America/Los_Angeles")
t_est  <- with_tz(t_utc, "America/New_York")

tibble::tibble(
  label    = c("UTC", "Los Angeles (PST)", "New York (EST)"),
  datetime = c(format(t_utc, "%Y-%m-%d %H:%M:%S %Z"),
               format(t_pst, "%Y-%m-%d %H:%M:%S %Z"),
               format(t_est, "%Y-%m-%d %H:%M:%S %Z"))
)

## # A tibble: 3 × 2
##   label             datetime               
##   <chr>             <chr>                  
## 1 UTC               2024-03-15 22:00:00 UTC
## 2 Los Angeles (PST) 2024-03-15 15:00:00 PDT
## 3 New York (EST)    2024-03-15 18:00:00 EDT

# CRITICAL DISTINCTION:
# force_tz()  — "this timestamp WAS recorded in timezone X" (no time shift)
# with_tz()   — "show me this UTC time in timezone X"      (display shift only)

raw_logger_time <- ymd_hms("2024-03-15 14:32:00")   # no tz → UTC assumed

correct <- force_tz(raw_logger_time, "America/Los_Angeles")  # correct
wrong   <- with_tz(raw_logger_time, "America/Los_Angeles")   # WRONG: shifts time

tibble::tibble(
  approach   = c("raw (no tz declared)", "force_tz — correct", "with_tz — WRONG"),
  time_shown = c(format(raw_logger_time, "%H:%M %Z"),
                 format(correct, "%H:%M %Z"),
                 format(wrong,   "%H:%M %Z"))
)

## # A tibble: 3 × 2
##   approach             time_shown
##   <chr>                <chr>     
## 1 raw (no tz declared) 14:32 UTC 
## 2 force_tz — correct   14:32 PDT 
## 3 with_tz — WRONG      07:32 PDT

Multiple datetime formats in one column

mixed_dates <- c(
  "2024-01-15",      # ISO
  "01/20/2024",      # US m/d/Y
  "25-Jan-2024",     # d-Mon-Y
  "February 3 2024", # long month
  "2024.02.10"       # dot separator
)

parse_date_time(mixed_dates,
                orders = c("Ymd", "mdY", "dBY", "BdY", "Y.m.d"),
                quiet  = TRUE)

## [1] "2024-01-15 UTC" "2024-01-20 UTC" "2024-01-25 UTC" "2024-02-03 UTC"
## [5] "2024-02-10 UTC"

Post-import sanity checks as a standard habit

# Run these after every import, before any analysis
stopifnot(nrow(df) > 0)                               # not empty
stopifnot(!all(is.na(df$pm25_ug_m3)))                 # key column not all NA
stopifnot(inherits(df$datetime, c("POSIXct","Date"))) # datetime parsed

range(df$date)               # plausible date range?
nrow(df)                     # row count
n_distinct(df$sensor)        # how many sensors?
sum(is.na(df$pm25_ug_m3))    # NAs in key column

What’s next?

• Functional Programming — use purrr::map() more deeply, write your own functions, and automate repetitive import pipelines
• Data Wrangling — clean and reshape after import with dplyr and tidyr
• Visualization — plot your imported data with ggplot2