read_excel | R Documentation |
Read xls and xlsx files
read_excel()
calls excel_format()
to determine if path
is xls or xlsx,
based on the file extension and the file itself, in that order. Use
read_xls()
and read_xlsx()
directly if you know better and want to
prevent such guessing.
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),
progress = readxl_progress(),
.name_repair = "unique"
)
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),
progress = readxl_progress(),
.name_repair = "unique"
)
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),
progress = readxl_progress(),
.name_repair = "unique"
)
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 interpret as 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. |
progress |
Display a progress spinner? By default, the spinner appears
only in an interactive session, outside the context of knitting a document,
and when the call is likely to run for several seconds or more. See
|
.name_repair |
Handling of column names. Passed along to
|
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"))
# Get a preview of column names
names(read_excel(readxl_example("datasets.xlsx"), n_max = 0))
# exploit full .name_repair flexibility from tibble
# "universal" names are unique and syntactic
read_excel(
readxl_example("deaths.xlsx"),
range = "arts!A5:F15",
.name_repair = "universal"
)
# specify name repair as a built-in function
read_excel(readxl_example("clippy.xlsx"), .name_repair = toupper)
# specify name repair as a custom function
my_custom_name_repair <- function(nms) tolower(gsub("[.]", "_", nms))
read_excel(
readxl_example("datasets.xlsx"),
.name_repair = my_custom_name_repair
)
# specify name repair as an anonymous function
read_excel(
readxl_example("datasets.xlsx"),
sheet = "chickwts",
.name_repair = ~ substr(.x, start = 1, stop = 3)
)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.