848696_en-US

liliconcom

JMP 18 not importing Excel data with correct data type

I recently upgraded to JMP 18 and am encountering issues with scripts I developed in JMP 16 for importing Excel files. Specifically, JMP 18 is not recognizing data types correctly when importing tables:

 - Many columns with characters in every row are being imported as numeric.
 - Numeric columns (with no missing data) are being imported as character.

I noticed that the Excel Import Wizard allows manual specification of data types, but with 1000+ columns (such as in survey data), manually adjusting each one is not practical.

Is there a way to automate or globally set correct data types when importing Excel files in JMP 18?

Any guidance would be greatly appreciated!

Automation and ScriptingData AccessRe: JMP 18 not importing Excel data with correct data type

If you could provide a sample Excel table it would be very helpful.

Re: JMP 18 not importing Excel data with correct data type

Hi Jim

 

Attached is an Excel file and the resulting JMP file following import. The Source ID and Status columns are correctly imported as character. However, the Source Description, Provider Email and File Extension columns are being imported as numeric, when they contain only text. Also, the Due Date column is imported as character, when it should be numeric.

 

I never had this issue with JMP 16 and now all my Excel import scripts are failing.

 

Regards

 

Sean

Re: JMP 18 not importing Excel data with correct data type

For me that excel seems to be importing fine. 

Have you tried if using Open and then copying that script instead of using the script created in older version of JMP? If I remember correctly, JMP18 did change how excel is imported into JMP.

Re: JMP 18 not importing Excel data with correct data type

I used the Excel Wizard when opening it previously, not a previously created script. However, when I opened it again now it is showing all the columns as character, even the numeric ones like Due Date, same as your import above. I didn't do anything different when importing it.

 

 

 

 

Re: JMP 18 not importing Excel data with correct data type

Fixing those columns which should be numeric from character is much easier than trying to fix missing data. This is a bit dirty solution and I haven't used something like this earlier, but it might work

Names Default To Here(1);

dt = Open(
	"$DOWNLOADS/Sample Excel table file import.xlsx",
	Worksheets("Sheet1"),
	Use for all sheets(0),
	Concatenate Worksheets(0),
	Create Concatenation Column(0),
	Worksheet Settings(
		1,
		Has Column Headers(1),
		Number of Rows in Headers(1),
		Headers Start on Row(1),
		Data Starts on Row(2),
		Data Starts on Column(1),
		Data Ends on Row(0),
		Data Ends on Column(0),
		Replicated Spanned Rows(0),
		Replicated Spanned Headers(0),
		Suppress Hidden Rows(0),
		Suppress Hidden Columns(0),
		Suppress Empty Columns(0),
		Treat as Hierarchy(0),
		Multiple Series Stack(0),
		Import Cell Colors(0),
		Limit Column Detect(1),
		Column Separator String("-")
	)
);

collist = dt << Get Column Names("String");
For Each({colname}, collist,
	r = Column(dt, colname) << Set Data Type("Numeric", << Fail On Conversion Error, <<RETURN failed="" rows=""></RETURN>

Re: JMP 18 not importing Excel data with correct data type

Thanks Jarmo

 

I can fix it to numeric in the Excel Wizard and save the new script for that. It isn't a huge issue for tables with a small number of columns, but I import some with 100's of columns and it isn't feasible to go through them all identifying which are numeric and which are character. Worked perfectly in my earlier versions of JMP. 

 

 

Re: JMP 18 not importing Excel data with correct data type

That is exactly the reason why I would use a simple script to do the conversion after importing the file into JMP Table. You can turn that type of script into toolbar item, add-in or custom function to make it easier to perform it as needed.

 

JMP18 did change how JMP imports excel files JMP Note 655981 - Changes to the import of Microsoft Excel files in JMP® 18 which could be why you are seeing a difference. Also it might be that JMP isn't able to properly deal with the Excel Table, if that data was just in a sheet, JMP seems to be able to detect the types better