I am trying to open the .xls file from this link 'https://www.ons.gov.uk/file?uri=/economy/grossvalueaddedgva/datasets/regionalgvaibylocalauthorityintheuk/1997to2015/regionalgvaibylainuk.xls' with the package 'xlsx', but it does not seem to be working. I have tried other packages like 'gdata' unsuccessfully, too.
install.packages('xlsx')
require('xlsx')
file <- system.file('https://www.ons.gov.uk/file?uri=/economy/grossvalueaddedgva/datasets/regionalgvaibylocalauthorityintheuk/1997to2015/regionalgvaibylainuk.xls', package = "xlsx")
res <- read.xlsx(file, 5) # read the fifth sheet
I get this error: 'Error in loadWorkbook(file, password = password) : Cannot find'
I have also tried the function read.xls(), but it throws me an error, too.
I think the answer from Read Excel file from a URL using the readxl package can be used here:
library(httr)
library(xlsx)
url1<-'https://www.ons.gov.uk/file?uri=/economy/grossvalueaddedgva/datasets/regionalgvaibylocalauthorityintheuk/1997to2015/regionalgvaibylainuk.xls'
GET(url1, write_disk(tf <- tempfile(fileext = ".xls")))
res <- read.xlsx(tf, 5)