Downloading csv file from website with buttons?

Hello,

I am trying to download the following table in a csv format: http://stats.oecd.org/Index.aspx?DataSetCode=MEI_CLI

However this website does not provide a direct link. Rather you have to press a few buttons before you are allowed to download the file.

Is there a way to download this file automatically from within R?

Thanks a lot for any suggestions in advance!

Posted Apr 9, 2017, 10:07 PM UTC

415
too_many_splines•

u/SataMaxx has the right idea, but I don't think it works in this case. The final GET call:

http://stats.oecd.org/Download.ashx?type=csv&Delimiter=%2c&IncludeTimeSeriesIdentifiers=False&LabelType=CodeAndLabel&LanguageCode=en

doesn't work because its downstream to the POST call:

http://stats.oecd.org/modalexports.aspx?exporttype=bulk&FirstDataPointIndexPerPage=undefined&SubSessionId=801bd869-896a-499d-a3e5-4c9b12d14783&Random=0.12979474325124074

Notice the subSessionId and Random parameters. You're not going to be able to guess those, and you don't even want to look at what's in the body of this POST call.

Trying to hunt down the right xpath/selector from the original url:

http://stats.oecd.org/Index.aspx?DataSetCode=MEI_CLI

won't work either because your data is not static html. It's dynamically triggered by javascript, so you need to actually be browsing for the data to render. For what its worth, the right minimal selector would be: "#fixedheader > table"

There IS an option here: headless browsing. This will allow you to traverse the website and click on the buttons programatically. From experience this is a gigantic pain in the ass, but it can be done with PhantomJS or even natively in R with RSelenium (an R package that provides a Selenium API).

4
PlayMeWhileOP•

Thanks a lot for all the info. Looks like I am in trouble then but I suspected that much.

Sounds like you are knowledgable in this area. I was thinking of an alternative way to do it by extracting the table directly from the html (it is displayed before downloading the file). Would that be doable in your opinion?

1
too_many_splines•

The problem is that table isn't actually in the html to begin with. The html is created after you start browsing it via javascript. Notice how the data loads after all the other elements on the page - thats because its not really part of the original html. If you disable javascript on your browser you can see that the data never appears on your browser.

I supplied the right selector above if you want to give it a shot, but because the js isn't triggered when you do standard webscrapping, the html won't be there either.

I have a potential solution I'm looking into that won't require headless browsing but it looks super ugly at the moment. I'll update when I have more.

Edit: Well, that ugly solution is XML, and there's no way in hell I am putting myself through the trauma of parsing XML, so headless browsing it is!

1
PlayMeWhileOP•

Thank you again for sharing your expertise and providing some insight into this issue. As mentioned in another comment - it's late here so I am going to sleep. But will try your proposed ideas in the morning.

1
SataMaxx•

Try going through the download step by steps with your web browser inspector. There is a tab where you can monitor network activity and peek at the underlying requests made by the browser to go through each step. You can see each parameter and even what kind of curl call you could make to automate the process using a shell script.
Study a bit the details of the requests and you will then be able to write appropriate R code to replicate it using the curl package, or httr.

Something like this should work, provided the url you gave in another comment works for you (doesn't for me when I click it in my browser) :

library(httr)
library(magrittr)

GET("http://stats.oecd.org/Download.ashx?type=csv&Delimiter=%2c&IncludeTimeSeriesIdentifiers=False&LabelType=CodeAndLabel&LanguageCode=en") %>%
content -> my_data

Though you might have to go through some previous GET calls to "set" the server for your request. It also looks like you might have to gather some tokens or sessionids along, using rvest and expressions such as

GET("url_with_params") %>%
content %>%
html_node("css selector") %>%
html_attr("value") -> token

to extract relevant parts from the served html page and re-inject them in the url parameters.

2
PlayMeWhileOP•

Thank you, I will look at this more closely tomorrow (it's late here now).

The URL I provided earlier worked before, but does not work now. Maybe it had some kind of recorded session. I am not knowledgable in how networks work (as you can surely tell).

2
SataMaxx•

I've added a bit more information regarding sessions in my previous post. Also, note that httr takes care of cookies, so that part is handled and you don't have to worry about it!

2
PlayMeWhileOP•

Thanks a lot of the help! Will give it a closer look tomorrow morning.

2
too_many_splines•

Headless browsing solution with phantomJS

url <- 'http://stats.oecd.org/Index.aspx?DataSetCode=MEI_CLI'

#PhantomJS code that will browse the url to trigger the javascript, then save the html
writeLines(paste0(
    "var page   = require('webpage').create();

    page.open(\'", url, "\',function () {
        require('fs').write('test.html', page.content,'w');
        phantom.exit();
    });"), con = "test.js")

# Run the phantomJS code defined above
system("phantomjs test.js")
library(dplyr)
library(rvest)

# Simple web-scrapping to extract the right information.
dat <- read_html("test.html") %>% html_node('table.DataTable') %>% html_table(fill = TRUE) %>% 
    setNames(.[3,])
dat <- dat[-seq(7), -2]
names(dat)[1] <- "Time"
2
PlayMeWhileOP•

Thank you for all the effort! I see this requires phantomjs - I am unfamiliar with it, but in this situation it's probably the easiest way.

1
mongooseondaloose•

I'm having a similar issue, where the desired data is not accessible via a static url (as u/Dokugumo assumes) but rather is a download prompt as a result of an in-browser/GUI request (e.g., a button click).

The minor progress I've had is by working with the rvest package, but even still I'm unable to "click to download" and intercept the stream returned by an asp/php request response.

Upvoting for visibility; hopefully a more experienced R user can help resolve this issue.

1
Dokugumo•

read.csv can handle urls:

X <- read.csv(url("http://some.where.net/data/foo.csv"))

0
PlayMeWhileOP•

Does not work for me:

Here is the url: http://stats.oecd.org/Download.ashx?type=csv&Delimiter=%2c&IncludeTimeSeriesIdentifiers=False&LabelType=CodeAndLabel&LanguageCode=en

And I get an error

In open.connection(file, "rt") : cannot open URL 'http://stats.oecd.org/Download.ashx?type=csv&Delimiter=%2c&IncludeTimeSeriesIdentifiers=False&LabelType=CodeAndLabel&LanguageCode=en': HTTP status was '500 Internal Server Error'

2
Dokugumo•

The URL you pass needs to be the direct link to the csv file. Easiest way to tell is that the URL will end in .csv

1
PlayMeWhileOP•

Yes I know... But that's my problem - the website does not provide such an URL.

2