Unable to load Excel 5.0 / 7.0 (BIFF5) format file in R
There are many ways to download Excel files in R as mentioned here: http://www.milanor.net/blog/?p=779
However, I've tried most of the options (RODBC, xlsx, gdata, XLConnect) and just can't get R to download this file published by the UK government in 2013:
Here's an example of failed attempts:
# save the file
download.file("http://www.ons.gov.uk/ons/rel/npp/national-population-projections/2012-based-projections/rft-table-a3-4-principal-projection---england-population-single-year-of-age.xls", destfile = "input-data/future-pop-ons.xls")
library(RODBC)
XLConnect::readWorksheetFromFile(file = "input-data/future-pop-ons.xls", sheet = 3)
## Error: OldExcelFormatException (Java): The supplied spreadsheet seems to be Excel 5.0/7.0 (BIFF5) format. POI only supports BIFF8 format (from Excel versions 97/2000/XP/2003)
library(XLConnect)
XLConnect::readWorksheet("input-data/future-pop-ons.xls", sheet = 3)
Error in (function (classes, fdef, mtable) :
unable to find an inherited method for function ‘readWorksheet’ for signature ‘"character", "numeric"’
library(gdata)
read.xls(xls = "input-data/future-pop-ons.xls", sheet = 3)
Use of uninitialized value $format in substitution (s///) at /home/robin/R/i686-pc-linux-gnu-library/3.1/gdata/perl/Spreadsheet/ParseExcel/Utility.pm line 183.
It would be great to be able to directly load this type of file, ensuring reproducibility.
+3
source to share
1 answer
This works for me on a rocker/rstudio
container I use for high isolation and reproducibility:
download.file("http://www.ons.gov.uk/ons/rel/npp/national-population-projections/2012-based-projections/rft-table-a3-4-principal-projection---england-population-single-year-of-age.xls",
destfile = "future-pop-ons.xls",
method = "wget")
library(gdata)
xx <- read.xls(xls = "future-pop-ons.xls", sheet = 3, fileEncoding="latin1")
There is a lot of warning output in the console, but the full sheet is readable and what's important.
+2
source to share