JMP 18 not importing Excel data with correct data type

swelish

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!

jthi

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);
	If(N Items(r) == 0,
		Column(dt, colname) << Set Modeling Type("Continuous");
	);
);