Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- JMP save as Excel (looses decimal format)

Topic Options

- Start Article
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

JMP save as Excel (looses decimal format)

Dec 29, 2017 9:11 AM
(817 views)

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

2 REPLIES 2

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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;
```