topic Re: Continue how to use JSL to process the JSON file from the web download? in Discussions

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

lwx228 — Fri, 11 Oct 2019 07:44:08 GMT

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!

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

lwx228 — Fri, 11 Oct 2019 07:38:21 GMT

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

headers( {"…… "} )

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

lwx228 — Fri, 11 Oct 2019 07:52:03 GMT

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

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

Craige_Hales — Sat, 12 Oct 2019 00:02:28 GMT

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.

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

lwx228 — Sat, 12 Oct 2019 08:27:09 GMT

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.

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

lwx228 — Sat, 12 Oct 2019 08:49:59 GMT

I try to save the dt after regular processing as TXT text without Table Headers with JSL, and then read the text as TXT with JSL. How can replace the specified character of TXT with null with regex?

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

lwx228 — Sat, 12 Oct 2019 09:32:29 GMT

I don't know how to write regex replace code in JSL.Thank you very much!

Replace all the following three characters
\"!

txt=loadtextfile("C:\Users\Administrator\Desktop\Untitled 6.txt");
txt = regex(txt,"\\","");

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

Craige_Hales — Sat, 12 Oct 2019 13:12:06 GMT

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 \!

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

lwx228 — Sat, 12 Oct 2019 14:54:49 GMT

OK.

Thank Craige!

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

UersK — Wed, 08 Feb 2023 05:07:57 GMT

Thanks!