Title: Work with Microsoft Access Files
Version: 0.2.1
Description: Use the open source 'MDB Tools' utilities https://github.com/mdbtools/mdbtools/. Primarily used for converting proprietary Microsoft Access files to simple text files and then reading those as data frames.
License: GPL-3
URL: https://k5cents.github.io/mdbr/, https://github.com/k5cents/mdbr
BugReports: https://github.com/k5cents/mdbr/issues
Imports: readr
Suggests: testthat
Encoding: UTF-8
RoxygenNote: 7.3.1
SystemRequirements: mdbtools: mdbtools (deb).
NeedsCompilation: no
Packaged: 2024-03-12 02:49:25 UTC; kiernan
Author: Kiernan Nicholls ORCID iD [aut, cre, cph]
Maintainer: Kiernan Nicholls <k5cents@gmail.com>
Repository: CRAN
Date/Publication: 2024-03-12 08:00:02 UTC

Export an Access database table as a text file

Description

Convert the data of a table into a delimited text string. Save the string as a character vector or write it to a text file. This direct conversion makes it easy to read tables into R or a spreadsheet.

Usage

export_mdb(
  file,
  table,
  output = TRUE,
  delim = ",",
  quote = "\"",
  quote_escape = "double",
  col_names = TRUE,
  eol = "\n",
  date_format = "%Y-%m-%d %H:%M:%S"
)

Arguments

file

Path to the Microsoft Access file.

table

Name of the table, list with mdb_tables().

output

Path or connection to write to. Passed to the stdout argument of system2(). Possible values are "", to the R console (the default), NULL or FALSE (discard output), TRUE (capture the output in a character vector) or a character string naming a file.

delim

Delimiter used to separate values.

quote

Single character used to quote strings. Defaults to ⁠"⁠.

quote_escape

The type of escaping to use for quoted values, one of "double", "backslash" or "none". You can also use FALSE, which is equivalent to "none". The default is "double", which is expected format for Excel.

col_names

If FALSE, column names will not be included at the top of the file. If TRUE, column names will be included.

eol

The end of line character to use. Most commonly either "\n" for Unix style newlines, or "\r\n" for Windows style newlines.

date_format

The format in which date columns are converted. MDB Tools uses the strftime(3) format, similar to readr::parse_date(). No need to specify whole string. Defaults to ISO8601.

Value

Character string, invisible if path to file.

Examples

## Not run: 
export_mdb(mdb_example(), "Airlines", output = TRUE)

## End(Not run)

Get path to mdbr example

Description

mdbr comes bundled with a sample file from the nycflights13 package in its inst/extdata directory. This function make it easy to access.

Usage

mdb_example(path = "nycflights13.mdb")

Arguments

path

path to the Microsoft Access file.

Specification for columns in a table

Description

Used to determine the column types for read_mdb(). Passed to col_types in readr::read_delim().

Usage

mdb_schema(file, table, condense = FALSE)

Arguments

file

Path to the Microsoft Access file.

table

Name of the table, list with mdb_tables().

condense

Should readr::cols_condense() be called on the spec?

Value

A readr cols specification list.

Examples

## Not run: 
mdb_schema(mdb_example(), "Flights", condense = TRUE)

## End(Not run)

List tables in a Microsoft Access database

Description

List tables in a Microsoft Access database

Usage

mdb_tables(file)

Arguments

file

Path to the Microsoft Access file.

Value

A character vector of table names.

Read a table as data frame

Description

Use export_mdb() to write a table as a temporary CSV file, which is then read as a data frame using readr::read_delim().

Usage

read_mdb(file, table, col_names = TRUE, col_types = NULL, ...)

Arguments

file

Path to the Microsoft Access file.

table

Name of the table, list with mdb_tables().

col_names

Whether or not to suppress column names from the table.

col_types

One of NULL, a readr::cols() specification, or a string. See vignette("readr") for more details. Use TRUE to create a generic readr::cols() specification with mdb_schema().

...

Additional arguments passed to readr::read_delim().

Value

A data frame.

Examples

## Not run: 
read_mdb(mdb_example(), "Airlines")

## End(Not run)