File Import: specify NAN?

hogi — Thu, 07 Dec 2023 21:27:21 GMT

When importing a csv file via Open or MFI, is there an option to specify the String for missing values, e.g. NAN, NV, *
such that numeric columns can be directly identified as such.

Re: File Import: specify NAN?

jthi — Fri, 08 Dec 2023 15:45:51 GMT

Might be just case with small .csv file, but JMP seems to be able to convert some already (NA and NAN at least)

This isn't recognized, but you can force character columns to be numeric

You will get a message about ONE of the columns

This most likely interrupts the operation from affecting other column (otherwise could work?). Now you would have to modify the script JMP creates for you (update all columns to be numeric)

Open(
	"$DOWNLOADS/mycsv.csv",
	columns(
		New Column("col1", Numeric, "Continuous", Format("Best", 12)),
		New Column("col2", Numeric, "Continuous", Format("Best", 12)),
		New Column("col3", Character, "Nominal")
	),
	Import Settings(
		End Of Line(CRLF, CR, LF),
		End Of Field(Comma, CSV(0)),
		Strip Quotes(1),
		Use Apostrophe as Quotation Mark(0),
		Use Regional Settings(0),
		Scan Whole File(1),
		Treat empty columns as numeric(0),
		CompressNumericColumns(0),
		CompressCharacterColumns(0),
		CompressAllowListCheck(0),
		Labels(1),
		Column Names Start(1),
		First Named Column(1),
		Data Starts(2),
		Lines To Read("All"),
		Year Rule("20xx")
	)
);

There could maybe be an option but I feel like you would end up adding massive amounts of different options there in the end, if you had to be able to handle all special cases that weirdly formatted text files might have. I just handle these case by case using either scripting or JMP's data table operations as there are so many different cases.

In general something like this could also be scripted

Names Default To Here(1);

my_csv = Load Text File("$DOWNLOADS/mycsv.csv");
Substitute Into(my_csv, "NV", ".");

dt = Open(Char To Blob(my_csv), "text");

Re: File Import: specify NAN?

hogi — Fri, 08 Dec 2023 15:49:13 GMT

nice workaround :)

Load - Substitute - Open

Re: File Import: specify NAN?

hogi — Sun, 29 Sep 2024 16:32:01 GMT

related wish:

topic Re: File Import: specify NAN? in Discussions

File Import: specify NAN?

Re: File Import: specify NAN?

Re: File Import: specify NAN?

Re: File Import: specify NAN?