- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
JMP16 Using Custom Date Formats In Scripts
Hello,
I am working on a data table in which the date is formatted as such: 15/09/08/19:12 .
I have managed to convert this using the custom format feature in the data table editor with the following: <YY>/<MM>/<DD>/<hh24><::><mm> but via script I cannot figure how to get this to work.
Below is an exert of the script, any help would be greatly appreciated.
Best Regards,
Luke
path = Get Default Directory();
dt = Pick File("Select .csv file with data", path, {"Excel|csv;xlsx;xls", "JMP Files|jmp;jsl;jrn", "All Files|*"}, 1, 0, "");
Open(dt,
columns(
New Column( "; DateTime", Numeric, "Continuous", Format( "y/m/d h:m"),Input Format( "<YY>/<MM>/<DD>/<hh24><::><mm>", 3 )),
New Column( "Machine ID", Character, "Nominal" ),
New Column( "Z/Xrmv_max", Numeric, "Continuous", Format( "Best", 12 ) ),
New Column( "Z/Xrmv_min", Numeric, "Continuous", Format( "Best", 12 ) ),
New Column( "Z/Xrmv_max-min", Numeric, "Continuous", Format( "Best", 12 ) ),
New Column( "P/Xrmv_max", Numeric, "Continuous", Format( "Best", 12 ) ),
New Column( "P/Xrmv_min", Numeric, "Continuous", Format( "Best", 12 ) ),
New Column( "P/Xrmv_max-min", Numeric, "Continuous", Format( "Best", 12 ) )
),
Import Settings(
End Of Line( CRLF, CR, LF ),
End Of Field( Comma, CSV( 1 ) ),
Strip Quotes( 0 ),
Use Apostrophe as Quotation Mark( 0 ),
Use Regional Settings( 0 ),
Scan Whole File( 1 ),
Treat empty columns as numeric( 0 ),
CompressNumericColumns( 0 ),
CompressCharacterColumns( 0 ),
CompressAllowListCheck( 0 ),
Labels( 1 ),
Column Names Start( 1 ),
Data Starts( 2 ),
Lines To Read( "All" ),
Year Rule( "20xx" )
)
);
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP16 Using Custom Date Formats In Scripts
I created quick test.csv with only couple of rows and used the option to directly modify it in the import platform:
Script:
Open(
"$DOCUMENTS/JMP/test.csv",
columns(
New Column("time",
Numeric,
"Continuous",
Format("Format Pattern", "<YY>/<MM>/<DD>/<hh24><::><mm>", 14),
Input Format("Format Pattern", "<YY>/<MM>/<DD>/<hh24><::><mm>")
)
),
Import Settings(
End Of Line(CRLF, CR, LF),
End Of Field(Comma, CSV(0)),
Strip Quotes(1),
Use Apostrophe as Quotation Mark(0),
Use Regional Settings(0),
Scan Whole File(1),
Treat empty columns as numeric(0),
CompressNumericColumns(0),
CompressCharacterColumns(0),
CompressAllowListCheck(0),
Labels(1),
Column Names Start(1),
Data Starts(2),
Lines To Read("All"),
Year Rule("20xx")
)
)
So maybe the "result" format must also be done with Format Pattern?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP16 Using Custom Date Formats In Scripts
Syntax when using Format pattern is a bit different. Try something like:
Format("y/m/d h:m"),
Input Format("Format Pattern", "<YY>/<MM>/<DD>/<hh24><::><mm>")
Example table
New Table("Untitled",
Add Rows(1),
New Column("Column 1",
Numeric,
"Nominal",
Format("y/m/d h:m"),
Input Format("Format Pattern", "<YY>/<MM>/<DD>/<hh24><::><mm>"),
Set Selected,
Set Values([3061825920]),
Set Display Width(89)
)
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP16 Using Custom Date Formats In Scripts
Thank you @jthi,
I am still a having issues where the column still is giving null results:
The column is changing to the desired format as if performing manually. Could it be because the original column name is not changing due to the name being "; The schema definition " ?
Thanks again.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP16 Using Custom Date Formats In Scripts
If you try importing it first as character, what does it look like? Only empty cells?
New Column( "; DateTime", Character, "Nominal),
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP16 Using Custom Date Formats In Scripts
If taken as Character we get the following:
Also, in regards to my previous reply, I edited the original csv and removing the ";" does not change the issue.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP16 Using Custom Date Formats In Scripts
I created quick test.csv with only couple of rows and used the option to directly modify it in the import platform:
Script:
Open(
"$DOCUMENTS/JMP/test.csv",
columns(
New Column("time",
Numeric,
"Continuous",
Format("Format Pattern", "<YY>/<MM>/<DD>/<hh24><::><mm>", 14),
Input Format("Format Pattern", "<YY>/<MM>/<DD>/<hh24><::><mm>")
)
),
Import Settings(
End Of Line(CRLF, CR, LF),
End Of Field(Comma, CSV(0)),
Strip Quotes(1),
Use Apostrophe as Quotation Mark(0),
Use Regional Settings(0),
Scan Whole File(1),
Treat empty columns as numeric(0),
CompressNumericColumns(0),
CompressCharacterColumns(0),
CompressAllowListCheck(0),
Labels(1),
Column Names Start(1),
Data Starts(2),
Lines To Read("All"),
Year Rule("20xx")
)
)
So maybe the "result" format must also be done with Format Pattern?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: JMP16 Using Custom Date Formats In Scripts
Hi @jthi , thank you so much! That has worked and has fixed the issue!