Read xls and xlsx files.
While read_excel()
auto detects the format from the file
extension, read_xls()
and read_xlsx()
can be used to
read files without extension.
read_excel(path, sheet = NULL, range = NULL, col_names = TRUE,
col_types = NULL, na = "", trim_ws = TRUE, skip = 0, n_max = Inf,
guess_max = min(1000, n_max))read_xls(path, sheet = NULL, range = NULL, col_names = TRUE,
col_types = NULL, na = "", trim_ws = TRUE, skip = 0, n_max = Inf,
guess_max = min(1000, n_max))
read_xlsx(path, sheet = NULL, range = NULL, col_names = TRUE,
col_types = NULL, na = "", trim_ws = TRUE, skip = 0, n_max = Inf,
guess_max = min(1000, n_max))
Path to the xls/xlsx file
Sheet to read. Either a string (the name of a sheet), or an
integer (the position of the sheet). Ignored if the sheet is specified via
range
. If neither argument specifies the sheet, defaults to the first
sheet.
A cell range to read from, as described in cell-specification.
Includes typical Excel ranges like "B3:D87", possibly including the sheet
name like "Budget!B2:G14", and more. Interpreted strictly, even if the
range forces the inclusion of leading or trailing empty rows or columns.
Takes precedence over skip
, n_max
and sheet
.
TRUE
to use the first row as column names, FALSE
to get
default names, or a character vector giving a name for each column. If user
provides col_types
as a vector, col_names
can have one entry per
column, i.e. have the same length as col_types
, or one entry per
unskipped column.
Either NULL
to guess all from the spreadsheet or a
character vector containing one entry per column from these options:
"skip", "guess", "logical", "numeric", "date", "text" or "list". If exactly
one col_type
is specified, it will be recycled. The content of a cell in
a skipped column is never read and that column will not appear in the data
frame output. A list cell loads a column as a list of length 1 vectors,
which are typed using the type guessing logic from col_types = NULL
, but
on a cell-by-cell basis.
Character vector of strings to use for missing values. By default, readxl treats blank cells as missing data.
Should leading and trailing whitespace be trimmed?
Minimum number of rows to skip before reading anything, be it
column names or data. Leading empty rows are automatically skipped, so this
is a lower bound. Ignored if range
is given.
Maximum number of data rows to read. Trailing empty rows are
automatically skipped, so this is an upper bound on the number of rows in
the returned tibble. Ignored if range
is given.
Maximum number of data rows to use for guessing column types.
A tibble
cell-specification for more details on targetting cells with the
range
argument
# NOT RUN { datasets <- readxl_example("datasets.xlsx") read_excel(datasets) # Specify sheet either by position or by name read_excel(datasets, 2) read_excel(datasets, "mtcars") # Skip rows and use default column names read_excel(datasets, skip = 148, col_names = FALSE) # Recycle a single column type read_excel(datasets, col_types = "text") # Specify some col_types and guess others read_excel(datasets, col_types = c("text", "guess", "numeric", "guess", "guess")) # Accomodate a column with disparate types via col_type = "list" df <- read_excel(readxl_example("clippy.xlsx"), col_types = c("text", "list")) df df$value sapply(df$value, class) # Limit the number of data rows read read_excel(datasets, n_max = 3) # Read from an Excel range using A1 or R1C1 notation read_excel(datasets, range = "C1:E7") read_excel(datasets, range = "R1C2:R2C5") # Specify the sheet as part of the range read_excel(datasets, range = "mtcars!B1:D5") # Read only specific rows or columns read_excel(datasets, range = cell_rows(102:151), col_names = FALSE) read_excel(datasets, range = cell_cols("B:D")) # }
Run the code above in your browser using DataCamp Workspace