Issues importing Excel sheets in JMP 16; dates and conversion of decimal separators

[#1]

Hi all, 

I'm having issues importing excel sheets into JMP 16, some of these issues are also present in JMP 14. 

I have two sheets that I am trying to import and work with in JMP and they present different issues. 
Both sheets contain dates written as: dd/mm/yyyy  and values using "," as a decimal separator. 
One sheet reads the dates just fine, and they are categorized with the correct format and data type. The other is read as text and categorical. 
JMP 16 also cannot handle imports with the , - decimal separator, despite it working fine in JMP 14. 

In this thread it was recommended to change data type manually: How do I import data from excel? - JMP User Community
This doesn't immediately work as JMP 16 simply converts all cells to NAN - additionally when there are many results columns this would be quite tedious.

Changing preferences marked  JMP 16 preferences > Widows Specific > Use JMP Language rather than system settings for number, date and currency formats. 
Allows me to use . instead of , as separator - but I need to be able to import data using the , - decimal separator. 
Does anyone know how to solve this problem? 

Thank you in advance. 

[#2 · Accepted solution]

You could import all of the data as character and then use JMP formulas to convert data to the correct type.  I will sometimes use the Scripting Tools add-in for this by selecting a bunch of columns that were imported with the comma format (or even cases where there is a mix of '.' and ','), selecting Custom Transformation from the Cols menu, and then use this formula to transform all columns:

Num(Substitute(<column>,",","."))

Then use standardize attributes to set modeling types to continuous if needed and hide/exclude all of the original columns.  For dates I would do something similar using the Parse Date function. 

     

That results in something like this:

     

[#3]

Thank you for your answer!

I was hoping to avoid needing to change the decimal separator, but this method is much faster and smarter than using find and replace.