topic Re: Keep Leading Zeros in Discussions

Keep Leading Zeros

ACraig — Thu, 27 Feb 2020 18:49:40 GMT

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.

Re: Keep Leading Zeros

Mark_Bailey — Thu, 27 Feb 2020 18:54:57 GMT

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.

Re: Keep Leading Zeros

txnelson — Thu, 27 Feb 2020 19:33:54 GMT

@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

Re: Keep Leading Zeros

hogi — Tue, 02 Dec 2025 21:30:09 GMT

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