Provides data from an MS Access database table in forms of a data frame.
Usage
hsGetTable(
mdb,
tbl,
cond = "TRUE",
fields = "*",
dbg = TRUE,
check = TRUE,
use2007Driver = NULL,
...
)Arguments
- mdb
full path to MS Access database file (extension “.mdb” or “.accdb”) or MS Excel file (extension “.xls” or “.xlsx”).
- tbl
Table name. Put it into brackets [] if it contains spaces and if it mdb does not point to a MySQL database
- cond
Condition string.
- fields
Comma separated list of names of fields to be selected.
- dbg
if TRUE, debug messages are shown, else not
- check
if TRUE (default), tbl is checked for existence in mdb before trying to get the data and a list of available tables is shown in the case that the table does not exist.
- use2007Driver
passed to
hsTablesandhsSqlQuery- ...
Additional arguments to be passed to hsSqlQuery
Details
ATTENTION: This function may not return what you want if the table contains a
timestamp field. Use hsMdbTimeSeries instead.
See also
hsSqlQuery, hsPutTable, hsGetTimeSeries,
hsMdbTimeSeries
Examples
if (FALSE) {
## Get all datasets from tbl_Hyd in example database where
## Q > 1.0 m3/s and temperature > 20 degree Celsius
## (only on Windows!)
if (.Platform$OS.type == "windows") {
ts <- hsGetTable(xmdb(), "tbl_Hyd", "Q > 1.0 AND T_Kanal > 20")
head(ts)
}
## Output:
# Zeitst Q v H T_Kanal
# 1 2011-08-24 22:33:00 1.075 0.459 1.366 20.1
# 2 2011-08-24 22:34:00 1.062 0.453 1.370 20.2
# 3 2011-08-24 22:35:00 1.050 0.449 1.364 20.2
# 4 2011-08-24 22:36:00 1.042 0.446 1.361 20.3
# 5 2011-08-24 22:37:00 1.032 0.443 1.354 20.3
# 6 2011-08-24 22:38:00 1.010 0.436 1.348 20.4
## TAKE CARE when getting time-series data:
if (.Platform$OS.type == "windows") {
hsGetTable(xmdb(), "tblTimestampTest_DST")
}
## Output:
# tstamp
# 1 2011-03-27 01:00:00
# 2 2011-03-27 01:30:00
# 3 <NA>
# 4 <NA>
# 5 2011-03-27 03:00:00
# 6 2011-03-27 03:30:00
## As the output shows the timestamps between 02:00:00 and
## 02:59:59 have been set to <NA>. Reason: When retrieving
## date/time data from MS Access, R converts the timestamps
## from a text representation into POSIXct objects. As POSIXct's
## standard time zone seems to be taken from the Windows system
## R tries to convert to Central European Time (CET) which
## does not exist for the hour in which time is switched to
## daylight-saving time (as in the example).
## This standard behaviour can be changed by setting the
## standard time zone:
tz <- Sys.getenv("tz") # save current standard time zone
Sys.setenv(tz = "UTC") # set standard time zone to UTC
## The same command as above now delivers all timestamps
## (in Coordinated Universal Time, UTC):
if (.Platform$OS.type == "windows") {
hsGetTable(xmdb(), "tblTimestampTest_DST")
}
## Output:
# tstamp
# 1 2011-03-27 01:00:00
# 2 2011-03-27 01:30:00
# 3 2011-03-27 02:00:00
# 4 2011-03-27 02:30:00
# 5 2011-03-27 03:00:00
# 6 2011-03-27 03:30:00
## Reset standard time zone
Sys.setenv(tz = tz)
}