The title of the post is a bit long, but that’s the problem I was facing this morning: importing dataset from files, online. I mean, it was not a “problem” (since I can always download, and extract manually the files), more a challenge (I should be able to do it in R, directly). The files are located on ressources-actuarielles.net, in a zip file. Those are mortality tables used in French speaking African countries, and I guess that one problem came from special characters, such as “é” or “è”… When you open the zip file, you see a folder
and in that folder, several files that I would like to import
My first code was quite standard, unfortunately, it did not work
> library(xlsx) > loc.url <- "http://ressources-a...CIMA.zip" > temp <- tempfile() > download.file(loc.url,temp) trying URL 'http://ressources-a...CIMA.zip' Content type 'application/x-zip' length 503786 bytes (491 Kb) opened URL downloaded 491 Kb > list.files <- unzip(temp,list=TRUE) > data <- read.xlsx(unz(temp, + list.files$Name[1]),sheetIndex=1) Error in path.expand(file) : invalid 'path' argument
Indeed, there was a problem with the path,
> list.files$Name[1] [1] "Tables de mortalit‚ CIMA/CIMA F.xlsx"
Hopefully, as usual, @3wen came to rescue me, and suggested the following,
> Sys.setlocale("LC_ALL", "C") [1] "C" > loc.url <- "http://ressources-a...CIMA.zip" > td <- tempdir() > tf <- tempfile(tmpdir=td, fileext=".zip") > download.file(loc.url, tf) trying URL 'http://ressources-a...CIMA.zip' Content type 'application/x-zip' length 503786 bytes (491 Kb) opened URL ============================================= downloaded 491 Kb > fname <- unzip(tf, list=TRUE)$Name[1] > unzip(tf, files=fname, exdir=td, + overwrite=TRUE) > fpath <- file.path(td, fname)
That was it… then @3wen suggested another package to read the xlsx file (that works well on my linux laptop, not on Windows)
> data <- read.xls(fpath, sheet = 1) perl: warning: Setting locale failed. perl: warning: Please check that your locale settings: LANGUAGE = "fr_CA:fr", LC_ALL = (unset), LANG = "fr_CA.UTF-8" are supported and installed on your system. perl: warning: Falling back to the standard locale ("C"). > Sys.setlocale() [1] "" > head(data) Age Lx..CIMA.F. qx..CIMA.F. dx..CIMA.F. 1 0 1000000.0 0.2849% 2848.5034 2 1 997151.5 0.0387% 385.8644 3 2 996765.6 0.0296% 295.2717 4 3 996470.4 0.0252% 251.5163 5 4 996218.8 0.0216% 214.9381 6 5 996003.9 0.0197% 195.9929
That was more subtle than expected…
OpenEdition suggests that you cite this post as follows:
Arthur Charpentier (October 30, 2014). Extracting datasets from excel files in a zipped folder. Freakonometrics. Retrieved December 3, 2024 from https://doi.org/10.58079/ouxi
One thought on “Extracting datasets from excel files in a zipped folder”