How to Read an Excel file into R

Installing R package

Because flipAPI does not require external libraries that use Java or Perl, installation is very straightforward. Simply open R and type the following into the console:

install.package(devtools)
devtools::install_github("Displayr/flipAPI")

Once the flipAPI package is installed, a file can be read in by using the command

library(flipAPI)
DownloadXLSX(filename)

Data output format

In many cases, the Excel format contains multiple tables with comments and other text. It is not necessary to reformat the file before importing. We can specify particular sheets or ranges to import.

cola1 = DownloadXLSX("https://wiki.q-researchsoftware.com/images/b/b9/Cola_Discriminant_Functions.xlsx", want.col.names = TRUE, range = "A2:G9")
cola2 = DownloadXLSX("https://wiki.q-researchsoftware.com/images/b/b9/Cola_Discriminant_Functions.xlsx", want.col.names = TRUE, want.row.names = FALSE, sheet = 2, range = "AB2:AC330"

To check the result of these command, we type

str(cola1)

and see the output:

 num [1:7, 1:6] -3.4 2.653 -0.566 -0.458 -0.428 ...
 - attr(*, "dimnames")=List of 2
  ..$ : chr [1:7] "Intercept" "Coca-Cola" "Diet Coke" "Coke Zero" ...
  ..$ : chr [1:6] "Coca-Cola" "Diet Coke" "Coke Zero" "Pepsi" ...

Similarly, we type str(cola2) and get output:

'data.frame':	328 obs. of  2 variables:
 $ Highest Score           : num  1.1202 1.8786 1.8311 3.6638 0.0754 ...
 $ Predicted Preferred Cola: Factor w/ 6 levels "Coca-Cola","Coke Zero",..: 6 6 3 1 3 6 2 1 3 2 ...

We can see that DownloadXLSX automatically parses and converts the data into the correct format. cola1, which contains only numeric data, is converted into a matrix, whereas cola2, which has both numeric and categorical data, is converted into a data frame.

Importing Excel files from cloud storage

Another useful feature of DownloadXLSX, which is not supported by the readxl package, is that it can read Excel files directly from the URL. If your file is in cloud storage — such as Dropbox, One Drive or Google Drive — you can simply use the link to that file instead of downloading a copy. Note that most links provided by cloud storage services do not take you directly to the file but instead to a page that shows a preview in your browser. DownloadXLSX will try to get the direct link automatically; however, this is supported only for Dropbox, One Drive and Google Drive.