Data Import and Export

library(readr)
library(dplyr)

# Basic CSV import with column type specification
sales <- read_csv("sales_data.csv",
  col_types = cols(
    date     = col_date(format = "%Y-%m-%d"),
    revenue  = col_double(),
    region   = col_character(),
    units    = col_integer(),
    category = col_factor(levels = c("Electronics","Clothing","Food"))
  ))

# Check for parsing problems
problems(sales)  # Shows rows that failed to parse
spec(sales)      # Show the column specification used

# Large file: read in chunks
read_csv_chunked("large_file.csv",
  callback = DataFrameCallback$new(function(chunk, pos) {
    # Process each chunk (1000 rows at a time)
    chunk %>% filter(revenue > 1000)
  }),
  chunk_size = 1000)

library(readxl)

# Read Excel file
df <- read_excel("financial_report.xlsx")
df <- read_excel("report.xlsx",
                  sheet = "Sales 2024",    # Sheet by name
                  range = "B2:G50",        # Cell range
                  col_names = TRUE,
                  na = c("", "N/A", "-"))  # NA values

# Multiple sheets
sheets <- excel_sheets("report.xlsx")  # List all sheet names
all_data <- lapply(sheets, function(s) read_excel("report.xlsx", sheet=s))
names(all_data) <- sheets

# Write Excel
library(writexl)
write_xlsx(list(Sales=sales_df, Inventory=inv_df), "output.xlsx")

# openxlsx — formatted Excel output
library(openxlsx)
wb <- createWorkbook()
addWorksheet(wb, "Summary")
writeData(wb, "Summary", data=summary_df, startRow=2)
addStyle(wb, "Summary", style=createStyle(fontColour="#FFFFFF",
                                           fgFill="#1565C0", bold=TRUE),
         rows=2, cols=1:ncol(summary_df), gridExpand=TRUE)
saveWorkbook(wb, "formatted_report.xlsx", overwrite=TRUE)

library(jsonlite)

# Simple JSON
json_str <- &#039;{"name": "Alice", "age": 30, "scores": [85, 92, 78]}'
data <- fromJSON(json_str)
data$name    # "Alice"
data$scores  # 85 92 78

# JSON file or URL
df <- fromJSON("api_response.json", flatten=TRUE)  # Flatten nested JSON
df <- fromJSON("https://api.example.com/data")     # Directly from URL

# Convert R object to JSON
r_list <- list(employees=employees, summary=dept_summary)
json_output <- toJSON(r_list, pretty=TRUE, auto_unbox=TRUE)
write(json_output, "export.json")

cat(json_output)

library(DBI)
library(RMySQL)  # or RSQLite, RPostgres, odbc

# MySQL connection
con <- dbConnect(RMySQL::MySQL(),
                  host     = "localhost",
                  dbname   = "company_db",
                  user     = "root",
                  password = "password",
                  port     = 3306)

# Query to data frame
employees <- dbGetQuery(con, "
  SELECT e.name, e.salary, d.dept_name
  FROM employees e
  JOIN departments d ON e.dept_id = d.id
  WHERE e.salary > 50000
  ORDER BY e.salary DESC
")

# Parameterized query (prevent SQL injection)
dept_query <- "SELECT * FROM employees WHERE dept = ?"
it_employees <- dbGetQuery(con, dept_query, params=list("IT"))

# Write R data frame to database table
dbWriteTable(con, "r_analysis_results", results_df, overwrite=TRUE)

# List tables
dbListTables(con)

# Close connection
dbDisconnect(con)

# SQLite (no server needed — great for local projects)
library(RSQLite)
con <- dbConnect(SQLite(), "local_data.sqlite")