7.4 Excel Data Read

20200104 Microsoft Excel spreadsheets are supported by readxl (Wickham and Bryan 2019) which provides readxl::read_excel(). A common requirement is to skip= the first few lines of the spreadsheet which might be taken up with logos and file meta data. A specific sheet can be chosen with sheet=``2 to select the second sheet or sheet=``"expenses" to select a named sheet. A specific range within a sheet is selected using range=. The package also provides readxl::excel_format() and readxl::excel_sheets().

Below we read data from the Sydney tab of the weatherAUS.xlsx spreadsheet created in Section @ref(ingestion:write_excel).

library(magrittr)     # Data pipelines: %>% %<>% %T>% equals().
library(glue)         # Format strings: glue().
library(readxl)       # Read Excel spreadsheets: read_excel().

dsname <- "weatherAUS"
dstype <- "xlsx"
fsep   <- .Platform$file.sep

getwd() %>% 
  glue("{fsep}{dsname}.{dstype}") %T>%
  print() ->
dspath
## /home/gjw/git/bitbucket/kayontoga/onepager/weatherAUS.xlsx
dspath %>% excel_format()
## [1] "xlsx"
dspath %>% excel_sheets()
## [1] "Sydney"
dspath %>%
  read_excel(sheet="Sydney") %>%
  assign(dsname, ., globalenv())

References

Wickham, Hadley, and Jennifer Bryan. 2019. Readxl: Read Excel Files. https://CRAN.R-project.org/package=readxl.


Your donation will support ongoing development and give you access to the PDF version of this book. Desktop Survival Guides include Data Science, GNU/Linux, and MLHub. Books available on Amazon include Data Mining with Rattle and Essentials of Data Science. Popular open source software includes rattle, wajig, and mlhub. Hosted by Togaware, a pioneer of free and open source software since 1984.
Copyright © 1995-2021 Graham.Williams@togaware.com Creative Commons Attribution-ShareAlike 4.0.