Data Import & Export: CSV, JSON & Excel

Data Import & Export: CSV, JSON & Excel

Reading CSV Files

# Base R — works everywhere, slower
df_base <- read.csv("data.csv", stringsAsFactors = FALSE)

# readr — faster, better type inference, returns tibble
library(readr)
df <- read_csv("data.csv")  # automatically reads column types

# Key read_csv options:
df <- read_csv(
  "data.csv",
  col_types = cols(
    date    = col_date(format = "%Y-%m-%d"),
    price   = col_double(),
    product = col_character()
  ),
  na        = c("", "NA", "N/A", "null", "-"),  # what to treat as NA
  skip      = 2,       # skip first 2 lines (metadata/header)
  n_max     = 10000,   # limit rows for testing
  locale    = locale(encoding = "UTF-8")
)

# Inspect after reading
glimpse(df)   # tidyverse's str() — shows type and preview per column
problems(df)  # any parsing errors?

---

Writing CSV Files

library(readr)

# Write to CSV
write_csv(df, "output.csv")                    # no row names (cleaner)
write_csv(df, "output.csv", na = "")           # write NA as empty string
write_csv2(df, "output_eu.csv")               # semicolon separator (European)

# Base R (adds row numbers by default — usually not wanted)
write.csv(df, "output.csv", row.names = FALSE)

# Append to existing file
write_csv(new_rows, "output.csv", append = TRUE)

---

JSON Import and Export

library(jsonlite)

# Read a JSON file
json_text <- '
[
  {"id": 1, "name": "Alice", "scores": [95, 88, 92]},
  {"id": 2, "name": "Bob",   "scores": [78, 81, 75]},
  {"id": 3, "name": "Carol", "scores": [91, 94, 89]}
]'

data <- fromJSON(json_text)
class(data)       # "data.frame"
data$name         # c("Alice", "Bob", "Carol")
data$scores       # a list column — each element is a vector

# Extract nested list column
avg_scores <- sapply(data$scores, mean)
data$avg_score <- avg_scores

# Read from URL or file
# json_data <- fromJSON("https://api.example.com/data")
# json_data <- fromJSON("data.json")

# Write to JSON
output_json <- toJSON(data, pretty = TRUE, auto_unbox = TRUE)
cat(output_json)
# writeLines(output_json, "output.json")

# Control JSON output
toJSON(list(name = "Jordan", scores = c(95, 88)), pretty = TRUE)

---

Excel Files

library(readxl)

# Read first sheet
df <- read_excel("data.xlsx")

# Specific sheet and range
df <- read_excel(
  "data.xlsx",
  sheet   = "Sales 2025",     # or sheet number: sheet = 2
  range   = "A1:F100",        # Excel range
  col_names = TRUE,
  na      = c("", "N/A")
)

# List all sheet names
excel_sheets("data.xlsx")  # c("Summary", "Sales 2025", "Products")

# Write Excel (needs writexl or openxlsx)
library(writexl)
write_xlsx(list(Sales = df_sales, Products = df_products), "output.xlsx")

library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, "Sales")
writeData(wb, "Sales", df_sales)
addWorksheet(wb, "Summary")
writeData(wb, "Summary", summary_df)
saveWorkbook(wb, "output.xlsx", overwrite = TRUE)

---

R Native Formats

# RDS — single R object, preserves types exactly (including factors, dates)
write_rds(df, "data.rds", compress = "gz")  # compressed
df_back <- read_rds("data.rds")             # reads back with exact types

# RData — multiple objects in one file
save(df1, df2, model, "workspace.RData")
load("workspace.RData")  # restores df1, df2, model to environment

---

Handling Encoding and Large Files

# Detect encoding of a file
library(readr)
guess_encoding("data_unknown.csv")  # returns likely encoding

# Read with specific encoding
df <- read_csv("data_latin1.csv", locale = locale(encoding = "latin1"))

# Large files — streaming with vroom (fast) or chunked processing
library(vroom)
df <- vroom("large_file.csv")  # 1GB+ files in seconds using ALTREP

# Chunked processing without loading everything into memory
library(readr)
read_csv_chunked(
  "huge_file.csv",
  callback = DataFrameCallback$new(function(chunk, pos) {
    # process each chunk here
    chunk %>% filter(value > 0)
  }),
  chunk_size = 100000
)