cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
neelsrejan
Level III

JSL Open function isn't creating a subset based on optional argument "Select Columns"

Hi all, 

 

I am working on importing various structured data files into a jmp dataframe using the Open method. I am struggling to see what I am doing wrong for the second case as I am able to use the method to subset as I wish for the first code block. Any help would be appreciated!

 

[file name] is for the string format of the file name not a variable reference. I am omitting full name per company policy.

 

Working Subset using Select Columns:

sl = Open(Pick File()/"[file name].xls",
	Select Columns ("Description", "Sample Mix", "Analyte Type", :Name("Threshold (nA)"), "Expected Outcome", "Lane", "Heater")
) << Set Name ("Sample Lookup");

Not Working Subset (Getting all original columns returned):

sd = Open (Pick File()/"[file name].csv", 
	Select Columns ("Accession Number", "Pad Name", " Warnings")
) << Set Name("Signal Data");

Only thing I can think of why the bottom case does not subset based on columns is the different data formats of xls vs csv. But if this is the case how would I go about correcting this? Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Craige_Hales
Super User

Re: JSL Open function isn't creating a subset based on optional argument "Select Columns"

there is a csv-specific way to omit columns. select columns() probably needs some documentation to say it is specific to JMP data tables. (edit: note sent to development team)

edit: the JSL from the source script after using the csv import wizard

Open(
	"$DOWNLOADS/Big_Class.csv",
	columns(
		New Column( "name", Character, "Nominal" ),
		Omitted Column( . ), // leave out the "age" column
		New Column( "sex", Character, "Nominal" ),
		Omitted Column( . ), // leave out the "height" column
		New Column( "weight", Numeric, "Continuous", Format( "Best", 12 ) )
	),
	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" )
	)
)

edit: Omitted can replace New like this

Omitted Column( "age", Numeric, "Continuous", Format( "Best", 12 ) ),

to document what is omitted. I'm pretty sure the values are ignored. Makes editing the JSL easier too if there are a lot of columns to ignore.

Craige

View solution in original post

4 REPLIES 4
Thierry_S
Super User

Re: JSL Open function isn't creating a subset based on optional argument "Select Columns"

Hi,

Have you checked that the CSV import parameters include the correct row as column headers? It might be as simple as replacing the Open statement with a Import Data statement with the correct parameters

 

Just a thought.

 

Best 

TS

Thierry R. Sornasse
neelsrejan
Level III

Re: JSL Open function isn't creating a subset based on optional argument "Select Columns"

Hi Thierry, 

 

As I am new the to the JSL side of JMP I currently thought "Open" was the main function for all types but I will look into "Import Data" for csv type files. I appreciate the guidance!

 

Thanks, 

Neel Srejan

Craige_Hales
Super User

Re: JSL Open function isn't creating a subset based on optional argument "Select Columns"

there is a csv-specific way to omit columns. select columns() probably needs some documentation to say it is specific to JMP data tables. (edit: note sent to development team)

edit: the JSL from the source script after using the csv import wizard

Open(
	"$DOWNLOADS/Big_Class.csv",
	columns(
		New Column( "name", Character, "Nominal" ),
		Omitted Column( . ), // leave out the "age" column
		New Column( "sex", Character, "Nominal" ),
		Omitted Column( . ), // leave out the "height" column
		New Column( "weight", Numeric, "Continuous", Format( "Best", 12 ) )
	),
	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" )
	)
)

edit: Omitted can replace New like this

Omitted Column( "age", Numeric, "Continuous", Format( "Best", 12 ) ),

to document what is omitted. I'm pretty sure the values are ignored. Makes editing the JSL easier too if there are a lot of columns to ignore.

Craige
neelsrejan
Level III

Re: JSL Open function isn't creating a subset based on optional argument "Select Columns"

Hi Craige,

 

Wow thank you for going though the trouble of screen recording and showing how JMP records the import and exclusion process and copying the source code for it. That was very informative and likewise thanks for requesting more documentation for "Select Columns" as it wasn't very intuitive for myself when reading through it. I appreciate the help!

 

Respectfully,

Neel Srejan