cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
emmablue
Level III

how to use jsl to read json file time column

I'm trying to use jsl to open a json file. The json file has a time column in the format of yyyy-mm-ddThh:mm:ss, but I'm struggling how to read it correctly. 

 

I tried to use this but it was not working

Open(path1,JSON Settings(
Stack( 0 ),
Row( "/root/value" ),
Col("/root/value/datetime",
Column Name( "datetime" ),
Fill( "Use Once" ),
Type( "Numeric" ),
Format( "yyyy-mm-ddThh:mm:ss" ),
Input format ( "yyyy-mm-ddThh:mm:ss" ),
Modeling Type( "Continuous" )
),

 

Any suggestions? Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

Re: how to use jsl to read json file time column

Most likely you want to load it as string and then change the format in JMP table (I don't think JSON specification has date format and usually they are stored as strings). Could you provide small example of the JSON file with at least the date key-value pair?

-Jarmo

View solution in original post

7 REPLIES 7
jthi
Super User

Re: how to use jsl to read json file time column

Most likely you want to load it as string and then change the format in JMP table (I don't think JSON specification has date format and usually they are stored as strings). Could you provide small example of the JSON file with at least the date key-value pair?

-Jarmo
emmablue
Level III

Re: how to use jsl to read json file time column

Thank you for your response! It does default to read as Character. So maybe changing it in JMP is the way to go.

 

Here's an example of the json file,

 

{
"PrimaryKey": "14240",
"DateTimeStamp": "2024-06-25T16:17:34.524327-05:00",
"DateTimeStampLocal": "2024-06-25T16:17:34.523",
"DateTimeStampUtc": "2024-06-25T21:17:34.524327",
},

txnelson
Super User

Re: how to use jsl to read json file time column

Please use the 

     txnelson_0-1719868588852.png

icon located at the top of the Discussion input screen to copy in your JSL statements.  It really enhances the ability of the reading and understanding of the code.

 

Jim
emmablue
Level III

Re: how to use jsl to read json file time column

Will do!

jthi
Super User

Re: how to use jsl to read json file time column

When you get the data to JMP table (Preview might work)

jthi_0-1719894398349.png

you can then use Word() (or Words()) and InFormat() (I suggest using Format Pattern with it) to clean your strings. If you need to keep the decimals it will be more complicated as you will have to handle those separately and same for the timezone.

 

Below is a small example which gives an idea what you can do

Names Default To Here(1);

str = "2024-06-25T16:17:34.524327-05:00";
{firstpart, lastpart} = Words(str, ".");

date = Informat(firstpart, "Format Pattern", "<YYYY>-<MM>-<DD>T<hh24>:<mm>:<ss>");
dec = Num("0." || Word(1, lastpart, "-+"));
-Jarmo
emmablue
Level III

Re: how to use jsl to read json file time column

Not sure if I followed this. But I followed your first suggestion to read the json file in as string, and then change it to numeric time format later. And it worked! Thank you again for your help.

jthi
Super User

Re: how to use jsl to read json file time column

JMP might be able to directly change the format of your date strings to JMP datenum and that happens when you convert the columns to numeric.

 

Using In Format() is for cases where you have to do something more complicated for the string formatted dates (sometimes required).

-Jarmo