Problem when importing Excel (.xlsx) to JMP 16 on Mac

[#1]

Hi all,

I have a strange problem when importing .xlsx files to JMP 16 on Mac:

some cells in .xlsx file have numbers with 9 decimal places, which are only seen in the value box if you click on the cell in MS Excel or Mac Numbers. Otherwise, the cell format shows only 1 decimal place.

When I import the .xlsx file to JMP 16 on Mac, only 1 decimal place is imported, and there is no way to convince JMP to import all decimal places (I tried changing locale format setting in JMP preference and also in Mac Language & Region preference, I tried resetting all JMP preferences to defaults, I tried to change column numeric format in JMP Excel import Wizard by right-clicking on the header, ...). Nothing works.

However, if I open .xlsx in Mac Numbers or in MS Excel, and save it again as .xlsx, and then import the newly saved file to JMP, it works without a problem. All decimal places are imported.

Even more strangely, when I tried to import the original .xlsx file to JMP on Windows 10, there is no problem, JMP imports all decimal places. So problem is only on Mac.

I was wondering if there is anything else I can try to do? The solution with opening and saving all .xlsx files is not optimal due to many files that I will have to import. Also, I want the solution working on both Mac and Windows.

Best regards

[#2 · Accepted solution]

Hi,

The reason that there is a difference between Windows and Mac is that there are two different third party libraries used to read the contents of the Excel file.  These files are incredibly complex and JMP needs another library to help interpret the contents.  The same library used on Windows is not available for the Mac.  Generally, these libraries will provide the numeric that you can see when we ask for the cell contents.  On Windows, there is a mode to override this so we can get the full numeric, but this does not exist on the Mac.  If you were to change the number of decimals in your Excel sheet to show more in Excel, then you would get more in JMP.  Also, if you changed the column type to General we would likely import all the decimals as they would likely all show in Excel.

I'm sorry there is not a better workaround.  I have mentioned the issue to the library vendor on the Mac before.

Brian Corcoran

JMP Development