readTable
Reading Excel tables from a workbook
Usage
# S4 method for workbook,numeric
readTable(object, sheet, table, header, rownames, colTypes, forceConversion,
dateTimeFormat, check.names, useCachedValues, keep, drop, simplify, readStrategy)
# S4 method for workbook,character
readTable(object, sheet, table, header, rownames, colTypes, forceConversion,
dateTimeFormat, check.names, useCachedValues, keep, drop, simplify, readStrategy)
Arguments
- object
- sheet
The index or name of the worksheet on which to look for the specified
table
- table
The name of the table to read
- header
The argument
header
specifies if the first row should be interpreted as column names. The default value isTRUE
.- rownames
Index (
numeric
) or name (character
) of column that should be used as row names. The corresponding column will be removed from the data set. Defaults toNULL
which means that no row names are applied.- colTypes
Column types to use when reading in the data. Specified as a
character
vector of the corresponding type names (seeXLC
;XLC$DATA_TYPE.<?>
). You may also use R class names such asnumeric
,character
,logical
andPOSIXt
. The types are applied in the given order to the columns - elements are recycled if necessary. Defaults tocharacter(0)
meaning that column types are determined automatically (see the Note section for more information). By default, type conversions are only applied if the specified column type is a more generic type (e.g. from Numeric to String) - otherwiseNA
is returned. TheforceConversion
flag can be set to force conversion into less generic types where possible.- forceConversion
logical
specifying if conversions to less generic types should be forced. Defaults toFALSE
meaning that if a column is specified to be of a certain type via thecolTypes
argument and a more generic type is detected in the column, thenNA
will be returned (example: column is specified to be DateTime but a more generic String is found). SpecifyingforceConversion = TRUE
will try to enforce a conversion - if it succeeds the corresponding (converted) value will be returned, otherwiseNA
. See the Note section for some additional information.- dateTimeFormat
Date/time format used when doing date/time conversions. Defaults to
getOption("XLConnect.dateTimeFormat")
. This should be a POSIX format specifier according tostrptime
although not all specifications have been implemented yet - the most important ones however are available.- check.names
logical
specifying if column names of the resultingdata.frame
should be checked to ensure that they are syntactically valid valid variable names and are not duplicated. See thecheck.names
argument ofdata.frame
. Defaults toTRUE
.- useCachedValues
logical
specifying whether to read cached formula results from the workbook instead of re-evaluating them. This is particularly helpful in cases for reading data produced by Excel features not supported in XLConnect like references to external workbooks. Defaults toFALSE
, which means that formulas will be evaluated by XLConnect.- keep
List of column names or indices to be kept in the output data frame. It is possible to specify either
keep
ordrop
, but not both at the same time. Defaults toNULL
. If a vector is passed as argument, it will be wrapped into a list. This list gets replicated to match the length of the other arguments.- drop
List of column names or indices to be dropped in the output data frame. It is possible to specify either
keep
ordrop
, but not both at the same time. Defaults toNULL
. If a vector is passed as argument, it will be wrapped into a list. This list gets replicated to match the length of the other arguments.- simplify
logical
specifying if the result should be simplified, e.g. in case thedata.frame
would only have one row or one column (and data types match). Simplifying here is identical to callingunlist
on the otherwise resultingdata.frame
(usinguse.names = FALSE
). The default isFALSE
.- readStrategy
character
specifying the reading strategy to use. Currently supported strategies are:"default"
(default): Can handle all supported data types incl. date/time values and can deal directly with missing value identifiers (seesetMissingValue
)"fast"
: Increased read performance. Date/time values are read as numeric (number of days since 1900-01-01; fractional days represent hours, minutes, and seconds) and only blank cells are recognized as missing (missing value identifiers as set insetMissingValue
are ignored)
Note
If no specific column types (see argument colTypes
) are specified,
readNamedRegion
tries to determine the resulting column types
based on the read cell types. If different cell types are found in a
specific column, the most general of those is used and mapped to the
corresponding R data type. The order of data types from least to most
general is Boolean (logical
) < DateTime (POSIXct
) <
Numeric (numeric
) < String (character
). E.g. if a column
is read that contains cells of type Boolean, Numeric and String then the
resulting column in R would be character
since character
is the most general type.
Some additional information with respect to forcing data type conversion
using forceConversion = TRUE
:
Forcing conversion from String to Boolean:
TRUE
is returned if and only if the target string is "true" (ignoring any capitalization). Any other string will returnFALSE
.Forcing conversion from Numeric to DateTime: since Excel understands Dates/Times as Numerics with some additional formatting, a conversion from a Numeric to a DateTime is actually possible. Numerics in this case represent the number of days since 1900-01-01. Fractional days represent hours, minutes, and seconds.
References
Overview of Excel tables http://office.microsoft.com/en-001/excel-help/overview-of-excel-tables-HA010048546.aspx
See Also
'>workbook
,
readNamedRegion
,
readWorksheet
,
writeNamedRegion
,
writeWorksheet
,
readNamedRegionFromFile
,
onErrorCell
Examples
# NOT RUN {
# mtcars xlsx file from demoFiles subfolder of package XLConnect
demoExcelFile <- system.file("demoFiles/mtcars.xlsx", package = "XLConnect")
# Load workbook
wb <- loadWorkbook(demoExcelFile)
# Read table 'MtcarsTable' from sheet 'mtcars_table'
data <- readTable(wb, sheet = "mtcars_table", table = "MtcarsTable")
# }