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

[DannyLau]

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

[apathetichell · Accepted solution]

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...

[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.

[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.