# XML / HTML tables / Web scraping (static HTML)

Data import is a surprisingly large part of any data project. Below is a compact but practical reference of common file types, recommended packages/functions, example code, and real-world tips (encoding, memory, speed, cloud, DBs, troubleshooting).

---

## Quick workspace prep (repeat — but important)

`# Check working dir`  
`getwd()`  
  
`# Set working dir (if you must)`  
`setwd("/path/to/project")`  
  
`# Start with a clean environment (use with care)`  
`rm(list = ls())`  
  
`# Helpful: show files`  
`list.files()`

---

## Base R vs modern tidyverse / high-performance packages

* **Base R** (`read.table`, `read.csv`, `read.delim`) works everywhere and is flexible.
    
* **readr** (`read_csv`, `read_delim`) — faster, consistent parsing, tidyverse-friendly.
    
* **data.table::fread** — extremely fast for large CSV-like files.
    
* **readxl** — best for Excel (no external dependencies).
    
* **haven** — best for SAS/SPSS/Stata (via ReadStat).
    
* **jsonlite** — modern JSON parsing.
    
* **DBI + specific DB backends** — standard interface for relational DBs.
    
* **arrow** — for Feather/Parquet and interop with Python/Spark; excellent for large data and columnar formats.
    

---

## CSV / delimited text

### Recommended

* Small-medium files: `readr::read_csv()`
    
* Very large: `data.table::fread()` or `vroom::vroom()`
    

### Examples

`# readr - tidy defaults, prints parsing problems`  
`install.packages("readr")`  
`library(readr)`  
`df <- read_csv("data/sales.csv") # comma`  
`df2 <- read_delim("data/data.txt", "\t") # tab separated`  
  
`# data.table: blazing fast, automatic type detection`  
`install.packages("data.table")`  
`library(data.table)`  
`dt <- fread("data/sales.csv")`  
  
`# vroom: loads lazily and is fast for many files`  
`install.packages("vroom")`  
`library(vroom)`  
`df_v <- vroom("data/sales.csv")`

### Useful arguments

* `locale = locale(encoding = "UTF-8")` — handle encodings
    
* `col_types = cols(...)` — force column types (avoid mis-parsing)
    
* `na = c("", "NA", "NULL")` — specify missing tokens
    
* `n_max` — read only first N rows (quick inspect)
    

---

## Excel (XLS / XLSX)

### Recommended

* `readxl::read_excel()` — no Java dependency, reliable
    
* `tidyxl`, `openxlsx` — if you need formulas or writing
    

### Example

`install.packages("readxl")`  
`library(readxl)`  
  
`# read first sheet`  
`df <- read_excel("data/book.xlsx")`  
  
`# read a specific sheet by name or index`  
`df_sheet3 <- read_excel("data/book.xlsx", sheet = "Sales")`  
`df_sheet2 <- read_excel("data/book.xlsx", sheet = 2)`  
  
`# read a range`  
`df_range <- read_excel("data/book.xlsx", range = "A1:F100")`

---

## JSON

### Recommended

* `jsonlite::fromJSON()` — robust, converts arrays to data frames where appropriate
    

`install.packages("jsonlite")`  
`library(jsonlite)`  
  
`# from local file`  
`j <- fromJSON("data/doc.json", flatten = TRUE)`  
  
`# from URL`  
`j2 <- fromJSON("https://api.example.com/data")`

**Tip:** If JSON contains nested lists, use `flatten=TRUE` or manually `tidyr::unnest()` to tidy.

---

## XML / HTML tables / Web scraping (static HTML)

### Recommended

* `xml2` + `rvest` for HTML scraping / table extraction
    
* `XML` for more advanced XML parsing (but `xml2` recommended)
    

`install.packages(c("xml2","rvest"))`  
`library(xml2); library(rvest)`  
  
`page <- read_html("https://example.com/page-with-tables")`  
`tables <- html_table(html_nodes(page, "table")) # list of tibbles`

---

## Excel-like & legacy formats (ODS, Google Sheets)

* Google Sheets: `googlesheets4` (supports OAuth & public sheets)
    
* ODS: `readODS` package
    

`# Google Sheets (best to follow auth flow)`  
`install.packages("googlesheets4")`  
`library(googlesheets4)`  
`sheet <- read_sheet("https://docs.google.com/spreadsheets/...")`

---

## SAS / SPSS / Stata

### Recommended

* `haven` (fast, preserves labelled variables)
    
* `foreign` is older; `haven` preferred now
    

`install.packages("haven")`  
`library(haven)`  
  
`df_sas <- read_sas("data/data.sas7bdat")`  
`df_spss <- read_sav("data/data.sav")`  
`df_stata <- read_dta("data/data.dta")`

**Tip:** `haven` keeps value labels (`labelled` class). Use `labelled::to_factor()` to convert labeled columns to factors.

---

## MATLAB / Octave

* `R.matlab::readMat()` for `.mat` files
    
* `foreign::read.octave()` for Octave text data
    

`install.packages("R.matlab")`  
`library(R.matlab)`  
`m <- readMat("data/matrix.mat")`

---

## Parquet, Feather, Arrow — modern columnar formats

* Use `arrow` package to read/write Parquet and Feather; very efficient for large datasets and cross-language sharing.
    

`install.packages("arrow")`  
`library(arrow)`  
  
`# read parquet`  
`tbl <- read_parquet("data/data.parquet")`  
  
`# write parquet`  
`write_parquet(df, "out/data.parquet")`

**Benefit:** Arrow allows out-of-memory, zero-copy reads, and is ideal for large-scale pipelines.

---

## ODBC / Relational DBs (SQL Server, Postgres, MySQL, etc.)

### Recommended approach

* `DBI` + driver package (`RPostgres`, `RMySQL`, `odbc`) — standard interface
    
* `pool` for connection pooling in apps
    

### Example: Postgres via DBI

`install.packages(c("DBI","RPostgres"))`  
`library(DBI)`  
`con <- dbConnect(RPostgres::Postgres(),`  
`dbname = "mydb", host = "db.example.com",`  
`port = 5432, user = "me", password = "pw")`  
  
`# run a query`  
`df <- dbGetQuery(con, "SELECT * FROM schema.table LIMIT 1000")`  
  
`# fetch a big table in chunks`  
`res <- dbSendQuery(con, "SELECT * FROM big_table")`  
`chunk <- dbFetch(res, n = 10000)`  
`# iterate until exhausted`  
`dbClearResult(res)`  
  
`dbDisconnect(con)`

### ODBC (Windows / DSN)

`install.packages("odbc")`  
`library(DBI)`  
`con <- dbConnect(odbc::odbc(), DSN = "my_dsn", UID = "user", PWD = "pw")`  
`dbListTables(con)`  
`dbDisconnect(con)`

**Tip:** Avoid `SELECT *` on huge tables — use column selection and SQL-side filtering.

---

## Cloud storage (S3, GCS, Azure)

* **S3:** `aws.s3`, `arrow` can read directly from S3 URIs
    
* **Google Cloud Storage:** `gcsfs` (less common in R), or use `arrow`/`gcs` integration
    
* **Azure:** `AzureStor`
    

Example with `aws.s3`:

`install.packages("aws.s3")`  
`library(aws.s3)`  
`# must configure AWS credentials (env vars or ~/.aws/credentials)`  
`s3read_using(FUN = read.csv, object = "s3://my-bucket/data.csv")`

`arrow::read_parquet("s3://bucket/path/file.parquet")` also works when configured correctly.

---

## Reading in chunks / streaming large files

If data is too big to fit in memory:

* Use database imports (load into DB and query subsets)
    
* Use `data.table::fread()` with `select=` to read only columns
    
* Use `vroom` + `dplyr` for delayed reads
    
* For complex chunking: `readr::read_lines_chunked()` or process via `LaF` or `iotools` packages
    

Example chunked processing with `LaF`:

`install.packages("LaF")`  
`library(LaF)`  
`laf <- laf_open_csv("big.csv", column_types = c("integer","double","string"), nrows = 1e8)`  
`block <- laf[1:100000, ] # read first 100k rows`

---

## Parsing dates, times, and types

Parsing types properly avoids nasty surprises later.

* Use `col_types` in `readr` or `fread`'s `colClasses`
    
* Use `lubridate` for robust date parsing
    

`install.packages("lubridate")`  
`library(lubridate)`  
  
`# convert common formats`  
`df$date <- ymd(df$date_string) # "2023-07-01"`  
`df$datetime <- ymd_hms(df$ts) # "2023-07-01 08:45:00"`

---

## Encoding & locale issues

If you see weird characters:

`# readr`  
`df <- read_csv("file.csv", locale = locale(encoding = "latin1"))`  
  
`# base read.table`  
`df <- read.table("file.txt", fileEncoding = "UTF-8")`

Check `Encoding()` and `iconv()` to convert.

---

## Common import pitfalls and troubleshooting

* **Headers misaligned / comments:** use `skip = n`, `comment = "#"`, or `skip_empty_rows = TRUE`.
    
* **Inconsistent column types:** pre-specify column types (using `col_types` or `colClasses`) to avoid type coercion.
    
* **NA tokens:** set `na = c("", "NA", "n/a")`.
    
* **Thousands separators / decimal:** specify `locale = locale(decimal_mark = ",", grouping_mark = ".")`.
    
* **Memory errors:** filter on SQL side, load a subset, or use `arrow`/Parquet to read columns only.
    

---

## Quick hacks & productivity tips

* **Clipboard:** quick copy-paste into R (Windows/Mac):
    

`df <- read.table("clipboard", header = TRUE, sep = "\t")`

* **Peek first rows** before full import:
    

`readr::read_lines("file.csv", n_max = 10)`

* **Inspect parsing problems** with `readr::problems()` after `read_csv()`.
    
* **Use** `col_types = cols()` to force types and prevent guessing mistakes.
    
* **Use projects & relative paths** (RStudio projects) to avoid brittle `setwd()`.
    

---

## Recommended import pipeline (small project)

1. Inspect the file (`read_lines`, `head`, `file.info()`).
    
2. Try a small sample read (`n_max`, `skip`).
    
3. Specify explicit `col_types` or `colClasses`.
    
4. Load with a high-performance reader (`fread`/`vroom`/`arrow`) for big data.
    
5. Convert dates and factor levels with `lubridate` and `forcats`.
    
6. Persist as Parquet (`arrow`) if reusing: `arrow::write_parquet(df, "data.parquet")`.
    

---

## Example: end-to-end — CSV → cleaned parquet

`library(data.table)`  
`library(lubridate)`  
`library(arrow)`  
  
`# fast read`  
`dt <- fread("raw/sales.csv", na.strings = c("", "NA"))`  
  
`# clean types`  
`dt[, order_date := ymd(order_date)]`  
`dt[, amount := as.numeric(amount)]`  
  
`# sample check`  
`head(dt)`  
  
`# persist for faster reads later`  
`write_parquet(dt, "clean/sales.parquet")`

---

## Useful packages summary (short cheat-sheet)

TaskPackage / Function

Fast CSV read

`data.table::fread()`

Tidy read

`readr::read_csv()`

Excel

`readxl::read_excel()`

JSON

`jsonlite::fromJSON()`

SPSS/Stata/SAS

`haven::read_sav()`, `read_dta()`, `read_sas()`

Parquet / Arrow

`arrow::read_parquet()` / `write_parquet()`

DB connections

`DBI` + `RPostgres` / `odbc`

S3

`aws.s3`, `arrow`

Google Sheets

`googlesheets4`

HTML tables

`rvest::html_table()`

Chunked reading

`LaF`, `readr::read_lines_chunked()`

Labelled variables

`labelled`

---

## Final notes & next steps

* Choose the right tool for your dataset size: `fread`/`vroom`/`arrow` for big files; `readr`/`readxl` for comfort and parsing diagnostics.
    
* Preserve raw files and create a reproducible import script (save `col_types`, date parsing logic).
    
* For production pipelines, prefer Parquet/Arrow or a DB as canonical storage (fast, typed, efficient).
    
* If you want, I can convert any of your examples above into a runnable RMarkdown notebook with sample files and tests.  
      
      
    At Perceptive Analytics, our mission is “to enable businesses to unlock value in data.” For over 20 years, we’ve partnered with more than 100 clients—from Fortune 500 companies to mid-sized firms—to solve complex data analytics challenges. Our services include [**tableau consultancy**](https://www.perceptive-analytics.com/tableau-consultants/) and [**advanced analytics consulting**](https://www.perceptive-analytics.com/advanced-analytics-consultants/), turning data into strategic insight. We would love to talk to you. Do reach out to us.