Subscribe Bookmark RSS Feed

JSL - Importing specific excel worksheet and modifying

mvcands

Community Trekker

Joined:

Jun 23, 2015

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 )

);

1 ACCEPTED SOLUTION

Accepted Solutions
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 )

));

6 REPLIES
ms

Super User

Joined:

Jun 23, 2011

Try to separate the opening and renaming commands:

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

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

mvcands

Community Trekker

Joined:

Jun 23, 2015

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.

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 )

));

mvcands

Community Trekker

Joined:

Jun 23, 2015

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!

razmah

Community Trekker

Joined:

Jul 20, 2015

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 )

));

briancorcoran

Joined:

Jun 23, 2011

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