Read Excel file from a URL using the readxl package

Question

Consider a file on the internet (like this one (note the s in https) https://evs.nci.nih.gov/ftp1/CDISC/SDTM/SDTM%20Terminology.xls

How can the sheet 2 of the file be read into R?

The following code is approximation of what is desired (but fails)

url1<-'https://evs.nci.nih.gov/ftp1/CDISC/SDTM/SDTM%20Terminology.xls' p1f <- tempfile() download.file(url1, p1f, mode="wb") p1<-read_excel(path = p1f, sheet = 2) 

Answer 1

This works for me on Windows:

library(readxl) library(httr) packageVersion("readxl") # [1] ‘0.1.1’  GET(url1, write_disk(tf <- tempfile(fileext = ".xls"))) df <- read_excel(tf, 2L) str(df) # Classes ‘tbl_df’, ‘tbl’ and 'data.frame': 20131 obs. of  8 variables: # $ Code                        : chr  "C115388" "C115800" "C115801" "C115802" ... # $ Codelist Code               : chr  NA "C115388" "C115388" "C115388" ... # $ Codelist Extensible (Yes/No): chr  "No" NA NA NA ... # $ Codelist Name               : chr  "6 Minute Walk Functional Test Test Code" "6 Minute Walk Functional Test Test Code" "6 Minute Walk Functional Test Test Code" "6 Minute Walk Functional Test Test Code" ... # $ CDISC Submission Value      : chr  "SIXMW1TC" "SIXMW101" "SIXMW102" "SIXMW103" ... # $ CDISC Synonym(s)            : chr  "6 Minute Walk Functional Test Test Code" "SIXMW1-Distance at 1 Minute" "SIXMW1-Distance at 2 Minutes" "SIXMW1-Distance at 3 Minutes" ... # $ CDISC Definition            : chr  "6 Minute Walk Test test code." "6 Minute Walk Test - Distance at 1 minute." "6 Minute Walk Test - Distance at 2 minutes." "6 Minute Walk Test - Distance at 3 minutes." ... # $ NCI Preferred Term          : chr  "CDISC Functional Test 6MWT Test Code Terminology" "6MWT - Distance at 1 Minute" "6MWT - Distance at 2 Minutes" "6MWT - Distance at 3 Minutes" ... 

Answer 2

From this issue on Github (#278):

some functionality for supporting more general inputs will be pulled out of readr, at which point readxl can exploit that.

So we should be able to pass urls directly to read_excel() in the (hopefully near) future.