To work with Excel files in R, you can use the readxl library
install.packages(“readxl”)
Use the read_excel() function to read and Excel workbook
> library(readxl)
>
> ## read excel file
> df1 <- read_excel("r_excel.xlsx")
> head(df1)
# A tibble: 6 x 4
Name Job Hours Complete
<chr> <chr> <dbl> <chr>
1 Sally Predictive Model 1. n
2 Philip Maintanence 10. n
3 Tom Ad-hoc Report 12. y
4 Bob SSRS 3. y
5 Philip Tableau 7. n
6 Tom Tableau 9. n
By default, read_excel() reads only the first sheet in the Excel file. To read other sheets using the sheet key word.
> ## read sheet 2
> df2 <- read_excel("r_excel.xlsx", sheet =2)
> head(df2)
# A tibble: 6 x 2
Animal Num_Legs
<chr> <dbl>
1 Dog 4.
2 Duck 2.
3 Snake 0.
4 Horse 4.
5 Spider 8.
6 Human 2.
The next example, we are reading range B2 – C6 on sheet 1 (same as Excel’s range function)
> ## read sheet 1, range B2 - C6
> df3 <- read_excel("r_excel.xlsx", sheet =1, range = "B2:C6")
> df3
# A tibble: 4 x 2
`Predictive Model` `1`
<chr> <dbl>
1 Maintanence 10.
2 Ad-hoc Report 12.
3 SSRS 3.
4 Tableau 7.
In this last example, we are importing only the first 4 rows on sheet 2
> ## read sheet 2, first 4 rows only
> df4 <- read_excel("r_excel.xlsx", sheet = 2, n_max = 4)
> df4
# A tibble: 4 x 2
Animal Num_Legs
<chr> <dbl>
1 Dog 4.
2 Duck 2.
3 Snake 0.
4 Horse 4.
