7.5 Excel Data Reading

20200104 Microsoft Excel spreadsheets are supported by readxl (Wickham and Bryan 2023) 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] "Adelaide"         "Albany"           "Albury"           "AliceSprings"    
##  [5] "BadgerysCreek"    "Ballarat"         "Bendigo"          "Brisbane"        
##  [9] "Cairns"           "Canberra"         "Cobar"            "CoffsHarbour"    
## [13] "Dartmoor"         "Darwin"           "GoldCoast"        "Hobart"          
## [17] "Katherine"        "Launceston"       "Melbourne"        "MelbourneAirport"
## [21] "Mildura"          "Moree"            "MountGambier"     "MountGinini"     
## [25] "Newcastle"        "Nhil"             "NorahHead"        "NorfolkIsland"   
## [29] "Nuriootpa"        "PearceRAAF"       "Penrith"          "Perth"           
## [33] "PerthAirport"     "Portland"         "Richmond"         "Sale"            
## [37] "SalmonGums"       "Sydney"           "SydneyAirport"    "Townsville"      
## [41] "Tuggeranong"      "Uluru"            "WaggaWagga"       "Walpole"         
## [45] "Watsonia"         "Williamtown"      "Witchcliffe"      "Wollongong"      
## [49] "Woomera"

dspath %>%
  read_excel(sheet="Sydney") %>%
  assign(dsname, ., globalenv())

References

Wickham, Hadley, and Jennifer Bryan. 2023. Readxl: Read Excel Files. https://readxl.tidyverse.org.

---

Your donation will support ongoing availability 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-2022 Graham.Williams@togaware.com Creative Commons Attribution-ShareAlike 4.0