Sorting multiple worksheets in an excel spreadsheet

Question

Good evening, I am trying sort through worksheets in one excel file. Right now I can open all the worksheets using this:

Should I save to .jmp datatables and then have a for loop sort through those?

files = Files In Directory( "C:\Users\Desktop\JMP" );

For( i = 1, i <= N Items( files ), i++,

If( Ends With( files[i], ".xlsx" ),

dt = open( files[i]), dt << save(filename)

);

Copy of w2089-data-analysis-1a (002).xlsx

uday_guntupalli · Answer

@tylerram123, 
         When you have multiple data tables open, you can do the following:

openDTs = List();
For( i = 1, i <= N Table(), i++,
	Insert Into( openDTs, Data Table( i ) );
   );

https://www.jmp.com/support/help/14/perform-actions-on-all-open-data-tables.shtml

Don't be sorry that you have to ask questions. The community is a great resource with lot of people who are patient and contribute a lot. I learned a lot from the community and asking questions here and I am happy to do a small bit in helping new users get familiar with JMP.

However, I would simultaneously encourage you to familiarize yourself with some great resources that you already have at your disposal.

"Scripting Index" - great resource to learn and understand about functions and it has examples

"Sample Data" - sample data sets to test your code and try different things on

"Books" - has a lot of unlimited resources which goes into a lot of little details and examples

Good Luck. And you have the JMP community too to help anytime

tylerram123 · Answer

So normally I use dt = Current Data Table(); so how would I get the dt when I am looping through. Sorry for all the trivial questions, I am a very new user

uday_guntupalli · Answer

@tylerram123,  
 Yes. you would use a for loop and do something like this :

Clear Log(); Clear Globals(); Close All(DataTables,"No save");

FilePath = "C:\Copy of w2089-data-analysis-1a (002).xlsx";

SheetsList = Get Excel Worksheets(FilePath);

Show(SheetsList);

SortedList = Sort List(SheetsList);

Show(SortedList);

for(i = 1, i <= N Items(SortedList), i++, 
        // Individual scripts  
    );

Alternatively, you can always open all sheets at once and then perform the actions on the sheets. You can use the UI to import the file and see how JMP is handling the import code. You can see something like this , when you use the Excel Import Wizard :

File -> Open ->