topic JMP16 Using Custom Date Formats In Scripts in Discussions

JMP16 Using Custom Date Formats In Scripts

LukeFarrell — Sat, 10 Jun 2023 23:35:20 GMT

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

Re: JMP16 Using Custom Date Formats In Scripts

jthi — Thu, 12 Aug 2021 15:18:10 GMT

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

Re: JMP16 Using Custom Date Formats In Scripts

LukeFarrell — Thu, 12 Aug 2021 15:34:33 GMT

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.

Re: JMP16 Using Custom Date Formats In Scripts

jthi — Thu, 12 Aug 2021 15:55:54 GMT

If you try importing it first as character, what does it look like? Only empty cells?

New Column( "; DateTime", Character, "Nominal),

Re: JMP16 Using Custom Date Formats In Scripts

LukeFarrell — Thu, 12 Aug 2021 16:01:24 GMT

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.

Re: JMP16 Using Custom Date Formats In Scripts

jthi — Thu, 12 Aug 2021 16:10:46 GMT

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?

Re: JMP16 Using Custom Date Formats In Scripts

LukeFarrell — Thu, 12 Aug 2021 16:15:00 GMT

Hi @jthi , thank you so much! That has worked and has fixed the issue!