Importing data from text file issue

[#1]

We are having problems with data import from text file. Now, we have error message for some columns ("Invalid data encountered while importing column... Try importing the data again, but explicitly set the Data Type of those column to Character").

To better clarify, we have JMP scrip that is used to extract bunch of data from a database. After extracting, the data are organized with parameter in columns and rows for each instance of data. These parameters are all categorical, but there are many numerical (and many are formatted as Date). Some years ago, we asked help to improve the performance (time) to convert all columns to the appropriate data type (using Jmp script took a lot of time because of thousands of columns).

Jim Nelson suggested to save the table as text file and then open again. This worked fine, improving the performances excellently, converting in numeric all columns and maintaining the date format for those that are dates.

Now, this does not work anymore, and a window alert stop the execution.

I looked into community, i tried one suggested method:

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");
			);
		);

This is not good because of we lose Date formatting, so columns that should be dates appear in numeric format.

Could anyone suggest a workaround?

[#2]

What changed that stopped the process from working?

What version of JMP are you using?

What operating system?

Can you provide a sample of the text file you are reading in, and the JSL you are using to do that?

[#3]

I'm using JMP PRO ver 18.0.0 (JMP normal show the same behavior).
Operating system : Windows 10 Enterprise
What I changed is the instruction open("C:\TEMP\jmp_conversion.csv", columns(LOT_ID=character), scan whole file(1));
where I added the clause columns(LOT_ID=character). Without this change it works (but in some specific case the lot_id is converted numeric in scientific notation: the reason for the change).

Consider that if you run just the following script you do not receive the blocking alert but a message in the log. (I don't know why) 

numeric_conversion = function({dt},{Default Local},
	n= dt << get name();
	try(delete file("C:\TEMP\jmp_conversion.csv"));
	dt << save as("C:\TEMP\jmp_conversion.csv");
	close(dt,nosave);
	try(
	dt= open("C:\TEMP\jmp_conversion.csv", columns(LOT_ID=character), scan whole file(1));
	);
	dt << set name(n);
	return(dt);
);
dt= data table("ExtractedData");

dt = dt << split(
	split( :VALUE ),
	split by( :PARAM ),
	group( :LOT_ID, :WAFER_NO ),
	remaining Columns( Keep All ),
);

dt = numeric_conversion(dt);

[#4]

The script does work just fine and JMP does tell you the reason for the log message

Invalid data encountered while importing the column Reactor.

25 values were set to missing.
Try importing the data again, but explicitly set the Data Type of those columns to Character.

And that is due to the MISSING character values in Reactor column. If you wish to avoid that, you can wrap Open with Log Capture()

Names Default To Here(1);

numeric_conversion = Function({dt_split}, {Default Local},
	n = dt_split << get name();
	Try(Delete File("C:\TEMP\jmp_conversion.csv"));
	dt_split << Save As("C:\TEMP\jmp_conversion.csv");
	Close(dt_split, nosave);
	Log Capture(dt_split = Open("C:\TEMP\jmp_conversion.csv", columns(LOT_ID = character), scan whole file(1)));
	dt_split << set name(n);
	Return(dt_split);
);

dt = Open("$DOWNLOADS/ExtractedData.jmp");

dt_split = dt << split(split(:VALUE), split by(:PARAM), group(:LOT_ID, :WAFER_NO), remaining Columns(Keep All), Output table("ExtractedData Split"));
Close(dt, no save);

dt_split = numeric_conversion(dt_split);