)
The goal of mdbr is to easily access the open source MDB Tools written by Brian Bruns. The MDB Tools C library is now bundled with the package — no external installation is required. This package reads proprietary Microsoft Access files directly and returns standard R data frames.
You can install the release version of mdbr from CRAN.
install.packages("mdbr")The development version can be installed from GitHub.
# install.packages("remotes")
remotes::install_github("k5cents/mdbr")library(mdbr)The package bundles the Northwind Access
database, including related tables, foreign keys, and non-ASCII
names. Find it with mdb_example().
The tables in a database can be listed with
mdb_tables().
mdb_tables(ex <- mdb_example())
#> [1] "Order Details" "Orders" "Products" "Shippers" "Categories" "Customers"
#> [7] "Employees" "Suppliers" "Umsätze"These tables can be exported as a delimited string or file.
string <- export_mdb(ex, "Shippers", output = TRUE, delim = "|", quote = "'")
cat(string, sep = "\n")
#> ShipperID|CompanyName|Phone
#> '1'|'Speedy Express'|'(503) 555-9831'
#> '2'|'United Package'|'(503) 555-3199'
#> '3'|'Federal Shipping'|'(503) 555-9931'Tables are read directly into R as a tibble with automatic type coercion.
read_mdb(ex, "Shippers")
#> # A tibble: 3 × 3
#> ShipperID CompanyName Phone
#> <int> <chr> <chr>
#> 1 1 Speedy Express (503) 555-9831
#> 2 2 United Package (503) 555-3199
#> 3 3 Federal Shipping (503) 555-9931The DDL for a table can be retrieved with
mdb_schema(mode = "ddl").
mdb_schema(ex, "Shippers", mode = "ddl")
#> [Shippers]
#> -- That file uses encoding UTF-8
#>
#> CREATE TABLE [Shippers]
#> (
#> [ShipperID] Long Integer,
#> [CompanyName] Text (40) NOT NULL,
#> [Phone] Text (24)
#> );Column types are returned as a readr col
spec (requires the readr package). Use
condense = TRUE to collapse columns sharing a type.
mdb_schema(ex, "Shippers")
#> cols(
#> ShipperID = col_integer(),
#> CompanyName = col_character(),
#> Phone = col_character()
#> )