Force Import Format of Excel columns

[#1]

Dear community,

I am trying to import data from the attached Excel file "DATA_SAMPLE_TO_IMPORT.xlsx", using following jsl script:

Open(
	".\DATA_SAMPLE_TO_IMPORT.xlsx",
	Worksheets( "DATA" ),
	Use for all sheets( 1 ),
	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( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 0 ),
		Suppress Hidden Columns( 0 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 1 ),
		Column Separator String( "-" ),
	)
);

The result is the attached jmp data table, in which all columns are considerd as numeric. I would like both columns to be Characters.

How could I force this format during import?

Thanks!

[#2]

JMP recognizes that in Excel your columns are continuous.

If you could change format in Excel to text, all would be fine.

[#3]

It is also very easy to change the columns imported as Numeric data to Character data. Or if you just want to use the levels for grouping, you could change the modeling type from Continuous to Nominal or Ordinal.

[#4]

@Mark_Bailey the Problem here is, that JMP Imports the columns as continuous, because they are formatted as numbers in Excel. But JMP can not interprete the Content ("LOT 1") as a number. Thats why the result ist NAN (or a visual dot).

Due to that any reformatting in JMP doesn't help.

There are only two options

- somehow to tell JMP to Import differently (I honestly do not know)

- to Reformat in Excel to txt, or to save as csv and Import from that Format ...

[#5]

Thank you for your help. My goal was to reformat data after import but it seems that there is no choice than to modify at least the data file format.

[#6 · Accepted solution]

The table seems to have cell formatting the forces a numeric identifier, even if the cell contains text.  JMP looks at that identifier, rather than the cell contents, to decide how to treat the cells.  In the case of your sample file, if you select the row 2 cells LOT 1, 36 and right click to Format cells, and then force them explicitly to Text in the Number tab, save the file and then import, JMP will import both columns as text.

JMP does allow you to change the numeric formatting within the Excel Wizard dialog, but changing from numeric to character is not one of the options unfortunately.

Brian Corcoran

JMP Development