read_excel {readxl} | R Documentation |
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 |
Path to the xls/xlsx file |
sheet |
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 |
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 |
col_names |
|
col_types |
Either |
na |
Character vector of strings to use for missing values. By default, readxl treats blank cells as missing data. |
trim_ws |
Should leading and trailing whitespace be trimmed? |
skip |
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 |
n_max |
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 |
guess_max |
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
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"))