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; } } }

Continue how to use JSL to process the JSON file from the web download?

lwx228

Hello everyone!I continue to try the JSL download of the WEB JSON file, and since the JSON structure is different, the successful download of the JSON in the JSL log, but the structure of the JSON file is different, I don't know how to arrange it into a table with JSL.

url="http://pdfm.eastmoney.com/EM_UBG_PDTI_Fast/api/js?rtntype=5&token=4f1862fc3b5e77c150a2b985b12db0fd&cb=jQuery18308309053384067384_1570754165747&id=6037121&type=m5k&authorityType=fa&_=1570754540714";
dt = Open(url, JSON );

Using browser to download the JSON file, remove the log message for an error, and then process it with JSL, can get the table, but the table is not what I want.Please advice from experts.Thank you very much!

5m.txt

Accepted answers

Craige_Hales

You'll have to split up the data column; JMP imported exactly what is in the JSON file...there are no other column names and data is a single field. You can make new columns (with the name you need) using formulas similar to this:

use the word() function with comma for the delimiter; this example selects the 3rd item between commas

You can do something like this to avoid hand-editing the downloads:

txt=loadtextfile("http://pdfm.eastmoney.com/EM_UBG_PDTI_Fast/api/js?rtntype=5&token=4f1862fc3b5e77c150a2b985b12db0fd&cb=jQuery18308309053384067384_1570754165747&id=6037121&type=m5k&authorityType=fa&_=1570754540714");
txt = regex(txt,"\((.*)\)$","\1"); // strip the jQuery18308309053384067384_1570754165747( ...json data... ) wrapper
dt = jsontodatatable(txt);

The regex uses

\( -- this is a literal open parenthesis

(.*) -- this is a capturing group that grabs as much as it can into the first back reference

\)$ -- this is a literal close parenthesis, at the end of the string

then

"\1" says the result of the regex is whatever the capturing group found between the parentheses.

Craige_Hales

This has some info about the GLOBALREPLACE option and some examples:

https://community.jmp.com/t5/Uncharted/Regex/ba-p/21008

I'm not sure what the character is; you might need to use Unicode escapes to represent it, see \!Uxxxx here:

https://community.jmp.com/t5/Discussions/best-practices-around-special-characters-in-JSL/m-p/214000

I *think* what is really happening is JMP is escaping some quotation marks and it looks confusing. Use the write() function to see the test without escapes:

using write() to see strings without escapes

I reformatted the text with a newline after the commas; it is actually all run together as sent from the JSON source. The data in txt is just " but when JMP displays the value as a quoted string (using print, for example), JMP will escape the embedded " using \!" .

Here is is using print(), notice the very first and last " are not escaped:

$internal " are escaped with \!$ internal " are escaped with \!

All comments

lwx228

I searched for posts that might be handled with this code, but tried and failed.

headers( {"…… "} )

lwx228

How to write JSL to download JSON directly from the WEB and automatically process it to get a normalized table?Thanks!

Craige_Hales

use the word() function with comma for the delimiter; this example selects the 3rd item between commas

You can do something like this to avoid hand-editing the downloads:

txt=loadtextfile("http://pdfm.eastmoney.com/EM_UBG_PDTI_Fast/api/js?rtntype=5&token=4f1862fc3b5e77c150a2b985b12db0fd&cb=jQuery18308309053384067384_1570754165747&id=6037121&type=m5k&authorityType=fa&_=1570754540714");
txt = regex(txt,"\((.*)\)$","\1"); // strip the jQuery18308309053384067384_1570754165747( ...json data... ) wrapper
dt = jsontodatatable(txt);

The regex uses

\( -- this is a literal open parenthesis

(.*) -- this is a capturing group that grabs as much as it can into the first back reference

\)$ -- this is a literal close parenthesis, at the end of the string

then

"\1" says the result of the regex is whatever the capturing group found between the parentheses.

lwx228

Thank Craige_Hales! with this regex code I was able to combine the methods of dealing with the regex in VBA.
But using regex processing in JSL is something I'll continue to learn.