Subscribe Bookmark RSS Feed

Opening Excel Worksheet and restricting columns

Hegedus

Community Trekker

Joined:

Jun 23, 2011

Hi,

Version 11.2 on Mac. I am attempting to open a worksheet and bring in ONLY the first 6 columns. I am using the following code, but It is bringing 12 columns instead of the 6 specified.  Any clues?

dt = Open(

  (pick file()),

  Worksheets( "STUD BUMP FIDUCIALS" ),

  Worksheet options(

  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(6),

  Replicated Spanned Rows( 1 ),

  Suppress Hidden Rows( 1 ),

  Suppress Hidden Columns( 1 ),

  Treat as Hierarchy( 0 )

  ),

);



4 REPLIES
briancorcoran

Joined:

Jun 23, 2011

The Worksheet Options JSL came into existence to replicate the behavior of the Excel Wizard.  This was available on Windows in JMP 11, but not the Mac.  JMP 12 for the Mac will provide this functionality.

Brian Corcoran

JMP Development

Hegedus

Community Trekker

Joined:

Jun 23, 2011

Let's see if I look up JMP in Wikipedia JMP (statistical software) - Wikipedia, the free encyclopedia, it says that JMP stands for John's Macintosh Program.  You would figure with that heritage that the Mac version should always be feature comparable with the Windows version.  Is that now a design guideline?  From where I sit in Silicon Valley the predominance of Mac users would argue that would be a good business decision.  Maybe you could run a controlled experiment.

Hegedus

Community Trekker

Joined:

Jun 23, 2011

This poses a major problem for me.  I have a client that I need to support and their scripts start by importing some Excel files.  Now I cannot run the scripts because the Mac version doesn't have that feature.  And Version 12 (added cost to me ) has not yet shipped.

Do you have a solution or just apologies?

julian

Staff

Joined:

Jun 25, 2014

Hi Hegedus,

Perhaps you can handle this by retaining only the first 6 columns after the full import? Here's one approach that selects the first 6 columns, then subsets the table and closes the original. You could also open the first table invisibly and your user wouldn't know any of this was happening in the background.

I hope this helps!

julian

dt = Current Data Table();

cc = dt << Get Column Names();

ncols = 6; //however many columns you want to keep

For( i = 1, i <= ncols, i++,

  cc[i] << Set Selected( 1 );

);

dt << Subset( All rows, Selected Columns );

Close( dt, No Save );