cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
heloiseheraud
Level II

How to force datatype of columns when importing Excel file

Hi all,

 

I am trying to import an Excel worksheet in JSL, but I meet a problem with some columns which are detected by JMP as numeric whereas they should be character. So I end with empty columns and I can't find a way of telling JMP these columns should be read as character, either in JSL or in Excel import wizard.

Here is my JSL script :

 

jsl_dt_orig = open(jsl_fic_planning,
	//private,
	Worksheets( "Planning" ),
	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( 4 ),
		Data Starts on Row( 5 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 63 ),
		Replicated Spanned Rows( 1 ),
		Suppress Hidden Rows( 0 ),
		Suppress Hidden Columns( 0 ),
		Suppress Empty Columns( 0 ),
		Treat as Hierarchy( 0 )//,
		//Limit Column Type Detection (0)
	)
);

I tried "Limit Column Type Detection(0)" after seeing it on this discussion, even though I can't find this option in the Excel import wizard, but it doesn't work (maybe it exists only for Mac? I have JMP 12.2 on Windows.).

 

What is even stranger is that when I use Excel import wizard, data type seems ok until I tell data start on row 5 (see column "Etat" for instance). But eventually when I import, data is always missing.

 

Data I would like to import as textData I would like to import as textWhat I get when I tell data start on row 5What I get when I tell data start on row 5

So if anyone could help me with formatting my columns at this step, I would be very thankful!

 

Regards

 

Héloïse

 

4 REPLIES 4
Byron_JMP
Staff

Re: How to force datatype of columns when importing Excel file

would it be possible to save that worksheet as a CSV?

There is a little more control over how the columns are imported from CSV files.

In the Source script from a CSV the data and modeling type for each column is captured when the file is imported, from there its pretty easy to edid the Source script to have the file import the way you want.

JMP Systems Engineer, Health and Life Sciences (Pharma)
vkessler
Level IV

Re: How to force datatype of columns when importing Excel file

I encounter the same problem and it is kind of annoying. Is there any way around it without saving the XLS file as CSV?

heloiseheraud
Level II

Re: How to force datatype of columns when importing Excel file

The only solution I found is to explicitly set the column format to "text" in my excel file...

vkessler
Level IV

Re: How to force datatype of columns when importing Excel file

Yes, that worked. Still kind of disappointing that this is not fixed in jmp 13...