Download several Excel files from url using R

Question

Problem

I want to download several Excel files from a website using R. I don't want to name each link separately, because there are many. I had a look at previous posts, but their links to the website did not work so I couldn't see how the answers related to the structure of the website.

The website I'm testing this on is: https://digital.nhs.uk/data-and-information/publications/statistical/adult-psychiatric-morbidity-survey/adult-psychiatric-morbidity-survey-survey-of-mental-health-and-wellbeing-england-2014

Attempt

My attempt to get the names of the full links for the attachments:

url <- "https://digital.nhs.uk/data-and-information/publications/statistical/adult-psychiatric-morbidity-survey/adult-psychiatric-morbidity-survey-survey-of-mental-health-and-wellbeing-england-2014"

simple <- read_html(url)

files <- simple %>%
  html_nodes(".attachment") %>% 
  html_text()

Yet, whilst I can get the names of the files, I can't get the actual names of their links. In particular, I don't know how to get to the "a href" attribute. Once I get the names, then I'll work out how to download them using lapply and download.file.

Thanks everyone!

I've not used it, but I recommend package politely - it makes it easy to make sure that you're webscraping as respectfully as possible. Wouldn't want to burden the NHS! — Captain Hat

G5W G5W · Accepted Answer · 2021-01-25T17:07:13

The XML package has some good tools to process web pages, in particular, for extracting the links.

library(XML)

url <- "https://digital.nhs.uk/data-and-information/publications/statistical/adult-psychiatric-morbidity-survey/adult-psychiatric-morbidity-survey-survey-of-mental-health-and-wellbeing-england-2014"
pageContent <- readLines(url)
Links <- getHTMLLinks(pageContent)
xlsFiles <- grep("\\.xls", Links)
Links[xlsFiles]

 [1] "https://files.digital.nhs.uk/excel/9/s/apms-2014-ch-02-tabs.xls"
 [2] "https://files.digital.nhs.uk/excel/9/b/apms-2014-ch-03-tabs.xls"
 [3] "https://files.digital.nhs.uk/excel/a/i/apms-2014-ch-04-tabs.xls"
 [4] "https://files.digital.nhs.uk/excel/a/t/apms-2014-ch-05-tabs.xls"
 [5] "https://files.digital.nhs.uk/excel/b/m/apms-2014-ch-06-tabs.xls"
 [6] "https://files.digital.nhs.uk/excel/b/p/apms-2014-ch-07-tabs.xls"
 [7] "https://files.digital.nhs.uk/excel/b/l/apms-2014-ch-08-tabs.xls"
 [8] "https://files.digital.nhs.uk/excel/c/1/apms-2014-ch-09-tabs.xls"
 [9] "https://files.digital.nhs.uk/excel/s/0/apms-2014-ch-10-tabs.xls"
[10] "https://files.digital.nhs.uk/excel/c/p/apms-2014-ch-11-tabs.xls"
[11] "https://files.digital.nhs.uk/6F/FB2F1B/apms-2014-ch-12-tabs.xls"
[12] "https://files.digital.nhs.uk/excel/d/g/apms-2014-ch-13-tabs.xls"
[13] "https://files.digital.nhs.uk/excel/d/r/apms-2014-ch-14-tabs.xls"

Download several Excel files from url using R

1 Answers