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

How to force datatype of columns when importing Excel file

heloiseheraud — Mon, 03 Jul 2017 08:58:54 GMT

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 textWhat 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

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

Byron_JMP — Thu, 20 Jul 2017 19:56:01 GMT

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.

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

vkessler — Mon, 21 Aug 2017 14:32:46 GMT

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

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

heloiseheraud — Mon, 21 Aug 2017 14:51:20 GMT

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

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

vkessler — Mon, 21 Aug 2017 15:51:47 GMT

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