422135_en-US

liliconcom

Help with writing a loop in JSL

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.

 

 

ScriptingRe: Help with writing a loop in JSL

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

Re: Help with writing a loop in JSL

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--&basin=SC-South+Coast&latitude=--PART+OF+STATE--&report=PICKDATA&order=basin%2Ccounty_name%2Cs.name&submit=Retrieve+Data&ptype=aqd&std15=&o3switch=new&hours=all&statistic=&qselect=Screened&start_mon=1&start_day=1&mon=12&day=31&submit=All+Sites&rows=29

 

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?

Re: Help with writing a loop in JSL

I see! good to know, thanks a lot.

 

Sina