topic Re: how to use jsl to read json file time column in Discussions

how to use jsl to read json file time column

emmablue — Mon, 01 Jul 2024 21:02:39 GMT

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!

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

jthi — Mon, 01 Jul 2024 21:13:05 GMT

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?

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

txnelson — Mon, 01 Jul 2024 21:18:39 GMT

Please use the

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.

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

emmablue — Mon, 01 Jul 2024 21:42:26 GMT

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",
},

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

emmablue — Mon, 01 Jul 2024 21:42:52 GMT

Will do!

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

jthi — Tue, 02 Jul 2024 04:33:45 GMT

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

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

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

emmablue — Tue, 02 Jul 2024 15:25:01 GMT

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.

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

jthi — Tue, 02 Jul 2024 15:28:20 GMT

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