Import text file fails to honor default field width setting
Dec 15, 2014 2:26 PM(2405 views)
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.
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
Num( Substr( date and time, 9, 2 ) )
// add 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