How to parse a JSON file opened from URL?

[#1]

I would like to import a JSON file into JMP that is downloaded off of the web. The best way I've been able to do this is through the GUI, manually selecting File --> Open --> and entering the URL into "File Name". If I select "JSON Data File (*.json)" from the file type list, JMP will download the file and open the parsed data into a new datatable. Unfortunately, I haven't been able to write a script that replicates this process.

A simple open("URL") command saves the JSON content of the URL to the log and does not open a new datatable. I tried amending the code to: open("URL","json"), but this returns an error and no data are received.

I can run save text file("output path/filename.json",open("URL")) and then open("output path/filename.json") and JMP will automatically parse the file into a new datatable. This workaround accomplishes my goal, but the processing time is substantially longer than the manual file-->open process described in the previous paragraph. Many of the URLs I'm trying to import contain large amounts of data, so I'd like to minimize processing time as much as possible.

How can I download a JSON file directly from a URL and have the data open in a correctly parsed table?

I am running JMP 13.0.0 on windows.

[#2]

Thank you all for your replies. I ended up working around this issue by using a VB script to download the .json file from my URL. Initiating the download using VBS was much faster than any method I could find with JMP.

Fortunately, I was still able to use JSL for the whole process. I wrote the VB script in JSL, saved the data with a .vbs extension and then issued an open() command from JSL to execute the VB script (which then downloaded my data). 

Here's an example of the code I used:

//define static VB script
one="dim xHttp: Set xHttp = createobject(\!"Microsoft.XMLHTTP\!")\!rdim bStrm: Set bStrm = createobject(\!"Adodb.Stream\!")\!r";
two="xHttp.Open \!"GET\!", \!"";
three="\!", False\!r";
four="xHttp.Send\!rwith bStrm\!r.type = 1\!r.open\!r.write xHttp.responseBody\!r";
five=".savetofile \!"C:\users\";
six="\desktop\data.json\!", 2\!rend with";

//define user.name
user.name="jwebber";

//define URL
url=www.somewebaddress.com;

//create full VB script
vbs=concat(one,two,url,three,four,five,user.name,six);

//save VB script
save text file("c:/users/"||user.name||"/desktop/download_data.vbs",vbs);

//open vbs file
open("c:/users/"||user.name||"/desktop/download_data.vbs"); 

[#3]

Hi @jwebber,

Glad to hear you were able to find a solution.

One suggestion I would have is to use RunProgram to execute your VBScript file instead of using the Open Function.

Using RunProgram ensures that the script is run on a VBScript host instead of relying on the computer's file association for vbs files (which could potentially be a text editor).

Another benifit of using RunProgram is that you can use the "text" option for the ReadFunction that will pause your JSL script until the VBScript has completed.

Below is some information I previously shared on the JMP Community here.

You can use either the WScript or CScript executables to run a VBScript file using RunProgram. The only difference is that WScript will show errors in a MessageBox, whereas CScript writes the error to the console/stdout.

Here is an example using CScript. 

rp = RunProgram( Executable( "CSCRIPT.EXE" ), Options( "\!"C:\runMe.vbs\!"" ), Read Function( "text" ) );

---