BookmarkSubscribe
Choose Language Hide Translation Bar
djhanson
Community Trekker

JMP save as Excel (looses decimal format)

Hi, is there any way to preserve the number of rounded decimals from a JMP table saved as Excel? (.xls)
(Windows 10/JMP 12.2)
So for example: dt<<save(myPath || "myTable.xls", xls);

In this case, the JMP table has a format of 1 rounded decimal place (e.g. 59.2), but the resulting Excel table shows all decimal places (e.g. 59.21546).  The Excel cell color formats do look great and match the JMP table good.

I am wondering do I need to actually remove decimals somehow instead? (not rounding, but removal)  If so, what's a good way to do this?  cheers, DJ

0 Kudos
2 REPLIES 2
txnelson
Super User

Re: JMP save as Excel (looses decimal format)

You can use the Format() or Round() functions to truncate the absolute mvales in JMP, which may help you when you upload the data.  But both of those will deminish some of the precision of the data.

Jim
Highlighted
ih
ih
Community Trekker

Re: JMP save as Excel (looses decimal format)

In case you need to do this in a script or on a large table, here is a crude implementation of Jim's suggestion to use Round() that might get you started.

 

Names default to here( 1);

//Open a table and set a column width
dt = Open( "$Sample_data/iris.jmp" );
Column( dt, 2 ) << Format( "Fixed Dec", 10, 0 );

//Make a copy of the table
dt2 = dt << Subset( All rows, Selected columns only( 0 ) );

//for each column
cols = dt2 << Get Column Names;
for( i = 1, i <= N Items( cols ), i++,

	//Get the format of the column and convert it to a list
	frmt = Column( dt2, cols[i] ) << Get Format;
	frmt = Parse("{" || Regex(char(name expr(frmt)), "Format\((.*)\)", "\1") || "}");
	
	//if formatted as a fixed decimal
	if(frmt[1] == "Fixed Dec",
		
		//Set the actual value to the rounded number
		Column( dt2, cols[i] ) << Set Values( 
			Round( 
				Column( dt2, cols[i] ) << Get Values, 
				frmt[3]
			)
		)
	)
);

//Save the table and open the temp files
dt2 << Save( "$Temp/mytable.xls", xls );
Open( "$Temp" );

//close the copied table
dt2 << Close Window;