Change to local format iterating through columns with date, numbers and strings

FN

I have table I would like to format appropriately (attached as well)

The main complexity comes from the following three problems:

1. Users of this script will have different date formats as they will have different windows machines in different languages. For example, the database input for the column datetime is (always) in format dd-mon-yyyy hh:mm:ss which does not exist in Spanish or German languages. 

 

2. Users will also have different number formats. All the columns will result in empty numeric values as it is expecting a comma for decimals (3.14 will not be recognized as 3,14).

 

3. After the first column, which is always a date-time, columns can contain numeric or categorical values. In the attached example, column3 contains comments so there is no change of format needed.

 

Potential solutions:

 

Problem #1 (date-time using locale)

Perhaps the easier is to verify that JMP langague is in English, activate/deactivate  the option of ignoring the locale settings:

 

dt = Current Data Table();
<BR />// TODO assert that JMP language is English
Preferences( Use JMP Locale Settings( 1 ) );
dt:datetime<< Set Data Type( Numeric ) << Modeling Type( continuous ) << Format( "Locale Date Time h:m:s" );
Preferences( Use JMP Locale Settings( 0 ) );
dt:datetime<< Format( "Locale Date Time h:m:s" );

Problem #2

To change the format from english to locale, we can do something similar. More info in this post

https://community.jmp.com/t5/Martin-Demel-s-Blog/JMP-and-Excel-A-Never-ending-story-Part-2/ba-p/35099

 

dt = Current Data Table();<BR />
// TODO assert that JMP language is English
Preferences( Use JMP Locale Settings( 1 ) );
dt:column1 << Set Data Type( Numeric ) << Modeling Type( continuous );

Preferences( Use JMP Locale Settings( 0 ) );
dt:column1 << Set Data Type( Numeric ) << Modeling Type( continuous );

Problem #3

How to code use the best guess from JMP so columns are only passed from characters to numeric?

 

Following this post: https://community.jmp.com/t5/Discussions/Convert-data-to-numeric-Only-If-Appropriate/td-p/106692

dt = CurrentDataTable();


// TODO Assert that JMP is in English
Preferences( Use JMP Locale Settings( 1 ) );


// Convert to numeric only if nedeed
For( i = 2, i <= N Cols( dt ), i++,
	isNumeric = "NO";
	For( theRow = 1, theRow <= 10, theRow++,
		If( Column( i )[theRow] != "" & Is Missing( Num( Column( i )[theRow] ) ) == 1,
			isNumeric = "NO";
			Break();
		,
			isNumeric = "YES"
		)
	);
	If( isNumeric == "YES",
		Column( i ) << set data type( numeric ) << Modeling Type( continuous );
		
		Preferences( Use JMP Locale Settings( 0 ) );
		Column( i ) << set data type( numeric ) << Modeling Type( continuous );
		Preferences( Use JMP Locale Settings( 1 ) );
	);
);

Preferences( Use JMP Locale Settings( 0 ) );

 

I am missing the assertion that makes sure that JMP is in English and if there is a more efficient way to this (it might have 100,000 rows and hundreds of columns).

jmp_formatting.jmp

bernd_heinen

I may not have understood the requirement fully. From what I see, all columns are character and there is no additional information that would identify a datetime column, except for its name. If that name is always the same, you don’t need to iterate through the columns, you can just name it in your script. The alternative is to write a little dialog that lets the user pick the column that shall be transformed. Also I don’t exactly get what the goal is: shall the date be displayed in the same format everywhere in the world, or shall it be displayed according to the local settings? For the moment, I assume that the respective column has always the same name and that the date format shall be displayed in local format. Then the attached script may do at least parts of what needs to be done.

reformat.jsl