Subscribe Bookmark RSS Feed

Jmp drops decimals when importing scientific or percentage number format for Excel

thomasz

Community Trekker

Joined:

Mar 11, 2016

When importing an Excel table with scientific or percentage formatting, Jmp drops decimals that are not displayed in the format. When formatted as a number - or even a fraction, all decimals are imported correctly.

Also, importing dates does not work when the date format is in dd-mm-yyyy format and Jmp finds a date in the first row that could fit into the mm-dd-yyyy format. Jmp simply delete all dates that do not fit the mm-dd-yyyy format.

 

Here is an example:

Excel file:

 2017-10-27 13_54_40-decimal_test.xlsx - Excel.png

 

File imported in Jmp:

2017-10-27 13_54_48-Sheet1 - JMP Pro [4].png

Decimals for percentage and scientific formats are lost during import (changing format does not help...)

Forcing a text import is a workaround for the date issue when the data file contains column names that can only be interpreted as text, but this workaround does not work for the number issue. Please fix both issues!

 

1 ACCEPTED SOLUTION

Accepted Solutions
briancorcoran

Joined:

Jun 23, 2011

Solution

Hi Thomasz,

 

You don't mention if you are using a Windows or Mac OS.  They have a different code base for portions of the functionality.  We have fixed some scientific/decimal and percent formatting issues on the Mac in the upcoming JMP 14.  If you are using Windows, I would send the issue to support@jmp.com.   

 

For the m/d/y issue, if you explicitly type the column within Excel prior to import, it should come in correctly.

 

Brian Corcoran

JMP Development

3 REPLIES
briancorcoran

Joined:

Jun 23, 2011

Solution

Hi Thomasz,

 

You don't mention if you are using a Windows or Mac OS.  They have a different code base for portions of the functionality.  We have fixed some scientific/decimal and percent formatting issues on the Mac in the upcoming JMP 14.  If you are using Windows, I would send the issue to support@jmp.com.   

 

For the m/d/y issue, if you explicitly type the column within Excel prior to import, it should come in correctly.

 

Brian Corcoran

JMP Development

thomasz

Community Trekker

Joined:

Mar 11, 2016

Hi Brian

It's on windows and I tried up to Jmp 14 EA7. All Jmp versions know to me on Windows are affected.

 

Thanks for the info about explicitly typing the column in Excel. Maybe it will be handy somewhere, but that solution requires that the Excel file is modified which is not always possible.

 

In case it is difficult to estimate the correct date format, a simple solution could be to make it possible to explicity specify the format (including date format) for each column in JMP. In many situations the format is known, so Jmp wouldn't have to guess the format. Jmp could also choose to scan more than one row before determining the date format. It would not be bullet-proof, but combined with using the correct Windows locale settings as a first guess if both formats are possible, it would be an improvement.

 

ih

Community Trekker

Joined:

Sep 30, 2016

If you use the JMP add-in for excel some (maybe all) of these issues go away, for sure the scientific number and percent problems.  Of course that would be a nightmare to script...

 

My workaround for excel files I import regularly is to use VBA or vbscript to open the file and output a csv, then import the csv file into JMP.  That also gets around issues importing excel files containing custom functions.