Macro to loop through years to build URL string to download data

Hi All,

I would like to loop through this URL string replacing the year parameter to return a unioned set of multiple years

https://climate.weather.gc.ca/climate_data/bulk_data_e.html?format=csv&stationID=51459&Year=2020&Month=1&Day=14&timeframe=2&submit= Download+Data

The above URL returns a CSV file for 2020, the format paramteter can also be set to XML.

I am able to use this URL to return 2020 data. I tried using a batch macro to build out the URL string in a formula task, but not sure how to use the output macro iterator in the input data task.

Thanks

Danny

Macros

Accepted answers

apathetichell

wasn't sure how many years you wanted so I built you an app...

A few notes - this version edits the URL line with the difference between the base year (2020) and the generated row.... It then uses download tool to download all year. Downloads are in .csv so that's the next part (ie parsing it) which I can help with if you need help with...

weather downloader.yxwz

All comments

apathetichell

wasn't sure how many years you wanted so I built you an app...

weather downloader.yxwz

DannyLau

Thanks for the quick reply, what task should I use next to parse the data? I just started using Alteryx today for the first time, so far so good, but there is definitely a learning curve.

apathetichell

yeah - honestly parsing the data has been a bit rough so far... .xml might be more straight forward... I'm having some issues with getting fields to line up even in the same dataset... The usually solution is to figure how to format one group of data and then convert it into a macro (ie a batch macro) but a) that's a bit complicated and b) I can't quite get there using even one source. I'm still looking at it... obviously if you know how many columns you have you can try a fairly generic text to columns split so maybe play around with that?

apathetichell

use this as a start.... I'll try to do more cleaning later.

weather downloader.yxzp

DannyLau

Thanks for the help, I ended up downloading all the CSVs to a local folder using the download task and then in another workflow load all the CSVs in the folder instead of tackling a parsing excercise

apathetichell

OK - that's probably more straightforward.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703