Keep Leading Zeros

Question

One of the columns in my data set is Employee ID. All employee ID's have a length of 8 and begin with leading zeros. When I open the excel file that has the data JMP seems to drop those leading zeros. Is there a way to get JMP to not do that? Or does it have to do with the way that column is stored in the xlsx file?

I tried using the code I found on here to just add them back in but it added them for the first row and then just changed every character to 0's for every other row. Any help is greatly appreciated.

hogi · Answer

Another Approach: use right() with a filler :

Names Default to Here(1);
dt = Open( "$SAMPLE_DATA/Big Class Families.jmp" );

new column ("weight_8",  Character, Formula(right(Char(:weight),8,"0")))

txnelson · Answer

@Mark_Bailey  has shown the best solution.  However, if you already have one or more data tables in JMP that have the incorrect values for the ID column, below is a very simple script that will convert the values

Names Default To Here( 1 );
dt = Current Data Table();

// Change the ID column to character
dt:ID << set data type( character );

// Loop through each row adding in the missing leading zeros
For Each Row( 
	:ID = Substr( "00000000", 1, 8 - Length( :ID ) ) || :ID;
);

Just swap out the reference to :ID to the actual name of your column

Mark_Bailey · Answer

It sounds like the numeric code is just a label or category (ID), so you could keep it as character data. Use the option in the preview pane of the Excel Import Wizard to change the default interpretation (Numeric) to Character.