Help with writing a loop in JSL

[#1]

I need to download the data that is available through a url:

http://www.arpalazio.net/main/aria/sci/basedati/chimici/BDchimici/RM/MedieGiornaliere/RM_PM2.5_2019_gg.txt

To download the data for different years, I need to change the year in the url, as shown in red. I would like to write a loop in JSL that runs through the years from 1990 to 2021 and put the data in a table. I am new to JSL so any help on that end is greatly appreciated.

[#2 · Accepted solution]

You need couple of things for this: Loop (for-loop), way to build the url (eval insert), way to load data from the url (Open with correct arguments) and way to combine the data (concatenate).

Names Default To Here(1);

//We can use Eval Insert to replace ^temp_year^ in the string
dl_url = "http://www.arpalazio.net/main/aria/sci/basedati/chimici/BDchimici/RM/MedieGiornaliere/RM_PM2.5_^temp_year^_gg.txt";
dt = New Table(); //table to collect results

//for-loop
For(i = 1990, i <= 2021, i++,
	temp_year= Char(i);
	year_url = Eval Insert(dl_url);
	//downloaded first one file by hand -> opened in JMP to get Open with correct parameters and replaced file path with URL
	Try(
		dt_temp = Open(
			year_url,
			Import Settings(
				End Of Line(CRLF, CR, LF),
				End Of Field(Spaces, Space, CSV(0)),
				Strip Quotes(1),
				Use Apostrophe as Quotation Mark(0),
				Use Regional Settings(0),
				Scan Whole File(1),
				Treat empty columns as numeric(0),
				CompressNumericColumns(0),
				CompressCharacterColumns(0),
				CompressAllowListCheck(0),
				Labels(1),
				Column Names Start(1),
				Data Starts(2),
				Lines To Read("All"),
				Year Rule("20xx")
			)
		);
		dt << Concatenate(dt_temp, Append to first table(1));
		Close(dt_temp, no save);
	,
		Show(exception_msg)
	);
);

Concatenate should be able to handle columns which are missing by creating missing values and Try is fairly easy way to handle URLs which don't exist (a bit lazy tho).

[#3]

Thanks a lot @jthi. This is very helpful.

I have a follow-up question and was wondering if you could help me with that. There is another database from which I need to download the data with almost the same method (i.e., changing the year value in the url). Here is the url:

https://www.arb.ca.gov/aqmis2/pickdl_hrly.php?param=OZONE&units=007&year=2021&county_name=--COUNTY--...

The only difference is that to get the data from this url, I need to click on "Get Data" button on that page. Is there any way that this can be done through JSL?

[#4]

To my knowledge you cannot click elements in websites with JMP, but you might be able to use HTTP Requests to get the data. For this you would have to figure out how the requests are done (developer tools of browser can be helpful with this).

[#5]

I see! good to know, thanks a lot.

Sina

[#6]

Yes, the developer tools make this easier than learning HTML Forms.

Using web browser developer tools to examine the link that was used to download a file

The link has parameters, which you can modify with JSL before opening it with open(). FF shown above, chrome is similar, but arranged differently.

Your original link requested 12 months of data. The site did not want to send that much, I stopped at 31jan instead of 31dec.

[#7]

@Craige_Hales This is immensely helpful and saves me a lot of time. Thanks a lot!!!