JSL - Importing specific excel worksheet and modifying

[#1]

Hello,

First, I am using JMP 11

I'm trying to create a template script that my analysts can use in the future. I'm new to JSL, and am trying to figure out how to import a specific worksheet in JSL, then modify particular variables to be numeric and add a new calculation.

When I try the import portion of this code, it opens the import wizard rather than creating a new data table called "Our Data".

--

dtmain = New Table("Our Data",

  Open("C:\Testing Data.xlsx",

  Worksheets( "Final Data" ),

  )

);

Current Data Table (dtmain);

column (dtmain, "Type") <<data type (Numeric) <<Modeling Type(Ordinal) <<Format(Best, 12);

column (dtmain, "Result1) <<data type (Numeric) <<Modeling Type(Continuous) <<Format(Best, 12);

column (dtmain, "Result2") <<data type (Numeric) <<Modeling Type(Continuous) <<Format(Best, 12);

New Column( "Result Ratio",

  Numeric,

  Continuous,

  Format( "Best", 12 ),

  Formula( :Result1 / :Result2 )

);

[#2]

Try to separate the opening and renaming commands:

dtmain = Open("C:\Testing Data.xlsx", Worksheets("Final Data"));

dtmain << Set Name("Our Data");

[#3]

Unfortunately it is still opening the Excel Import Wizard. There are multiple worksheets in the excel file if that makes a difference, though I would think the Worksheets command would prevent that from being a problem.

[#4 · Accepted solution]

The easiest way for you to get the File Open portion of the JSL correct would be to first open the file using the UI and the Excel Wizard.  Then, in the open table, you will see a Source script.  This script contains the JSL needed to reopen the table in the exact same fashion.  You can context click on the source script and select Edit to extract the JSL.  An example from a table of mine is:

Open(

"C:\Testdata\excel\Excel 2007+\MultipleDiffSettings.xlsx",

Worksheets( "Planets" ),

Use for all sheets( 1 ),

Concatenate Worksheets( 0 ),

Create Concatenation Column( 0 ),

1,

Has Column Headers( 1 ),

Number of Rows in Headers( 1 ),

Headers Start on Row( 2 ),

Data Starts on Row( 3 ),

Data Starts on Column( 1 ),

Data Ends on Row( 0 ),

Data Ends on Column( 0 ),

Replicated Spanned Rows( 1 ),

Suppress Hidden Rows( 1 ),

Suppress Hidden Columns( 1 ),

Treat as Hierarchy( 0 )

));

[#5]

Yes thank you! I had tried that before with my previous code, and it was opening a data table with only one blank column. Doing this with the above mentioned code change worked.

Thank you so much!

[#6]

how is the format if we want to open any file?

I used this but didn't work

Open();

Worksheets( "Planets" ),

Use for all sheets( 1 ),

Concatenate Worksheets( 0 ),

Create Concatenation Column( 0 ),

1,

Has Column Headers( 1 ),

Number of Rows in Headers( 1 ),

Headers Start on Row( 2 ),

Data Starts on Row( 3 ),

Data Starts on Column( 1 ),

Data Ends on Row( 0 ),

Data Ends on Column( 0 ),

Replicated Spanned Rows( 1 ),

Suppress Hidden Rows( 1 ),

Suppress Hidden Columns( 1 ),

Treat as Hierarchy( 0 )

));

[#7]

You can't have the Worksheets part outside the Open, as it refers to the file to open.  There really isn't a way to specify default settings for a general open in JSL.  The JSL is meant to open known sheets with known names and settings.  Sorry.

Brian Corcoran

[#8]

As alternative, there is the function "Pick File()" to choose the specific file in the file path, which can be integrated in the Open() function: 

Open(Pick File(
	"Select JMP File",
	"$DOCUMENTS",
	{"JMP Files|jmp;jsl;jrn", "All Files|*"},
	1,
	0,
	"newJmpFile.jmp"
),
	Worksheets( "Sheet 1" ),
	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( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);

Or other possiblity is to name the excel file always as identical name so that the script Open() can be used with this specific name: 

Open(
	"C:\Desktop\ExcelFile.xlsx",
	Worksheets( "Sheet1" ),
	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( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);