Macro change input and record to same file

[ben7]

Hi all,

I am trying to pull in some weather data from the government's Bureau of Meteorology. The way their data is stored is only accessible by each weather station number. Below is an example of one of them. If you click the 'All years of data' link near the top right of the page, you download a zip file with 2 excel files (one containing 'Data1' and the other containing 'Data 12'

http://www.bom.gov.au/jsp/ncc/cdio/wData/wdata?p_nccObsCode=139&p_display_type=dataFile&p_stn_num=00...

I would like to go to each weather station, download the zip and combine all 'Data1' files vertically (put the data set under the previous weather station's).

I have a list of the weather stations numbers (e.g. 001021) in an excel doc, however they do not have leading zeros (they are fixed at 5 digits).

Is this possible?

Thanks in advance!

[BrandonB]

Yes, this should be possible. To add the leading zeros to your numbers, go ahead and change the field to a string with a select tool and then use a formula tool with the formula padleft([the numbers field], 5, “0”). This will “pad left” the numbers with leading zeros until they are 5 characters long.

Then you will need to build the URLs of each of these pages with another formula tool.

After that you will need to create a workflow that takes a single one of these URLs and uses a download tool to pull down and parse out the zip file link. You will then turn this workflow into a macro which feeds in each of the URLs that you built previously.

Then you will need to establish a place where to save these files so I would use the number made in the first step in this formula as part of your save location.

Finally, connect it all together! I can help you build this if any of the points aren’t clear, but that is the general method of approaching this task.

[ben7]

Thanks for that!

If I have a list of say 1000 weather station numbers in a separate excel file, how would I feed this into a formula?

[BrandonB · Accepted solution]

Workflow attached! I have also annotated each section using containers so that you can see what needs to be done. You will need to change the location to save the files to in the last container as well as feed in your own list of stations in the very beginning. 

[ben7]

Perfect. I parsed it a little differently but using it as a matrix input as opposed to reading it in through a macro is totally the way to go!

The next step is to extract csv files from the zips. If there are 2 csv files in it, how do I extract the on whose name contains "Data1"?