topic Re: scraping multiple excel files from a website in SAS Programming

scraping multiple excel files from a website

Anita_n — Mon, 18 Jul 2022 08:34:23 GMT

Dear all,

I need some help, I wish to extract some excel files from this website:

https://www.destatis.de/SiteGlobals/Forms/Suche/Expertensuche_Formular.html?resourceId=2402&input_=2408&pageLocale=de&templateQueryString=Alle+politisch+selbst%C3%A4ndigen+Gemeinden+mit+ausgew%C3%A4hlten+Merkmalen+&submit.x=0&submit.y=0

@Kurt_Bremser helped me some time ago with this solution:

filename ht temp;

proc http
  url="https://www.destatis.de/DE/Themen/Laender-Regionen/Regionales/Gemeindeverzeichnis/Administrativ/Archiv/GVAuszugJ/31122020_Auszug_GV.xlsx;jsessionid=B4477D957BFAB0E007E62A86373ED7F9.live711?__blob=publicationFile"
  out=ht
;
run;

proc import
  datafile=ht
  out=test
  dbms=xlsx
  replace
;
sheet="Onlineprodukt_Gemeinden";
run;

The problem I have now is I wish to extract all excel files which has a date 31.12.1999, 31.12.2000, 31.12.2001 ....... and so on till 31.12.2021

What can I add to the above code to accomplish this task?

Can anyone help please?

Please note:

What I realised in the url is that the name of the excel sheet also contains the date (see below in link, I have highlighted this in red)

"https://www.destatis.de/DE/Themen/Laender-Regionen/Regionales/Gemeindeverzeichnis/Administrativ/Archiv/GVAuszugJ/31122020_Auszug_GV.xlsx;jsessionid=B4477D957BFAB0E007E62A86373ED7F9.live711?__blob=publicationFile"

Please also note that the excel files might be found on different pages

Re: scraping multiple excel files from a website

JosvanderVelden — Mon, 18 Jul 2022 09:32:42 GMT

Based on the search link (first link in the post) you send it seems to me there are only 12 files to download. The time it will take to create the code and test will surely be a lot more then just clicking 12 times to download the files.
The coding challenge is interesting and solving it will be rewarding. But taking into account the effort I suggest you just click the 12 links, unless you need similar code for similar tasks in the future.

Best regards, Jos

Re: scraping multiple excel files from a website

Anita_n — Mon, 18 Jul 2022 10:24:04 GMT

It is not only about 12 files but about several from 1999 to 2021 and further years. I will still appreciate any help

Re: scraping multiple excel files from a website

Reeza — Mon, 18 Jul 2022 17:20:27 GMT

It's doable but not as simple as your original question by any fashion. Scraping is a bit of guess and test and digging into the data.

https://blogs.sas.com/content/sasdummy/2017/12/04/scrape-web-page-data/

Re: scraping multiple excel files from a website

Anita_n — Tue, 19 Jul 2022 05:44:04 GMT

Yes, you are right, I believe it's not so easy, but maybe someone can help anyway