The go to tool comes from the readxl library in R. We can install it with:
install.packages("readxl")
To use it, the Markdown must call it – make it active – just as we must at the command line to make it work. The Files pane will make this easier, we can right click to import and get code from the subsequent interaction.
library(readxl)
The command to read Excel files comes in three forms:
- read_excel()
- read_xls()
- read_xlsx()
where the first works for all file types while the second is written specifically for older .xls files and the third is written for newer .xlsx file types. If we type ?read_excel we can obtain the help file that guides us through a host of situations including specifying a range of cells, whether or not to use the first row as column names, the data type in a column, what is missing [NA], whether rows should be skipped, and a host of others. The one thing that is required is the file that we wish to import [known to this command as the path]. We can acquire the file from the following link. If my file is known to my computer as /home/rob/Downloads/BondFunds.xlsx then I also want to be careful about the sheet, in this case, it is JustData:
# A tibble: 18 × 9
...1 ...2 ...3 ...4 ...5 ...6 ...7 ...8 mosai…¹
<chr> <chr> <chr> <chr> <chr> <lgl> <lgl> <lgl> <lgl>
1 <NA> <NA> <NA> <NA> <NA> NA NA NA NA
2 Fees and Bond Type Fees <NA> <NA> <NA> NA NA NA NA
3 Type No Yes Gran… <NA> NA NA NA NA
4 Intermediate Government 0.288043… 0.18… 0.47… <NA> NA NA NA NA
5 Short Term Corporate 0.418478… 0.10… 0.52… <NA> NA NA NA NA
6 Grand Total 0.706521… 0.29… 1 <NA> NA NA NA NA
7 <NA> <NA> <NA> <NA> <NA> NA NA NA NA
8 <NA> <NA> <NA> <NA> <NA> NA NA NA NA
9 <NA> <NA> <NA> <NA> <NA> NA NA NA NA
10 <NA> <NA> <NA> <NA> <NA> NA NA NA NA
11 <NA> <NA> <NA> <NA> <NA> NA NA NA NA
12 <NA> <NA> <NA> <NA> <NA> NA NA NA NA
13 <NA> <NA> <NA> <NA> <NA> NA NA NA NA
14 Risk and Bond Type Risk <NA> <NA> <NA> NA NA NA NA
15 Type Above av… Aver… Belo… Gran… NA NA NA NA
16 Intermediate Government 29 32 26 87 NA NA NA NA
17 Short Term Corporate 30 37 30 97 NA NA NA NA
18 Grand Total 59 69 56 184 NA NA NA NA
# … with abbreviated variable name ¹`mosaic(xtabs(~Type+Fees, data=Bonds))`
That is not well-formatted because the first sheet is a table of sorts. A little bit of Rvangelism.
A look at the magic of R.
I am going to use R’s ability to define variable(s) to make my life easier. I do not wish to know what the tabs are; let R figure it out.
AllSheets <-function(path) { # Feed a path to an Excel file WorkBook <-excel_sheets(path=path) %>%# Get a vector of sheet namesmap(~read_excel(path=path, sheet=.x)) # Load each sheet by namenames(WorkBook) <-excel_sheets(path=path) # Rename the sheets by their namesreturn(WorkBook)}MyWB <-AllSheets(path="~/Downloads/BondFunds.xlsx")