Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
lwx228
Level VII

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

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

 

 

2019-10-11_15-08.png

 

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!

 

 

2019-10-11_15-14.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Craige_Hales
Staff (Retired)

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

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 commasuse 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

View solution in original post

Highlighted
Craige_Hales
Staff (Retired)

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

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

Craige

View solution in original post

8 REPLIES 8
Highlighted
lwx228
Level VII

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


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

 

headers( {"…… "} )
Highlighted
lwx228
Level VII

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

How to write JSL to download JSON directly from the WEB and automatically process it to get a normalized table?Thanks!2019-10-11_15-48.png

Highlighted
Craige_Hales
Staff (Retired)

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

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 commasuse 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

View solution in original post

Highlighted
lwx228
Level VII

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

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.
Highlighted
lwx228
Level VII

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

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?

 

2019-10-12_16-38.png

Highlighted
lwx228
Level VII

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

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

 

 

Highlighted
Craige_Hales
Staff (Retired)

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

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

Craige

View solution in original post

Highlighted
lwx228
Level VII

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

OK.

Thank Craige!

 

2019-10-12_22-54.png

Article Labels

    There are no labels assigned to this post.