The goal of mdbr is to easily access the open source MDB Tools written by Brian Bruns. The MDB Tools command line utilities take proprietary Microsoft Access files and convert them to standard text files. This package is experimental and has only been tested on simple MDB databases.
Installation
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")The user must install MDB Tools separately. For example, users on Debian systems can install the tools from the apt repository.
On MacOS, the tools can be installed using homebrew.
More installation methods can be found on the package’s README.
Example
The package comes with a version of the nycflights13 relational database found with mdb_examples().
The tables in a database can be listed with mdb_tables().
mdb_tables(ex <- mdb_example())
#> [1] "Airlines" "Airports" "Flights" "Planes"These tables can be exported as a delimited string or file.
string <- export_mdb(ex, "Airlines", path = TRUE, delim = "|", quote = "'")
cat(string, sep = "\n")
#> carrier|name
#> '9E'|'Endeavor Air Inc.'
#> 'AA'|'American Airlines Inc.'
#> 'AS'|'Alaska Airlines Inc.'
#> 'B6'|'JetBlue Airways'
#> 'DL'|'Delta Air Lines Inc.'
#> 'EV'|'ExpressJet Airlines Inc.'
#> 'F9'|'Frontier Airlines Inc.'
#> 'FL'|'AirTran Airways Corporation'
#> 'HA'|'Hawaiian Airlines Inc.'
#> 'MQ'|'Envoy Air'
#> 'OO'|'SkyWest Airlines Inc.'
#> 'UA'|'United Air Lines Inc.'
#> 'US'|'US Airways Inc.'
#> 'VX'|'Virgin America'
#> 'WN'|'Southwest Airlines Co.'
#> 'YV'|'Mesa Airlines Inc.'Tables can be easily converted to a temporary file and read immediately.
read_mdb(ex, "Airports")
#> # A tibble: 1,458 × 8
#> faa name lat lon alt tz dst tzone
#> <chr> <chr> <dbl> <dbl> <int> <int> <chr> <chr>
#> 1 04G Lansdowne Airport 41.1 -80.6 1044 -5 A America/New_York
#> 2 06A Moton Field Municipal Airport 32.5 -85.7 264 -6 A America/Chicago
#> 3 06C Schaumburg Regional 42.0 -88.1 801 -6 A America/Chicago
#> 4 06N Randall Airport 41.4 -74.4 523 -5 A America/New_York
#> 5 09J Jekyll Island Airport 31.1 -81.4 11 -5 A America/New_York
#> 6 0A9 Elizabethton Municipal Airport 36.4 -82.2 1593 -5 A America/New_York
#> 7 0G6 Williams County Airport 41.5 -84.5 730 -5 A America/New_York
#> 8 0G7 Finger Lakes Regional Airport 42.9 -76.8 492 -5 A America/New_York
#> 9 0P2 Shoestring Aviation Airfield 39.8 -76.6 1000 -5 U America/New_York
#> 10 0S9 Jefferson County Intl 48.1 -123. 108 -8 A America/Los_Angeles
#> # ℹ 1,448 more rowsWhen reading a converted table, you might need to know what types of data to expect from each column.
The schema of database tables describes the column types.
mdb-schema -T Airports nycflights13.mdb
#> -- ----------------------------------------------------------
#> -- MDB Tools - A library for reading MS Access database files
#> -- Copyright (C) 2000-2011 Brian Bruns and others.
#> -- Files in libmdb are licensed under LGPL and the utilities under
#> -- the GPL, see COPYING.LIB and COPYING files respectively.
#> -- Check out http://mdbtools.sourceforge.net
#> -- ----------------------------------------------------------
#>
#> CREATE TABLE [Airports]
#> (
#> [faa] Text (510),
#> [name] Text (510),
#> [lat] Double,
#> [lon] Double,
#> [alt] Long Integer,
#> [tz] Integer,
#> [dst] Text (510),
#> [tzone] Text (510)
#> );This information can be interpreted as a readr spec object, which is used to accurately parse columns of a converted text file.
mdb_schema(ex, "Airports", condense = TRUE)
#> cols(
#> .default = col_character(),
#> lat = col_double(),
#> lon = col_double(),
#> alt = col_integer(),
#> tz = col_integer()
#> )
