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

[#1]

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:

 

File imported in Jmp:

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!

[#2 · Accepted 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]

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.

[#4]

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.

[#5]

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.