cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
thomasz
Level IV

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

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

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

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

View solution in original post

4 REPLIES 4

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

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
Level IV

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

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.

 

Ben_Sagalovsky
Level II

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

This is still happening with JMP 16 in Windows !!!!

 

Incredible that JMP has not fixed this after soooooo long! Just lost a lot of work when I realized that the numbers were not right.

ih
Super User (Alumni) ih
Super User (Alumni)

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

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.