nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to parse a JSON file opened from URL?

jwebber

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.

Accepted answers

Justin_Chilton

You can add the "JSON" option to the Open function to force the JSON file to be opened as a data table.

Open( "http://date.jsontest.com/", JSON );

Here's a comparison from the log when using this option vs not using the option:

open("http://date.jsontest.com/")
/*:

"{
   \!"time\!": \!"01:21:07 PM\!",
   \!"milliseconds_since_epoch\!": 1505827267889,
   \!"date\!": \!"09-19-2017\!"
}
"
//:*/
open("http://date.jsontest.com/", JSON)
/*:

Data Table( "date.jsontest.com" )

jwebber

I updated my software to v13.2 and can now open my URL and have it correctly parsed into a new datatable using

open("my URL","json")

Executing this command is still substantially slower than the GUI file-->open command (the processing time is about twice as long). When using the GUI, a dialog box pops up showing the download status of the file, information I do not receive with the open() JSL command. Even before my upgrade, the GUI file-->open was able to quickly open the URL and correctly parse the file (the output was different than open("my URL",text)). Is there any way to exactly replicate the file-->open process using JSL? Is there a way to record the code generated when executing file-->open (similar to the way recording a macro in excel captures the code generated by using the GUI)?

All comments

Justin_Chilton

You can add the "JSON" option to the Open function to force the JSON file to be opened as a data table.

Open( "http://date.jsontest.com/", JSON );

Here's a comparison from the log when using this option vs not using the option:

open("http://date.jsontest.com/")
/*:

"{
   \!"time\!": \!"01:21:07 PM\!",
   \!"milliseconds_since_epoch\!": 1505827267889,
   \!"date\!": \!"09-19-2017\!"
}
"
//:*/
open("http://date.jsontest.com/", JSON)
/*:

Data Table( "date.jsontest.com" )

jwebber

Thanks for your reply, Justin. I ran your example code and received the following error:

//:*/
open("http://date.jsontest.com/", JSON)
/*:

Unexpected "M". Perhaps there is a missing [ or {.
Line 1 Column 1: ►M",

Tried to open: http://date.jsontest.com/

Cannot open table in access or evaluation of 'Open' , Open/*###*/("http://date.jsontest.com/", JSON)

In the following script, error marked by /*###*/
Open/*###*/("http://date.jsontest.com/", JSON)

I attempted to add the ",JSON" extension to my URL and received the following error:

//:*/
open("my URL",JSON)
/*:

Unexpected "sponseTime". Perhaps there is a missing [ or {.
Line 1 Column 1: ►sponseTime":"2017-09-19T13:33:38.9224228+00:00","S

Tried to open: my URL

Cannot open table in access or evaluation of 'Open' , Open/*###*/(
"my URL",
JSON)

In the following script, error marked by /*###*/
Open/*###*/(
"my URL",
JSON)

Thoughts?

Justin_Chilton

I just tried in JMP 13.0.0 and replicated the error you are seeing. I am using JMP 13.2 and there is no issue.

Would you be able to update to 13.2 and try it again? You can download the updater at jmp.com/update.

jwebber

I'll work on updating my software.

I'm still not sure why, using JSL, I can't replicate the GUI file-->open process that correctly handles my URL and opens a new datatable. There must be some way to script this procedure, right?

jschroedl

JMP 13.2 gives you a bit more control over how the file is opened. Uunfortunately, it's not too obvious that you can control the behavior. You should be able to get what you need.

If the incoming file extension is unknown to JMP, we'll next check the file type drop-down to the right of the edit field. If that has a selection which is not ambiguous then we'll open the file assuming it's a file of the type selected. If the file type dropdown selection is ambiguous (ex. All JMP Files or All Files), we ask the OS to open the file.

So, in your case you can try this:

* Paste the URL

* Change the dropdown from "All JMP Files" to either "Text Data" or "JSON Data File"

* [Optional] You may want to un-check the "Select this filter the next time..." if you don't want it remembered.

* Click OK

The OS dialog will download the file to a temporary location with a crazy name like FSERXVJW with no extension. JMP will try to open the file with the kind you selected in the drop down.

I am interested to know if having the dialog download your json and opened with the "JSON Data File" choice is faster than the Open("URL","json"); route. We may have perf work to do in that case...

John

jschroedl

Clearly, I didn't read your original posting to thoroughly!

You did indeed know that changing to JSON data was the way to go.... I'm glad you have the JSL equivalent from the other replies and it does sound like we have some performance work to do to catch up with the download time of the Open dialog.

John

jwebber

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");

Justin_Chilton

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 <SPAN class="token operator">=</SPAN> <SPAN class="token function">RunProgram</SPAN><SPAN class="token punctuation">(</SPAN> Executable<SPAN class="token punctuation">(</SPAN> <SPAN class="token string">"CSCRIPT.EXE"</SPAN> <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN> Options<SPAN class="token punctuation">(</SPAN> <SPAN class="token string">"\!"</SPAN>C<SPAN class="token punctuation">:</SPAN>\runMe<SPAN class="token punctuation">.</SPAN>vbs\<SPAN class="token operator">!</SPAN><SPAN class="token string">""</SPAN> <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN> Read <SPAN class="token function">Function</SPAN><SPAN class="token punctuation">(</SPAN> <SPAN class="token string">"text"</SPAN> <SPAN class="token punctuation">)</SPAN> <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">;</SPAN>