Import text file fails to honor default field width setting

Question

I'm having a problem where I frequently import text files that have a long numeric field, like a "datetime" in the form of "20141214115959", and JMP keeps displaying it as a truncated number in scientific notation like "2.0141e+13".  In order to make the number display properly, I must manually go into the column setting and increase the width; the automatic field width is always 10 when importing the text file.  But JMP has a program preference for "Default Field Width" which I also tried setting, yet JMP fails to even honor this setting for the purpose of importing text files.  The "Default Field Width" is only honored if I make a new column in a JMP table.

This has to be a bug, right?  Why on earth is the software written so that the default field width when importing a file always hard-coded to 10, and doesn't use the preference?  Is there another way that I can have the field width set correctly without always having to edit the columns manually after importing?

MS Excel has the same problem, and I would have expected JMP to do better, but I guess that's not the case.

Mark_Bailey · Answer

Your example is a user-coded integer that JMP does not recognize as a point in time. You could import this value as a character string and then use JSL functions in the formula of a new numeric data column to parse it into a JMP time and date value. JMP represents any point in time as the number of seconds since midnight January 1, 1904. You can use the column format to display this value in a more friendly way, such as "m/d/y h:m:s" or any other format that suits you.

This prototype script could be converted to a column formula:

// your example long number, representing a point in time

date and time = "20141214115959";

// convert to JMP time by parsing character string value

date time = Date MDY(

Num( Substr( date and time, 5, 2 ) ),

Num( Substr( date and time, 7, 2 ) ),

Num( Substr( date and time, 1, 4 ) )

) +

// add hours

In Hours(

Num( Substr( date and time, 9, 2 ) )

) +

// add minutes

In Minutes(

Num( Substr( date and time, 11, 2 ) )

) +

// add seconds

Num( Substr( date and time, 13, 2 ) );

// now format number of seconds in user-friendly form

Format( date time, "m/d/y h:m:s" );

Jeff_Perkinson · Answer

This does look like an oversight. I'll note it and see if we can get it addressed in a future release.

In the mean time the script below will change the field width of all the selected columns in the current data table to 20.

cols = current data table() << Get Selected Columns;

for (i=1, i<=nitems(cols), i++,
  column(cols[i])<<set field width(20);
);

You could make a custom menu or toolbar button to do this.

-Jeff