Search code examples
rfilegdatainstall.packagesr-xlsx

read.xlsx Error in loadWorkbook(file, password = password) : Cannot find

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.

Solution

  • 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)